Import csv data into MySql

Unusually for an asp.net developer, MySql is my database platform of choice – very quick and less server intensive that certain other database platforms I could mention…

Anyway here’s how I go about loading csv data into MySql

• Prepare your data in a program such as excel or open office
• Save as comma delimied or tab delimited in the file system where mysql stores the data, e.g. if the data is to go in a database called “test” then save the csv in C:\Program Files\MySQL\MySQL Server 5.0\data\test
• Create a temporary import table whose structure matches the file to import
• Make each field a varchar(255) even if the field contains numbers, we can always remove rogue data later – let’s just get it into the database first!
• Load in the data (note: this is ignoring the first line as it contains the column headers and is set for a tab delimited file)

LOAD DATA INFILE 'myimport.txt' INTO TABLE test.tempimporttable

FIELDS TERMINATED BY '\t'

LINES TERMINATED BY '\r\n'

IGNORE 1 LINES

This entry was posted by nerdboy on Friday, May 8th, 2009 at 8:16 pm and is filed under MySql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response below, or trackback from your own site.

Show/Hide Comments (0)