Importing .csv file into mysql in php

Question

I have a .csv file with me but i am unable to import it into the database. I have parsed my .csv file using the below query. Can you please help me how to insert data into MySql.

My code is:-

$fp = fopen('test.csv','r') or die("can't open file");
print "<table>\n";

while($csv_line = fgetcsv($fp,1024)) 
{
    print '<tr>';
    for ($i = 0, $j = count($csv_line); $i < $j; $i++) {

        print '<td>'.$csv_line[$i].'</td>';
        $data[] = $csv_line[$i];
    }
    print "</tr>\n";
}
print '</table>\n';
fclose($fp) or die("can't close file");

Have you tried using phpmyadmin to import the CSV file into the database? That is my usual method. This is the tutorial I used the first time to figure it out. http://vegdave.wordpress.com/2007/05/19/import-a-csv-file-to-mysql-via-phpmyadmin/ — donlaur, Jan 05 '13 at 05:56
Take a look here:http://stackoverflow.com/questions/12262412/import-txt-csv-data-into-database-according-to-database-table-header — DWright, Jan 05 '13 at 05:56
possible duplicate : http://stackoverflow.com/questions/3635166/how-to-import-csv-file-to-mysql-table — Angel, Jan 05 '13 at 06:01

score 4 · Answer 1 · answered Jan 05 '13 at 05:58

In MySQL we can import data from CSV file to a table very easily:

LOAD DATA LOCAL INFILE 'import.csv' INTO TABLE from_csv FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n'  (FIELD1, FIELD2, FIELD3);

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Example: http://www.techtrunch.com/linux/import-csv-file-mysql-table

score 0 · Answer 2 · answered Jan 05 '13 at 06:04

0

The code seems to take the csv file and export it out to the browser as a tabular data table.

You mentioned importing into a mysql table, but there are no mysql information listed. Create a mysql table and try to map your fields you are importing to database fields in a table. The easiest way for me is to use phpmyadmin to generate the load data sql although the load data local infile mentioned in an answer will work as well if you understand enough to change it.

When I learned how to do it I used this tutorial. http://vegdave.wordpress.com/2007/05/19/import-a-csv-file-to-mysql-via-phpmyadmin/

answered Jan 05 '13 at 06:04

donlaur

1,269
1
12
21

Hey thanks , but my doubt out here is that how to manually insert into database using the Insert query. In the above code i have read csv file and got the data into an array format, but the problem i am facing is inserting that array into db – Jan 05 '13 at 06:22
Yes, phpmyadmin will do all of that for you. You just select the csv file and it will import and you can have it done within a few minutes. If it is something you need to do more than once, that might be a different story. – donlaur Jan 05 '13 at 06:53

shail85 · Answer 3 · 2013-01-05T06:38:41.063

you can use this code. I hope this will be helpfull
//after uploading csv file from a upload file field
$handle = fopen($_FILES['csvfile']['tmp_name'], "r");

$header = fgetcsv($handle);

while(! feof($handle)){

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

$import="INSERT into csvtest($header[0], $header[1], $header[2], $header[3], $header[4], $header[5],$header[6]) values ('$data[0]', '$data[1]', '$data[2]', '$data[3]', '$data[4]', '$data[5]', '$data[6]')";

mysql_query($import) or die(mysql_error());

}

fclose($handle);

Importing .csv file into mysql in php

3 Answers3