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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.