Tuesday, 23 January 2007

Bulk Import to MySQL

2 posts in a day! Ring the bells.

Just a quick post because I have found a nice solution to a problem I have encountered a lot and that is that when you move from MS SQL server to MySQL you tend to miss some of the nice quick data import systems in DTS. A typical scenario is how do I bulk import into MySQL from an excel spreadsheet which someone has given me?

The solution is scary but quick and described nicely here. Essentially save your spreadsheet as a csv file, FTP it up to your MySQL server and start an SSL session. Log into MySQL on the command line and switch to your intended target database with "USE database name" then use the command "load data local infile 'Path to csv' into table table name fields terminated by ',' lines terminated by '\n' (field name,field name...........);". Hey Presto its in as long as your fields are in the correct order, one nice aspect is it even allows you to import into auto increment fields.


1 comment:

bronius said...

> ..start an SSL session..

Minor typo: I think you mean SSH
Thanks for sharing..

A view from the rack is the personal blog of an IT manager who works for a pub company - hence beer