Excel into MySQL..How?!

December 5, 2007 2:53 PM

I've been trying to get a 600 record Excel database into MySQL, and failed at every turn. There must be a way!

I'm getting really fed up with this now, but I have to get it working in the next week or so.

I have a 600 record database in Excel that I need to import into a MySQL database so that I can generate a nice HTML page of the content (or selected bits of it).

I've got it exported as a CSV, but MyPHPAdmin fails to import it saying "Invalid field count in CSV input on line 1.", but I've checked and I have 30 fields in the CSV and in the database.

All other options seem to be software costing money. There must be something I'm missing!

posted by lemonfridge to computers & internet (15 comments total)

Are there commas in any of the data in CSV file?
posted by oh pollo! at 2:57 PM on December 5, 2007

Are you sure your feeding phpmyadmin the right column and row delimiters? And are you absolutely sure you have the field count perfect, and your column types are good? This is something you can do quickly with Python (or really any other scripting language), provided you have basic coding skills.
posted by Mach5 at 3:00 PM on December 5, 2007

So how can I do this, step by step, from Excel to MySQL, with my limited PHP knowledge?
posted by lemonfridge at 3:05 PM on December 5, 2007

Follow the directions here.

Did you change the "Fields terminated by" option to the comma?
posted by junesix at 3:11 PM on December 5, 2007

Could try mysql's native LOAD DATA INFILE method.
posted by xmutex at 3:24 PM on December 5, 2007

In the Excel file, try selecting a whole bunch of columns and rows outside of your data and deleting them. There may be an errant space or break in there somewhere. I seem to recall having a similar problem to yours and this may have fixed it.
posted by erpava at 3:47 PM on December 5, 2007

I don't use MySQL, but if it's just 600 records, could you not chuck a formula into your excel sheet to generate an insert line for each record?

like:

="INSERT INTO table VALUES ("&A1&","&A2&","&A3&")"


and just run that.


(after checking for commas in your data, like everyone says!)
posted by pompomtom at 3:48 PM on December 5, 2007

You could have blank columns (i.e. no title on the column) at the end of your csv file.

To import into mySQL, Copy the columns you want to import into a new excel file then export that as CSV.
posted by seanyboy at 4:24 PM on December 5, 2007

If you're using pompomtom's solution, you should check your data for single quotes or double quotes, depending on which one you use to enclose strings in the SQL query.
posted by qvtqht at 4:55 PM on December 5, 2007

Everytime I need to do this, it's usually an edge case, so I just download a trial version of Navicat and do the import. It has never failed me, unlike the free XLS parsing libraries available, which are usually crap.

If you need to do this regularly, I've had luck with converting XLSes to XML, and then using an XML parser to generate SQL insert queries. A little stupid, but I've never had a need to do it more often than a couple times a month, so I always took the half-assed way out.
posted by fishfucker at 5:07 PM on December 5, 2007

I had this same problem last week. After a lot of Google searching, I never found a good solution for it (ended up making a lot of fixes by hand), but I can tell you where the problems are: either MySQL or Excel fail to follow the CSV RFC in various places.

MySQL fails to adhere to section 2.6: "Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes," specifically the part about line breaks. MySQL also fails section 2.7: "If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote." Excel, for it's part, fails to adhere to section 2.4: "Each line should contain the same number of fields throughout the file."

The combination of the two failing to follow the standard in different ways makes them not play well together.
posted by scottreynen at 5:10 PM on December 5, 2007

One thing I've found helpful to find the problem was to take one or two lines of the excel file, try to export them as a csv file that imports through phpmyadmin.

The last time I had this problem, it was because the number of fields was off (but you've already checked that). Try playing around with quoting out your sample data.
posted by drezdn at 5:55 PM on December 5, 2007

I had similar issues with a one-off import and found a tool that worked for me: SoftGalaxy's Excel MySql Converter. It's not free ($38) but it did the job & saved a lot of time.
posted by cbrody at 6:28 PM on December 5, 2007

I like HeidiSQL: a free, easy to use MySQL GUI. I've imported cvs files with it before, and it worked very well.
posted by Yavsy at 7:45 PM on December 5, 2007

I made a simple web based tool to do exactly this; an online utility to turn a spreadsheet into a MySQL table.

(I'm probably a year too late to help you, but I was excited to see someone having a use for my tool)
posted by GregX3 at 5:34 PM on November 20, 2008