PHP - Import CSV File to MySQL Database Using Load Data Infile

PHP - Import CSV file to mysql database Using LOAD DATA INFILE

If you'd do echo($sql); before you execute it you'd see that syntax of your query is incorrect for following reasons:

1. Filename should be enclosed in quotes rather than backticks because it's a string literal not an identifier.

2. There is absolutely no need to call mysql_escape_string() to specify a delimiter in FIELDS TERMINATED BY and ENCLOSED BY and ESCAPED BY clauses.

3. You overuse backticks. In fact in your case, since there are no reserved words used, you ditch them all. They only add clutter.

4. At the end of the very first line of your CSV file you have to have ,,, because you use them as part of a line delimiter. If you won't do that you'll skip not only first line but also second one that contains data.

5. You can't use ENCLOSED BY clause more than once. You have to deal with Number field in a different way.

6. Looking at your sample rows IMHO you don't need ESCAPED BY. But if you feel like you need it use it like this ESCAPED BY '\\'.

That being said a syntacticly correct statement might look like this

LOAD DATA INFILE 'detection.csv'
INTO TABLE calldetections
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY ',,,\r\n'
IGNORE 1 LINES 
(date, name, type, number, duration, addr, pin, city, state, country, lat, log)

---

Now IMHO you need to transform quite a few fields while you load them:

1. if date in your table is of datetime data type then it needs to be transformed, otherwise you'll get an error

   Incorrect datetime value: 'Sep-18-2013 01:53:45 PM' for column 'date' at row

2. you have to deal with single qoutes around values in Number field

3. you most likely want to change "null" string literal to actual NULL for addr, pin, city, state, country columns

4. if duration is always in seconds then you can extract an integer value of seconds and store it that way in your table to be able to easily aggregate duration values later.

That being said a useful version of the statement should look something like this

LOAD DATA INFILE 'detection.csv'
INTO TABLE calldetections
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY ',,,\r\n'
IGNORE 1 LINES 
(@date, name, type, @number, @duration, @addr, @pin, @city, @state, @country, lat, log)
SET date = STR_TO_DATE(@date, '%b-%d-%Y %h:%i:%s %p'),
    number = TRIM(BOTH '\'' FROM @number),
    duration = 1 * TRIM(TRAILING 'Secs' FROM @duration),
    addr = NULLIF(@addr, 'null'),
    pin  = NULLIF(@pin, 'null'),
    city = NULLIF(@city, 'null'),
    state = NULLIF(@state, 'null'),
    country = NULLIF(@country, 'null') 

Below is the result of executing the query on my machine

mysql> LOAD DATA INFILE '/tmp/detection.csv'
    -> INTO TABLE calldetections
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"' 
    -> LINES TERMINATED BY ',,,\n'
    -> IGNORE 1 LINES 
    -> (@date, name, type, @number, @duration, @addr, @pin, @city, @state, @country, lat, log)
    -> SET date = STR_TO_DATE(@date, '%b-%d-%Y %h:%i:%s %p'),
    ->     number = TRIM(BOTH '\'' FROM @number),
    ->     duration = 1 * TRIM(TRAILING 'Secs' FROM @duration),
    ->     addr = NULLIF(@addr, 'null'),
    ->     pin  = NULLIF(@pin, 'null'),
    ->     city = NULLIF(@city, 'null'),
    ->     state = NULLIF(@state, 'null'),
    ->     country = NULLIF(@country, 'null');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from calldetections;
+---------------------+---------+---------------+-------------+----------+------+------+------+-------+---------+------+------+
| date                | name    | type          | number      | duration | addr | pin  | city | state | country | lat  | log  |
+---------------------+---------+---------------+-------------+----------+------+------+------+-------+---------+------+------+
| 2013-09-18 13:53:45 | Unknown | outgoing call | 123456      |        0 | NULL | NULL | NULL | NULL  | NULL    | 0.0  | 0.0  |
| 2013-09-18 13:54:14 | Unknown | outgoing call | 1234567890  |        0 | NULL | NULL | NULL | NULL  | NULL    | 0.0  | 0.0  |
| 2013-09-18 13:54:37 | Unknown | outgoing call | 14772580369 |        1 | NULL | NULL | NULL | NULL  | NULL    | 0.0  | 0.0  |
+---------------------+---------+---------------+-------------+----------+------+------+------+-------+---------+------+------+
3 rows in set (0.00 sec)

---

And finally in php assigning a query string to $sql variable should look like this

$sql = "LOAD DATA INFILE 'detection.csv'
        INTO TABLE calldetections
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '\"' 
        LINES TERMINATED BY ',,,\\r\\n'
        IGNORE 1 LINES 
        (@date, name, type, @number, @duration, @addr, @pin, @city, @state, @country, lat, log)
        SET date = STR_TO_DATE(@date, '%b-%d-%Y %h:%i:%s %p'),
            number = TRIM(BOTH '\'' FROM @number),
            duration = 1 * TRIM(TRAILING 'Secs' FROM @duration),
            addr = NULLIF(@addr, 'null'),
            pin  = NULLIF(@pin, 'null'),
            city = NULLIF(@city, 'null'),
            state = NULLIF(@state, 'null'),
            country = NULLIF(@country, 'null') ";

PHP MySQL - Import CSV Using LOAD DATA INFILE - Determine file path?

Your problem is that PHP is working on the webserver, MySQL is working on the database server. I think as long as no replication is involved the missing "dot" could be

LOAD DATA LOCAL INFILE

to read from the client-computer.

(original: http://dev.mysql.com/doc/refman/5.1/de/load-data.html)

as of 2018: https://dev.mysql.com/doc/refman/8.0/en/load-data.html

Import CSV file with LOAD DATA LOCAL INFILE

Try this:

LOAD DATA LOCAL INFILE 'solic2.txt' INTO TABLE solic_code FIELDS TERMINATED BY ','  OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

This is a csv (comma separated) rather than tab separated, which is what you set with \t.

To make sure it will be using UTF-8:

LOAD DATA LOCAL INFILE 'solic2.txt' INTO TABLE solic_code  CHARACTER SET UTF8 FIELDS TERMINATED BY ','  OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

Using LOAD DATA INFILE statement when csv table headings don't match mySQL table headings?

You do this using the appropriate syntax for your problem and specify a column list:

LOAD DATA INFILE 'path/to/your/file' INTO TABLE yourTable (col1,col2,...);

The manual states further:

You must also specify a column list if the order of the fields in the
input file differs from the order of the columns in the table.
Otherwise, MySQL cannot tell how to match input fields with table
columns.

LOAD DATA INFILE doesn't upload CSV data into MySQL table with no error

Changing:

$file = $_FILES['csv']['tmp_name'];

to:

$file = addslashes($_FILES['csv']['tmp_name']);

fixed the issue and CSV now gets uploaded.

Basically I had to escape slashes in the file path.

---

Related Topics