By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,591 Members | 629 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,591 IT Pros & Developers. It's quick & easy.

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

P: 1
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?
Aug 25 '17 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 1,035
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'):
Expand|Select|Wrap|Line Numbers
  1. a,b,c
  2. 1,2,3
  3. 4,5,6
And an awkfile ('csvtosql.awk') like this:
Expand|Select|Wrap|Line Numbers
  1. FNR==1 {
  2.         s = "CREATE TABLE " FILENAME "(";
  3.         for(i=1; i<=NF; i++) {
  4.                 s = s $i " VARCHAR(50),";
  5.         }
  6.         s = substr(s,0,length(s)-1);
  7.         s = s ");"
  8.         print s;
  9. }
It's possible to do this:
Expand|Select|Wrap|Line Numbers
  1. gawk.exe -F "," -f csvtosql.awk test.csv
  2. 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.
Oct 28 '17 #2

Post your reply

Sign in to post your reply or Sign up for a free account.