Loading csv file to MySQL DB with blank "columns" with INSERT

Question

I'm trying to write a php script that will load a tab-delimited CSV file into a MySQL database table so that I can set up a cron job to automate the process.

PROBLEM: The problem is that the CSV file contains many blank "columns" including blank column titles.

GOAL: What I want to do is load all columns into my database table EXCEPT for the columns that contain no column titles even if the data fields are blank. As an added condition, there are certain columns that I simply don't want, even if they contain titles.

For instance, for the sake of argument, my csv file contains the following:

ID [tab] NAME [tab] [tab] [tab] LOCATION [tab] FAX [tab] PHONE [end]
111 [tab] Billy [tab] [tab] [tab]Seattle [tab] [tab] 111-111-1111 [end]
112 [tab] Bob [tab] [tab] [tab] Atlanta [tab] 111-111-1112 [tab] 111-111-1114 [end]

Notice in the sample above that not only are some of the columns missing titles, but some of the data fields are empty for columns that are titled.

As an example, in my database table, I have the following column titles:

ID | NAME | LOCATION | PHONE < - notice that I don't want the FAX column at all.

I can't use LOAD DATA INFILE, so I'm assuming that I need to do everything through an INSERT function through php.

I know some php, but can't do what I'm asking above. Please help.

possible duplicate of [How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?](http://stackoverflow.com/questions/2139069/how-to-skip-columns-in-csv-file-when-importing-into-mysql-table-using-load-data-i) — Marc B, Sep 22 '11 at 19:24

score 0 · Answer 1 · edited Oct 04 '12 at 10:51

0

The fgetcsv() function has a bug that omits empty fields.

https://bugs.php.net/bug.php?id=46463

edited Oct 04 '12 at 10:51

j0k

21,914
28
75
84

answered Aug 16 '12 at 02:01

ma3145tt

1
3

Scoutman · Answer 2 · 2011-09-22T19:03:44.423

0

Use the PHP function fgetcsv():

if (($handle = fopen('filename.csv', 'r')) !== FALSE)
{
    while (($data = fgetcsv($handle, 10000, "\t")) !== FALSE)
    {
        print_r($data); // here you get one row -> array with all fields
        // here you can build a SQL insert query    

    }
    fclose($handle);
}

edited Sep 22 '11 at 19:03

answered Sep 22 '11 at 18:25

Scoutman

1,540
10
19

score 0 · Answer 3 · answered Sep 22 '11 at 18:29

Something like the following using file() and str_getcsv() might work:

$file = file('csv.csv');
$cols = str_getcsv(array_shift($file), "\t");
foreach ($file as $line) {
    $cells = str_getcsv($line, "\t");
    $set = array();
    for ($i = 0; $i < sizeof($cols); $i++) {
        $set[] = "`{$cols[$i]}` = '{$cells[$i]}'";
    }
    $sql = "INSERT INTO `table` SET " . implode(',', $set);
    mysql_query($sql);
}

Loading csv file to MySQL DB with blank "columns" with INSERT

3 Answers3