Archive for May, 2009

Import csv data into MySql

Posted on May 8th, 2009 • Filed under MySql • No Comments

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