6

As the title says: I've got a bunch of tab-separated text files containing data.

I know that if I use 'CREATE TABLE' statements to set up all the tables manually, I can then import them into the waiting tables, using 'load data' or 'mysqlimport'.

But is there any way in MySQL to create tables automatically based on the tab files? Seems like there ought to be. (I know that MySQL might have to guess the data type of each column, but you could specify that in the first row of the tab files.)

AP257
  • 701
  • 2
  • 9
  • 13
  • If you want to get _all_ columns of a delimited text file into your database _quickly_, without having to create a table or think for even one minute about appropriate column types and sizes, [this SO answer](https://stackoverflow.com/a/23654356/785213)'s suggestion of using phpMyAdmin works fine. You will have to rename the table when you're done (if there's an option for it during import, I overlook it every time). The upload file size will however be restricted by your `php.ini` settings. _This is in a comment because the OP's question is "how do you [...] **in MySQL**"._ – TheDudeAbides Jan 24 '18 at 03:39

2 Answers2

3

No, there isn't. You need to CREATE a TABLE first in any case.

Automatically creating tables and guessing field types is not part of the DBMS's job. That is a task best left to an external tool or application (That then creates the necessary CREATE statements).

Pekka
  • 442,112
  • 142
  • 972
  • 1,088
2

If your willing to type the data types in the first row, why not type a proper CREATE TABLE statement.

Then you can export the excel data as a txt file and use

LOAD DATA INFILE 'path/file.txt' INTO TABLE your_table;
Eddie
  • 9,696
  • 4
  • 45
  • 58