Devit!

7 Responses to “Excel (CSV) to MySQL hell”

1. Sean Says:
   January 18th, 2008 at 11:04 am

   I export a fairly large amount of data in CSV everyday and import into MySQL. I use a simple PHP script. I’m not saying it will work for you, but it does for me:

   mysql_connect("localhost", "user", "password", false, 128) or die(mysql_error());
mysql_select_db("DATABASE") or die(mysql_error());
$datafile = '/path/to/file.csv';
$sql = mysql_query("TRUNCATE TABLE table_name");
$sql = mysql_query("LOAD DATA LOCAL INFILE '$datafile' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\\\' LINES TERMINATED BY '\\r\\n'") or die(mysql_error());

2. noah Says:
   January 18th, 2008 at 11:13 am

   I’ve run into similar problems and implemented a slightly different solution. Frustrated with searching for a utility to convert the CSV file, I eventually turned to the command line functions in MySQL. LOAD DATA INFILE is the command to quickly load text file into a database table.

   The easiest way to get to the command line client is to open MySQL Query Browser and choose MySQL Command Line Client from the Tools menu. You can also get there by typing mysql at a Windows command prompt; it is the same command in the Mac and Unix terminal.

   Make sure you first select the database you want with the use database_name command.

   The syntax of LOAD DATA INFILE can be a bit overwhelming at first. This is what I found worked best for CSV files exported from MS Access or phpMyAdmin.

   LOAD DATA LOCAL INFILE 'c:/import.csv' INTO TABLE tbl_import_data FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES (column_1, column_2, column_3, column_n);

   There are many more options to this powerful command but the above example will handle the majority of your CSV needs. There are a few things to consider when using it:

   • Fields are usually terminated by a comma but could be a semi-colon, tilde, or whatever delimiter you like.
   • Specifying a line terminator may be necessary, depending on the text file format. This often depends on the operating system. Windows uses ‘\r\n’ while Mac and Unix uses ‘\n’, which is the default.
   • Optional enclosing quotes are necessary when your text fields are enclosed by quotes, which is often the case by default. The “optional” keyword ensures that the command doesn’t die when it encounters a field that isn’t enclosed, such as an integer value.
   • Ignoring the first line is useful for when you export data with column names in the first row
   • Working on a local database may be easier, as special file permissions must be given to the user or the file must be accessible to the MySQL server.
   • When loading a local file, you must supply the full path of the file you want to load.
3. Wendy Says:
   April 23rd, 2008 at 5:59 pm

   But you are all using ENCLOSED BY ‘”‘ when Excel DOESN’T enclose the fields. How can any importer know what to do if a field has a comma in it? That will throw the field count off. The key is getting it OUT OF EXCEL with quotes. How do you do that?

   Currently, I import the Excel file into Access and then export that in CSV (which allows you to select ” to enclose the fields. But that causes problems too - especially with date fields.

   /Wendy

4. Mathieu Says:
   May 1st, 2008 at 10:25 pm

   I’m so new in that… but anyway…
   OpenOffice (at least NeoOffice, the aqua-themed counterpart for Mac) DOES let you choose wether or not having fields enclosed with ” “.
   It’s free. Install it. Open a .xls file. Save it in the .csv formal. A window with many options will open. It’s there.
   I hope that can be usefull!

5. Mathieu Says:
   May 1st, 2008 at 11:33 pm

   i tried noah’s clue through terminal.
   It works! so well!
   for someone who uses a client (i use CocoaMySQL — yes, I’m a Mac), it’s possible to type those commands directly in the “Custom Query” area. No use to mess with the terminal. Maybe everyone would have known this but I didn’t!

6. JRSquared Says:
   May 5th, 2008 at 1:37 pm

   Is there a way to have this run automatically? Say 1AM everyday?

7. JRSquared Says:
   May 5th, 2008 at 2:56 pm

   nevermind, I got JCron to work it out for me..

Leave a Reply

Name (required)

Mail (will not be published) (required)

Website