Import CSV's into Mysql DB

[steveissuperman]

This is a dumb question, but I have honestly spent a long time today trying to figure it out, and I'm sure I'm not the only one who has trouble with this.

How do you import a CSV directly into a MYSQL DB?

I've tried using

LOAD DATA LOCAL INFILE

in mysql, but it loads the entire spreadsheet into one row, or at least it seems like that's what it's doing? I've also tried doing this through phpMyAdmin by following these instructions: http://vegdave.wordpress.com/2007/05/19/import-a-csv-file-to-mysql-via-phpmyadmin/
Same thing.

Can anyone list out a few simple steps for me? I've got a huge CSV with like 500 rows and column headers at the top.

Thanks.

[noelthefish]

as you seem to be using PHP here is a function that is built into PHP

array fgetcsv ( resource $handle [, int $length [, string $delimiter [, string $enclosure [, string $escape ]]]] )

<?php
$row = 1;
$handle = fopen("test.csv", "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    $num = count($data);
    echo "<p> $num fields in line $row: <br /></p>\n";
    $row++;
    for ($c=0; $c < $num; $c++) {
        echo $data[$c] . "<br />\n";
    }
}
fclose($handle);
?>

this is the very basic of what you need. You would of course put in the database update part within the while loop. Take out the ECHO statements as well unless you are debugging but basically with a little alteration this small piece of code will do what you need. if you need more info check out uk.php.net

[metaface]

Hello,

You are missing the declaration of delimiters after the LOAD DATA INFILE in your SQL statement. You can use "FIELDS TERMINATED BY" and "LINES TERMINATED BY" to define these based on how your CSV file is delimited.

For example:

LOAD DATA LOCAL INFILE '/your_file.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3);

Read the MySQL Docs:
http://dev.mysql.com/doc/refman/5.1/en/load-data.html

noelthefish's suggestion in the previous post is a good one if you want to insert the CSV data programmatically. However, if it's just a one-time thing and you don't want to write a SQL statement, then the CSV import feature of PHPMyAdmin should do the trick.

Hope this helps.

Dave

[shinejones]

you said you used phpmyadmin, i really becomes quite easy when phpmyadmin comes into picture.
so you just nead to import the csv file using the import button and you are done..
suppose you have a "file.csv" file with format like this which you want to import
1,php,mysql
2,php,oracle
3,asp,mssql
4,java,oracle

so what you need to do in phpmyadmin interface is,
1. select a database
2. select a table where you want to import the csv file fields..
3. click import button
4. browse for the file "file.csv"
5. change ';' to ',' as ';' is selected as default in the "Fields terminated by" field
6. click go and you are done.

This method works 100 % for me everytime, hope will for you too..

[yanti]

well, i hv this kind of code to import csv data into mysql using php...somehow...its working well but a little bit problem....hope that anyone can fix it....it will be great....

if(isset($_POST['submit']))
   {
     $filename=$_POST['filename'];
     $handle = fopen("$filename", "r");
     while (($data = fgetcsv($handle, 100000, ",")) !== FALSE)
     {
    
       $import="INSERT into kmmb_member1(no_ahli,no_pin,nama,no_ic_baru,no_ic_lama) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";
       mysql_query($import) or die(mysql_error());
     }
     fclose($handle);
     print "Import done";
   }
   else
   {
 
      print "<form action='import.php' method='post'>";
      print "Type file name to import:<br>";
      print "<input type='text' name='filename' size='20'><br>";
      print "<input type='submit' name='submit' value='submit'></form>";
   }

[jainishsenjaliy]

code for how to import .sql file in to Mysql DB

[jainishsenjaliy]

http://www.daniweb.com/forums/post1205569.html#

[celega]

I used the above code but somehow only the first row was imported. Any idea? Thanks

[celega]

actually this is what I wrote, but only the first row was imported:

LOAD DATA LOCAL INFILE 'address_book.csv'
INTO TABLE address
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(first_name, last_name, address, city, state, zip, phone);

Thanks for your help!

[ericdsouza]

I was only getting one line imported too. On a Mac I think the line delimiter is carriage return. Try

LINES TERMINATED BY '\r'

I know this article is old but just in case someone comes across it while Googling like I did.

[drjohn]

Start by exporting your data out of your excel spreadsheet!
People are talking sometimes about importing the excel spreadsheet which is NOT a csv file (csv doesn't need to be comma delimited incidentally, tabs are quite acceptable)

so use Save as and select the file type of tab delimited text file, then use

LOAD DATA LOCAL INFILE '/your_file.txt'
INTO TABLE your_table

I always use tab delimted rather than comma delimited because my data can have a comma in it and that caused problems the very first time I tried this (the data extract I was sent also had no '...' around the data - that might also have solved the problem I had, but I'm happy with tab delimited, so I stick with it). I've never had to specify the delimiter at all. I do this once every week to two tables, and it has worked okay for about four or five years now. I do have to give the full path to the file starting at c:...

I use HeidiSQL so I can connect to a remote database easily. One client is with a web host who does not include phpMyAdmin for "security reasons", which means I'd have to install it and set up any security it needs. Using a desktop application for remote access solves this as it by-passes the problem of a web page which might be found by others.