如何用PHP将CSV文件读入MySQL [重复]

问题描述：

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 '
'
        ('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 '
'
        ('symbol_t', 'last_trade_price_only_t','a','b','c','d','e')";

Table

答

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

LINES TERMINATED BY '
'

LINES TERMINATED BY '
'

Per the documentation:

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

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`)";

如何用PHP将CSV文件读入MySQL [重复]

相关推荐