Import CSV file directly into MySQL
Solution 1
You can create a script to parse your csv file and to put the data into db.
Something like:
$path = "yourfile.csv";
$row = 1;
if (($handle = fopen($path, "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$row++;
$data_entries[] = $data ;
}
fclose($handle);
}
// this you'll have to expand
foreach($data_entries as $line){
$sql = "INSERT INTO ..."
$db->execute($line);
}
Solution 2
but column names in csv and that in database table are different what should i do?
Not a problem. You can specify which CSV column gets imported into which database column.
By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list:
LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);
What I like to do when I find the INFILE syntax too complicated is use a graphical client like HeidiSQL to click together the proper column order (it has a graphical preview) and copy+paste the generated SQL query.
Solution 3
If you want to load only the first 7 characters of the last column from the CSV file into the uniqComments column of the table, then you can do something like this...
load data local infile 'uniq.csv' into table tblUniq fields terminated by ','
enclosed by '"' lines terminated by '\n' (uniqName, uniqCity, @seven_chars)
set uniqComments=left(@seven_chars,7)
Comments
-
ehmad almost 2 years
i want to import csv file into mysql.. something like:
load data local infile 'uniq.csv' into table tblUniq fields terminated by ',' enclosed by '"' lines terminated by '\n' (uniqName, uniqCity, uniqComments)
http://www.tech-recipes.com/rx/2345/import_csv_file_directly_into_mysql/
but column names in csv and that in database table are different what should i do? i want to do it programmatically..
-
Criesto over 10 years..i ran into this problem and i found your answer..can you plz tell me what is this $row doing..? as my $data returns only the single rows value. thanks
-
Elzo Valugi over 10 yearsI guess is not doing anything in here... probably I left it in while cleaning up real code to make this example
-
Criesto over 10 yearsSo, would you be kind enough to write the complete code plz..?