I try a bit with CSV import methods. In the moment I use fgetcsv
with each row validation and inserts, which works but is slow. So i thought to use load data infile
instead.
The CSV is exported by excel with ;
as standard delimiter. Its from different users, so I cannot change this.
Problem is one column, which can contain HTML and also the use of ; in code.
When I use fgetscv
with inserts I have no problems, identifying headers. But with load data in-file I get for sure problems with this delimiter.
This way i do this now:
while (($aCell = fgetcsv($handle, 1000, ";")) !== FALSE) {
$num = count($aCell);
//Run through columns, build array
for ($a = 0; $a < $num; $a++) {
// IDENTIFY HEADERS
switch ($field[$a]) {
case ($field_name = "comlumn1"):
// DO SOME VALIDATION etc.
$array['column1'] = $aCell[$a];
break;
case ($field_name = "comlumn2"):
// DO SOME VALIDATION etc.
$array['column2'] = $aCell[$a];
break;
// AND SO ON
}
}
// INSERT array in db for each row
}
An example of the csv structure:
column1 column2
1 <p style="margin-top: 10;">...
When i use load data infile i get problems with column description cause of ";"
LOAD DATA INFILE '/filepath/import.csv' INTO TABLE import_csv
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
Which solutions I've got?
One idea is to read out the lines by fgetscv
first (like i do above) make an temp array with no validation and no inserts and then do a new implode with an other delimiter, so that i can use it in load data in-file.
I'm not sure if it is faster or really make sense than doing the whole thing with fgetcsv
, remember the fact, that there will not be more than 1000 rows in CSV.
EDIT
My solution now is, as written above build an array and implode it with a new unique delimiter:
$fp = fopen("file.csv","w");
foreach((array)$oldarray as $val) {
fwrite($fp,implode("*|*",$val)."\r\n");
}
fclose($fp);
With this unique delimiter i can use LOAD DATA INFILE with no problem.