Importing CSV into MySQL with PHP script

Question

I need to write a PHP script that takes CSV files with 166 columns (yes, 166) and around 20.000 rows. I have no decision regarding the csv files. On top of that, they arent regular csv files. For some reason the delimiter in these files is ";" and not a colon. Some fields are completely empty which in the csv is represented like this: value1;;value3.

I already have the Database and table with matching columns, but with an additional id column as 1st column.

What I've tried:

Getting a LOAD DATA (LOCAL) INFILE query working, but after 15 hours of relentless trying, I decided to ask here. The production server I'm working on is a regular LAMP stack in UBUNTU server but also with a local XAMPP installation it didn't work at all.

I've also tried loading the csv into an array and then looping through it but the csv was never trimmed right.

Even if the LOAD DATA INFILE would work, I'd still have the problem that the 1 column in the DB is set to auto_increment and I really don't want to specify 166 column headers in the sql query.

MySQL:

LOAD DATA INFILE 'D:/User Directories/Desktop/test.csv'
     INTO TABLE nwcatalogue
     FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
     LINES TERMINATED BY '\r\n'

PHP:

//I don't know ho to specify the delimiter for str_getcsv when used as 
//callback

$csv = array_map('str_getcsv', file('data.csv'));

The LOAD DATA INFILE sometimes runs without error, but no records are inserted. Sometimes I get a permission denied error and most often I get "duplicate keys" or "out of range" errors. The query works neither with a Windows nor with a linux file system.

The PHP str_getcsv function works (a bit) better but the values are sometimes not seperated in the right spot. e.G I get values in the array like this:

Array =>
[0] => 0;1;;;2;1;2;1
[1] => 0;4;;12;1;0;5
[2] => 5;1;1;;;1;2;;
[3] => 4;1;;;2;1;2;7

If there is no value between 2 semicolons, it should just insert NULL as the value.

In short, I either need an array where each value has a specific key so that I can loop through it easily and row by row or i need to get this LOAD DATA INFILE query working.

I also have admin rights on the web server and mysql server.

From here https://stackoverflow.com/a/29984411/6701413 you can handle it with an anonymous function to specify the delimiter to your `str_getcsv`, which have the delimiter as the second parameter of the function `str_getcsv ( string $input [, string $delimiter = "," ...`. Also for the `NULL` value, either you try to insert data line per line and specify which column you actually have (which will set the value at null by default), or you hardcode `NULL` in your query if you can — Frankich, Jul 02 '19 at 12:56

score 3 · Accepted Answer · answered Jul 02 '19 at 13:03

3

Try this:

<?php
        $query = <<<eof
            LOAD DATA INFILE 'D:/User Directories/Desktop/test.csv'
             INTO TABLE nwcatalogue
             FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
             LINES TERMINATED BY '\n'
            (fields_go_here)
        eof;

        $db->query($query);
        ?>

As for the CSV, try something like this:

function process_csv($file) {
   $file = fopen($file, "r");
   $data = array();
   while (!feof($file)) {
      $data[] = fgetcsv($file,null,';');
   }
   fclose($file);
   return $data;
}

And then just call process_csv('D:/User Directories/Desktop/test.csv')

answered Jul 02 '19 at 13:03

Carlos Negron

74
4

Thanks for the reply mate. I found the first snippet as well but I want to avoid putting 166 columns atthe end of the query ^^ As for the second snippet, it doesn't work with my csv files. Cant tell you why though. Thanks again. – Steve Gelhausen Jul 02 '19 at 13:21
1

@SteveGelhausen can't you just copy paste all the column ? (if you are using PHPMyAdmin you definitly can – Frankich Jul 02 '19 at 13:37
Thats true ^^ I ll try it out asap.It's just that I don't like seeing 160 specified columns in my code. I think it's kind of unprofessional ^^ – Steve Gelhausen Jul 02 '19 at 13:47
@Frankich It works when copy pasting the columns into the query. Thanks ^^ – Steve Gelhausen Jul 03 '19 at 07:02

Importing CSV into MySQL with PHP script

1 Answers1