I've searched this for a few hours already, but it doesn't work.
I have a csv files of presidency. the first row is the header.
Presidency ,President ,Wikipedia Entry,Took office ,Left office ,Party ,Portrait,Thumbnail,Home State
I want to create a relation that has only Presidency
, President
, and Party
.
$sqlquerycreate1 = "CREATE TABLE IF NOT EXISTS `sample1` (
`Presidency` VARCHAR(30),
`President` VARCHAR(30),
`Party` VARCHAR(30)
)";
mysql_query($sqlquerycreate1);
# download the file off the internet
$file = file_get_contents("http://localhost/sample.csv");
$filepath = "sample.csv";
file_put_contents($filepath, $file);
# load the data into nycgrid table
$pt11 = "LOAD DATA LOCAL INFILE ";
$pt21 = "'C:/xampp/htdocs/test/file/sample.csv' INTO TABLE sample1(Presidency,President,@Wikipedia Entry,@Took office ,@Left office ,Party,@Portrait,@Thumbnail,@Home State) ";
$pt31 = "FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ";
$pt41 = "LINES TERMINATED BY '
' ";
$pt51 = "IGNORE 1 LINES";
$sqlquerynew1 = $pt11.$pt21.$pt31.$pt41.$pt51;
mysql_query($sqlquerynew1);
It doesn't work. The table is created, but no data loaded. Any idea why? Thanks.
mysql> LOAD DATA LOCAL INFILE 'C:/xampp/htdocs/sample.csv' INTO TABLE sample1(Pr
esidency, President, @x, @x, @x, Party, @x, @x, @x) FIELDS TERMINATED BY ',' ENC
LOSED BY '\"' LINES TERMINATED BY '
' IGNORE 1 LINES;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'FIELD
S TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '
' IGNORE 1 LINE' at
line 1