Importing large CSV text files into mysql

Forum Moderators: open

Message Too Old, No Replies

Importing large CSV text files into mysql

dougmcc1

3:28 am on Oct 9, 2008 (gmt 0)

What's the best way to get large CSV files into a mysql database? Some of these text files get as large as 50MB or more.
I can request the data to be sent to me in an XML format instead of CSV but I dont know if that's any better.

The files come from Commission Junction so I have no way of modifying them myself except manually in Excel which would be too time consuming.

Anyone else run into a similar situation and find a workaround? My thinking is that this must be a common issue.

Thanks.

rocknbil

3:16 pm on Oct 9, 2008 (gmt 0)

mysqlimport [dev.mysql.com]?

Usually I just script a solution to do this, because it allows me to filter and check the data as I import it and print reports on errors. Choose your language, PHP, ASP, perl.

- Open & read .csv line by line
- split the fields into an array (skip header!)
- build select statement

lethal0r

11:28 am on Oct 10, 2008 (gmt 0)

I seem to recall reading that xml is faster than csv too - if you have data in both formats you will of course be able to check for yourself.