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.