Firefly Firefly - 5 months ago 78
SQL Question

You have an error in your SQL syntax: MariaDB server + codeigniter + LOAD DATA INFILE

I'm using codeigniter. Trying to load csv file into DB.

$path = 'C:/xampp/htdocs/CodeIgniter-3.0.6/';


The error:

A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Institution ID Code,Acquiring Institution Name,Service Indicator,Local Point of ' at line 5

LOAD DATA INFILE "C:/xampp/htdocs/CodeIgniter-3.0.6/fichier/test3.csv" INTO TABLE atm_location FIELDS TERMINATED BY ";" LINES TERMINATED BY "\n" IGNORE 0 LINES (Acquiring Institution ID Code,Acquiring Institution Name,Service Indicator,Local Point of Service ID,Country Code,Administrative Region, Tourist Area,City / Town / Locality,Postal Code,Address,Location Name,Type of Location,Type of Access,Opening Hours,Opening Days,Card Programm Indicator,Opening Date,GPS Latitude,GPS Longitude)

Filename: C:/xampp/htdocs/CodeIgniter-3.0.6/system/database/DB_driver.php

Line Number: 691


This works:

$query = $this->db->query('LOAD DATA INFILE "'.$path.'fichier/test3.csv" INTO TABLE atm_location FIELDS TERMINATED BY ";"');


This doens't work (give the error above):

$query = $this->db->query('
LOAD DATA INFILE "'.$path.'fichier/test3.csv"
INTO TABLE atm_location FIELDS TERMINATED BY ";"
LINES TERMINATED BY "\n"
IGNORE 0 LINES
(Acquiring Institution ID Code,Acquiring Institution Name,Service Indicator,Local Point of Service ID,Country Code,Administrative Region, Tourist Area,City / Town / Locality,Postal Code,Address,Location Name,Type of Location,Type of Access,Opening Hours,Opening Days,Card Programm Indicator,Opening Date,GPS Latitude,GPS Longitude)');


No columns missing. csv file don't have columns names. DB have columns names that are the same as write here. I'm working on localhost, with xampp obviously.

Ray Ray
Answer

I would assume the issue is the whitespace in your column names:

    (Acquiring Institution ID Code,Acquiring Institution Name,Service Indicator,Local Point of Service ID,Country Code,Administrative Region, Tourist Area,City / Town / Locality,Postal Code,Address,Location Name,Type of Location,Type of Access,Opening Hours,Opening Days,Card Programm Indicator,Opening Date,GPS Latitude,GPS Longitude)'

Backtick them and try again:

 (`Acquiring Institution ID Code`,`Acquiring Institution Name` ....

In the future, avoid whitespace for column names. Keeping the names all lowercase is good advice too.

Comments