Importing CSV into MYSQL through php not working right

[Raz3rt]

Hello,

 

I am trying to import a CSV file into an existing mysql table but it doesn't seem to work well.

 

Here is how my mysql is looking like:

1    a_id               int(11)        AUTO_INCREMENT
2    a_lobecoid         int(7)    
3    a_code             varchar(30)    utf8_general_ci
4    a_omschint         varchar(60)    utf8_general_ci
5    a_beveiligingniv   int(5)
6    a_type             varchar(5)     utf8_general_ci    
7    a_assortiment      int(5)
8    a_discipline       varchar(30)    utf8_general_ci
9    a_brutoprijs       varchar(50)    utf8_general_ci    
10    a_status          varchar(5)     utf8_general_ci    
11    a_levcode         varchar(10)    utf8_general_ci
12    a_omschr_nl       varchar(60)    utf8_general_ci
13    a_omschr_fr       varchar(60)    utf8_general_ci

these are some lines from my CSV file

16158|-H|Factory installed heater|10|S|400|CCTV|45.0|E|  1829|Factory installed heater|Factory installed heater
16159|-IR|Factory installed IR LED ring|10|S|400|CCTV|50.0|E|  1829|Factory installed IR LED ring|Factory installed IR LED ring
9001|00-SBN2|Smoke box niet geaspireerd,230VAC|10|S|267|BRAND|1587.03|D|   642|Smoke box niet geaspireerd,230VAC|Smoke box pas aspiré,230VCA
9003|00-TP1|Telescopische verlengstok voor Smoke box,2,4-4,6m|10|S|267|BRAND|644.09000000000003|D|   642|Telescopische verlengstok voor Smoke box,2,4-4,6m|Rallonge téléscopique pour Smoke box,2,4-4,6m
9004|00-TP2|Telescopische verlengstok voor Smoke box,2,4-9,2m|10|S|267|BRAND|944.64999999999998|D|   642|Telescopische verlengstok voor Smoke box,2,4-9,2m|Rallonge téléscopique pour Smoke box,2,4-9,2m
12161|001-0081|Thermistor probe,rood, high temp. 0-+150°C|10|S|52|INBRAAK|136.91|D|  1731|Thermistor probe,rood, high temp. 0-+150°C|Sonde température,rouge,high temp. 0-+150°C

Here you have the code for the import:

$upload_article_query = "LOAD DATA INFILE 'ARTIKELS.CSV'
                             INTO TABLE artikelen
                             FIELDS TERMINATED BY '|'
                             LINES TERMINATED BY '\\r\\n'
                             (a_lobecoid, a_code, a_omschint, a_beveiligingniv, a_type, a_assortiment, a_discipline, a_brutoprijs, a_status, a_levcode, a_omschr_nl, a_omschr_fr)";
$upload_article_stmt = $dbh->prepare($upload_article_query);
    
    
$upload_article_stmt->execute();

If i use the code then in the MYSQL table the first line is filled in but where the second line has to start it just writes it into the last column and doesn't start a new line.

Also if i edit that first line it shows a lot of "?" (questionmarks) into a window symbol.

 

Anyone has an idea what i am doing wrong?

 

In attachment some printscreens of my table after the insert. Apologies for the Dutch language.

 

Thanks in advance

[ginerjm]

Why are you doing the 'prepare'?  You have no values in the query that need to be prepared.  Try just running query instead of prepare/execute

[Jacques1]

This looks like an issue with the CSV content and/or problems with the character encoding.

 

Check the CSV with a hex editor to see what it actually contains; there may be invisible characters. Also make sure that the encodings are correct. Is the CSV file indeed stored as UTF-8? Is character_set_database also UTF-8? If not, use the CHARACTER SET clause. 

[Raz3rt]

Well thank you for the answer!

 

@ginerjm: I thought that allready but i think it cannot do any harm to do it that way? It's just how do you call it a habit to write it this way

 

@Jacques1: Yes indeed the CSV is actually in UCS-2 format so there is my problem! I changed it using Notepad++ now i got a whole other issue but one that i can solve i think just by looking at it.

(Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '00-SBN2' for key 'a_code'')

 

Thanks for the help i glad i've learned something!

[Barand]

It would suggest "a_code" is defined as unique key and you have two (or more) records in your data with the same value "00-SBN2" in that field