create a mySQL table from the field headings line of a CSV file

[thuylinhphuong]

What I'm looking for is a lazy man's way to define a table structure, using the table headings row in a CSV file. I'd be happy to start with all fields being VARCHAR(50) or some such default, and then making appropriate changes via PHPmyAdmin later.

The reason for being this lazy is the number of fields that I need to define - the CSV file is downloaded from a hotel booking affiliate, and contains every conceivable detail about the hotels. If they were also using mySQL, I could have asked if they'd provide a dump instead of a CSV file...

Anyone have any bright ideas?

[Luuk]

Ok , better late than never ;)

You can use a tool like gawk.exe for that.
(search Google for it, or find it here)

When you have a csv file like this ('test.csv'):

a,b,c
1,2,3
4,5,6

And an awkfile ('csvtosql.awk') like this:

FNR==1 {
        s = "CREATE TABLE " FILENAME "(";
        for(i=1; i<=NF; i++) {
                s = s $i " VARCHAR(50),";
        }
        s = substr(s,0,length(s)-1);
        s = s ");"
        print s;
}

It's possible to do this:

gawk.exe -F "," -f csvtosql.awk test.csv
CREATE TABLE test.csv(a VARCHAR(50),b VARCHAR(50),c VARCHAR(50));

Here the
-F "," specifies the field delimiter
-f csv2sql.awk references the awk-script
test.csv references the input file.