Does anyone know of a tool for working with CSV files and then importing them into MySQL?
I’ve got a flatfile database which has a series of fields that I need to import into MySQL.
Most of the fields in the orginal will map to new ones in the MySQL, while some on both sides will be ignored
There’s about a thousand entries in the database, so I’d rather not do it manually, as it’s not simply a matter of copying and inserting – I will also have to manually change one element associated with each entry….
Any suggestions would be welcome if they run on windows or linux (I don’t have OSX yet)
CSV / MySQL Tools?
sqlyog has CSV importing as far as I remember (I use the command line now rather than a GUI.)
A low tech way though is to open the CSV in Excel or some spreadsheet app. You then put your “INSERT INTO abc” command in the first column and then break up the rest of the columns with new columns having “‘,'” in them. Remember to include brackets where necessary. Do it for one line and then use Excel’s repeat-copy feature to copy it all the way down. I then save it as a text file and run it through MySQL. It can be a bit tricky getting the quotes right but it works.
Try the LOAD DATA INFILE command http://dev.mysql.com/doc/refman/5.0/en/load-data.html
Or if you have more time on your hands get to know Pentaho’s Kettle tool http://kettle.pentaho.org/
It’s a powerful open source GUI fronted ETL tool, will load almost any format into any database.
Tom
I generally use a mixture of Openoffice and bash tools when I want to work with csv files. Awk is wonderful for reaaranging columns!
I’ll bring in a copy of Perl For Dummies to you 😉
Niall
Does that come in a “retards” edition? 🙂
Donncha – I’ve been trying something similar but ended up losing an entire field…
Tom – I couldn’t find any sign of kettle on their site, but thanks anyway
Paul – Too much like hard work 🙂 It would have been as easy to copy and paste into the new DB
The might have it in a “Cark” edition all right 🙂
We managed to get a list of 50,000 feeds into a database using the Excel/OpenOffice method. Took about 10 minutes to setup.
You could always post it on Mechanical Turk or RentACoder and pay 1c a line 😉
I had to do something similar a few months back and ended up using the LOAD DATA INFILE command as suggested by Tom above. I had a CSV file with around 1000 entries and around 10 fields per entry.
The first thing I did was to create a new table describing the various fields I planned to import. This was the trickiest part.
MySQL> create table players (name varchar(20), club varchar(20), value double);
Them, I imported from the file as follows:
MySQL> load data local infile “abc.csv” into table players fields terminated by ‘,’;
The key things here are the use of the “local” keyword before the infile and the “fields terminated by ‘,’
Best of luck
I found myself in the same situation and hacked a small utility to import CSV files to MySQL.
It recognizes fieldtypes and -lengths automatically on import.
Drawback, because my English is really bad, i have coded it in German language.
Youll find the tool here:
http://mcyra.ath.cx/csvutil/index.html
And can download it here:
http://mcyra.ath.cx/dl/download.php?dldfile=3