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..
July 18th, 2008 at 2:22 am
Excel does enclose fields with double quotes when the field contains a comma, ex. “red, white, and blue”
November 22nd, 2008 at 2:44 pm
Thanks to #1 Sean. I have been trying to get this to work and this simple script does the trick.
November 24th, 2008 at 7:23 pm
i tried Mathieu’s suggestion of using openoffice
1. it solved the comma issue
2. it was easier to format the date into yyyy-mm-dd
for mysql without having to setup a custom format as usual in excel.
February 21st, 2009 at 7:54 am
A good way to output a quoted csv file from Excel is to create a macro to do it. Then use phpMyAdmin import or MySQL LOAD DATA command to import the data into a MySQL table.
Here’s the code
Public Sub OutputQuotedCSV()
Const QSTR As String = “”"”
Dim myRecord As Range
Dim myField As Range
Dim vFilename As Variant
Dim nFileNum As Long
Dim sOut As String
‘Get a filename to save as
vFilename = Application.GetSaveAsFilename(filefilter:=”Microsoft CSV files,*.csv”, _
Title:=”Save as CSV with fields in double quotes”)
If vFilename = False Then Exit Sub ‘User chose Cancel
nFileNum = FreeFile
Open vFilename For Output As #nFileNum
For Each myRecord In Range(“A1:A” & _
Range(“A” & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, 256).End(xlToLeft))
sOut = sOut & “,” & QSTR & _
Replace(myField.Text, QSTR, QSTR & QSTR) & QSTR
Next myField
Print #nFileNum, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #nFileNum
End Sub
June 19th, 2009 at 11:07 pm
G Laughton – U Da Man! or woman :S
June 30th, 2009 at 5:33 am
Painless and cheaper solution : use openoffice to open your excel spreadsheet, thu you can export it into a CSV file with the ability to choose the fields separators and enclosure.
I found excel having issues to properly escape text fields, sometimes the double quotes are missing on whole field, or sometimes just on the end of field (In My Excel 2003 SP3 at least).
A good text editor to make few replacements needed, and you can go
September 23rd, 2009 at 2:40 pm
Thanks for the tip about LOAD DATA. I have spent WAY too much time trying to get a spreadsheet that somebody created loaded up into my MySQL database. Other than needing to put the OPTIONALLY ENCLOSED BY section before the LINES TERMINATED BY section, this worked beautifully.
Thanks!
November 27th, 2009 at 8:40 am
I also have been searching for some tricky tools to get data and fields from excel to mysql. Needless to say that I did not succeed.
Some problems with transfering the data from excel to mysql.
What are the steps I took:
Excel : save file as *.csv : no problems
import the csv file into the predefined mysql table and skipping row 1 ofcourse.
: 1 problem
My date values were all wrong cause mysql has a hard formatted date structure , meaning that all date values are transformed to yyy-mm-dd no matter what format they have in excel.
So even putting the date in the same format in excel did not work because it formats the date back to dd-mm-yyyy.
I would make some scripts to fix it somehow but I do no know enough yet about php and mysql. Guess it will take some time getting there.
November 30th, 2009 at 6:28 am
PhpMyAdmin can import .csv file without any problem
April 15th, 2011 at 6:34 pm
Us open office. It allows you to select your field delimiters and enclosers.