0

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.

0 Answers0