使用第一行标签从制表符分隔的文本文件创建数据库表

Question

I have a tab delimited text file with the first row being label headings that are also tab delimited, for example:

Name    ID    Money
Tom    239482    $2093984
Barry    293984    $92938

The only problem is that there are 30 some columns instead of 3 so I'd rather not have to type out all the (name VARCHAR(50),...) if it's avoidable.

How would I go about writing a function that creates the table from scratch in php from the text file, and say the function takes in $file_path and $table_name? Do I have to write all the column names again telling mysql what type they are and chop off the top or is there a more elegant solution when the names are already there?

Answer 1

You would somehow need to map the column type to the columns in your file. You could do this by adding that data to your textfile. For instance

Name|varchar(32)    ID|int(8)    Money|int(10)
Tom    239482    $2093984
Barry    293984    $92938

or something similar. Then write a function thet get's the column name and columntype using the first line and the data to fill the table with using all the other rows. You might also want to add a way to name the given table etc. However, this would probably be as much work (if not more) than creating SQL queries using you text file. Add a create table statement at the top and insert statements for each line. With search and replace this could be done very fast.