How to read a CSV file into MySQL with PHP

Question

Possible Duplicate:
Import CSV file directly into MySQL
Export CSV from Mysql

I want to read from a CSV file that I have on the system (C:/xampp/htdocs/live/quotes.csv) into my MySQL table.

I created the table like this:

mysql_query("CREATE TABLE IF NOT EXISTS datas(
                                 id int(255) NOT NULL auto_increment,
                                 symbol_t char(6) NOT NULL,
                                 last_trade_price_only_t varchar(100) NOT NULL,
                                 a varchar(30) NOT NULL,
                                 b varchar(30) NOT NULL,
                                 c varchar(30) NOT NULL,
                                 d varchar(30) NOT NULL,
                                 e varchar(30) NOT NULL,
                                 PRIMARY KEY (id) 
)");

and now want to write the CSV in the table like that:

$location="C:/xampp/htdocs/live/quotes.csv";
$sql = "LOAD DATA LOCAL INFILE '{$location}'
        INTO TABLE '{datas}'
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '\"'
        LINES TERMINATED BY '\n'
        ('symbol_t', 'last_trade_price_only_t','a','b','c','d','e')";

mysql_query($sql);

but somehow it is not working. Did I set the location wrong?

And i got another question. If i want to get an external csv (yahoo finanace csv) with the url: http://finance.yahoo.com/d/quotes.csv?s=$ticker_url&f=sl1=.csv ($ticker_url is a collection of ticker symbols). Does my $location var have to the just the url, or do I have to open it first with fopen(url)?

now:

$data_tablename="data_".date("m_d_Y",time());
$filename="C:\\xampp\\htdocs\\live\\quotes.csv";
$sql = "LOAD DATA LOCAL INFILE '{$filename}'
        INTO TABLE '{$data_tablename}'
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '\"'
        LINES TERMINATED BY '\r\n'
        ('symbol_t', 'last_trade_price_only_t','a','b','c','d','e')";

Table

"somehow it is not working". Then something clearly is wrong. — Gordon, Jan 05 '13 at 12:10
Try running that `LOAD DATA` query in MySQL directly so you can see if any errors are raised - I suspect `{datas}` is wrong. Does it need braces? Failing that, try using PHP directly to export lines using [fputcsv](http://php.net/manual/en/function.fputcsv.php). Also, plenty more ideas [here](http://stackoverflow.com/questions/4607357/export-csv-from-mysql) - remember to web-search your problems before asking questions. — halfer, Jan 05 '13 at 12:20
Ah, you can disregard part of my answer - your question originally asked how to _write_ to a CSV file. However, I worked out you want to read a CSV file into a database table - question substantially edited to make this clearer! — halfer, Jan 05 '13 at 12:28

kittycat · Accepted Answer · 2013-01-05T13:12:28.403

4

Since you are using Windows your lines in your CSV file are likely terminated with \r\n so you need to change:

LINES TERMINATED BY '\n'

To

LINES TERMINATED BY '\r\n'

Per the documentation:

If you have generated the text file on a Windows system, you might have to use LINES TERMINATED BY '\r\n' to read the file properly, because Windows programs typically use two characters as a line terminator. Some programs, such as WordPad, might use \r as a line terminator when writing files. To read such files, use LINES TERMINATED BY '\r'.

Also:

'{datas}'

Probably was meant to be:

'{$datas}'

Also:

('symbol_t', 'last_trade_price_only_t','a','b','c','d','e')";

Needs to be:

(`symbol_t`, `last_trade_price_only_t`, `a`, `b`, `c`, `d`, `e`)";

edited Jan 05 '13 at 13:12

answered Jan 05 '13 at 12:13

kittycat

14,983
9
55
80

hey cryptic you again. sry but not working. – sami_analyst Jan 05 '13 at 12:20
@user1791283, sorry I had changed my answer as I found it to be incorrect as I read more. Did you try the above edit? – kittycat Jan 05 '13 at 12:21
@user1791283 - can I make some suggestions? Rather than saying "not working", it is best to use answers as clues to help you, rather than giving up straight away. It also doesn't indicate what might have gone wrong, such as error messages etc - so people helping you will have to ask further questions to dig into your problem. Being persistent will make you a better programmer! `:)` – halfer Jan 05 '13 at 12:23
@user1791283 did you change {datas} to {$datas} ? or removed the braces if they were not meant to be there? – kittycat Jan 05 '13 at 12:27
i changed it to $data_tablename – sami_analyst Jan 05 '13 at 12:28
$data_tablename="data_".date("m_d_Y",time()); $filename="C:\\xampp\\htdocs\\live\\quotes.csv"; $sql = "LOAD DATA LOCAL INFILE '{$filename}' INTO TABLE '{$data_tablename}' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' ('symbol_t', 'last_trade_price_only_t','a','b','c','d','e')"; – sami_analyst Jan 05 '13 at 12:30
@user1791283 can you remove the quotes around the table name. – kittycat Jan 05 '13 at 12:38
btw please try to verify what type of line ending the file has, so we aren't making changes to debug and not realizing the line terminator needs to be changed as well. – kittycat Jan 05 '13 at 12:42
did it also nothing. also tried with removing them from $filename. – sami_analyst Jan 05 '13 at 12:43
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/22235/discussion-between-cryptic-and-user1791283) – kittycat Jan 05 '13 at 12:46

How to read a CSV file into MySQL with PHP

1 Answers1