Excel (CSV) to MySQL hell
Converting data from one format to another is no easy job. Character sets, data types, collations and proprietary commands complicate things to no end.
But what I wanted to do was simple. I had a simple 2 column list of IDs with a text value. Seemed simple enough, export as
CSV and import into MySQL. My first choice was to try and use phpMyAdmin for the import. phpMyAdmin provides a fairly simple import procedure which allows the import of a CSV file into an existing table. You can select the delimiter, record separator, escape character and field enclosure character. Excel exports with no field enclosure character (usually double quotes) so I figured I would just blank it out and the import would be flawless. But phpMyAdmin didn’t like having no field enclosure character and threw an error. Seemed like a simple problem to fix, just modify the Excel CSV output and we’re good.
And that is when I arrived in hell. Excel gives no (not 2, not 1, none!) options for CSV export. What they give you is all you get. I guess they figure you will only output number based spreadsheets to CSV but the lack of field enclosure wreaks havoc on anyone trying to output data with a comma in it. How are you supposed to know if the comma is part of the field or the field separator? My simple export from Excel to MySQL suddenly became something that was virtually impossible.
I’m not sure if Microsoft is trying to dissuade use of the CSV file format but it sure seems that they are not making it easy. I know we live in a world of XML but CSV is still used as a workhorse for those simple import and export tasks. It seems far more baffling when Excel’s CSV import features are actually quite robust.
In searching high and low, I found a truck load of small utilities that claimed to convert every format under the sun to MySQL. Each one costing at least $30 with no real track record or history. I’m not balking at the $30 but if I’m going to pay some money I want to be sure the tool is going to do what I need it to.
Being a PHP developer I knew how easy CSV files were to manipulate in PHP so I decided to see if anyone had come upon the same problem. At last some answers! I found PHP CSV Importer 3 on HotScripts. This small php script makes the process of importing a CSV file into MySQL simple as ever. It steps you through selecting the CSV file, entering the MySQL database information, select the table to import to, seeing a preview of the data before import. It even allows you to map the various CSV columns to MySQL table columns.
I’m relieved to finally find a worthy solution to the simple task of importing a CSV file into MySQL.
Subscribe to:
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());
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:
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
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!
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!
May 5th, 2008 at 1:37 pm
Is there a way to have this run automatically? Say 1AM everyday?
May 5th, 2008 at 2:56 pm
nevermind, I got JCron to work it out for me..