I have a .csv file data like that
Date,Name,Call Type,Number,Duration,Address,PostalCode,City,State,Country,Latitude,Longitude
"Sep-18-2013 01:53:45 PM","Unknown","outgoing call",'123456',"0 Secs","null","null","null","null","null",0.0,0.0,,,
"Sep-18-2013 01:54:14 PM","Unknown","outgoing call",'1234567890',"0 Secs","null","null","null","null","null",0.0,0.0,,,
"Sep-18-2013 01:54:37 PM","Unknown","outgoing call",'14772580369',"1 Secs","null","null","null","null","null",0.0,0.0,,,
and I'm using the following code to insert the data into database
$sql = "LOAD DATA INFILE `detection.csv`
INTO TABLE `calldetections`
FIELDS TERMINATED BY '".@mysql_escape_string(",").
"` OPTIONALLY ENCLOSED BY `".@mysql_escape_string("\"").
"` OPTIONALLY ENCLOSED BY `".@mysql_escape_string("\'").
"` ESCAPED BY `".@mysql_escape_string("\\").
"` LINES TERMINATED BY `".",,,\\r\\n".
"`IGNORE 1 LINES `"
."(`date`,`name`,`type`,`number`,`duration`,`addr`,`pin`,`city`,`state`,`country`,`lat`,`log`)";
$res = @mysql_query($con,$sql);
but nothing is inserted; where is the mistake?
If you'd do echo($sql);
before you execute it you'd see that syntax of your query is incorrect for following reasons:
Filename should be enclosed in quotes rather than backticks because it's a string literal not an identifier.
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.
You overuse backticks. In fact in your case, since there are no reserved words used, you ditch them all. They only add clutter.
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.
You can't use ENCLOSED BY
clause more than once. You have to deal with Number
field in a different way.
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:
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
you have to deal with single qoutes around values in Number
field
you most likely want to change "null"
string literal to actual NULL
for addr, pin, city, state, country
columns
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') ";