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 ',,,rn'
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 ',,,rn'
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') ";

According to the documentation for LOAD DATA, treating doubled double quotes as a double quote is the default:

If the field begins with the ENCLOSED BY character, instances of that character are recognized as terminating a field value only if followed by the field or line TERMINATED BY sequence. To avoid ambiguity, occurrences of the ENCLOSED BY character within a field value can be doubled and are interpreted as a single instance of the character. For example, if ENCLOSED BY '"' is specified, quotation marks are handled as shown here:

"The ""BIG"" boss"  -> The "BIG" boss
The "BIG" boss      -> The "BIG" boss
The ""BIG"" boss    -> The ""BIG"" boss

So all I need to do is disable interpreting as an escape character, by using ESCAPED BY ''.

LOAD DATA
  LOCAL INFILE 'temp-1.csv'
  INTO TABLE person
  FIELDS
    TERMINATED BY ','
    ENCLOSED BY '"'
    ESCAPED BY ''
  LINES
    TERMINATED BY 'n'
  IGNORE 1 LINES
;