Database generation from non-consistent csv files

I have many different csv files that I would like to convert in to database tables.

The csv files are not always consistent, meaning that the columns may not be in the same place all the time. For example:

--First scenario--
col1, col2, col3, col4, col5
1,2,3,4,5

--second scenario--
col3,col4,col1,col5,col2
3,4,1,5,2

--The scenario's are endless and it is no possible to predict what order the columns will be in--

The information is the same but the order in which the data is placed in the csv file is different. I need to be able to generate database tables that are consist开发者_运维技巧ent from the csv files, so that no matter what order the columns are in, when the table is created it will always be in one format.

I was thinking I could generate a temporary table from the csv file and then convert the temporary table in to my required format. But I'm unsure on how to do that, or if that is possible.

Thank you for your time, hope someone will be able to help or offer some suggestions on how to resolve this problem.

If you can use the first line to figure out how to order the columns programmatically, you'll be in good shape. I don't think there's a good way to do this automatically or directly in MySQL. Second, you can load the data without rearranging it, just set the column names in your SQL:

LOAD DATA INFILE 'file.txt' INTO TABLE table (col3, col1, col2, col4)

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Might need FIELDS TERMINATED BY ',' to change from TSV to CSV as the default format.

It sounds like you have some very tedious work in front of you.

You may be able to speed the cleanup by first opening the files in Excel, rearranging the columns and then re-saving as CSV.