Importing a very large tab delimited file into MySQL WITHOUT first creating a table

Question

I have a very large tab delimited file. It's about 500 columns wide, and perhaps a million records long. It's a mix of text, dates and numbers. I'm actually only interested in a few columns from inside that - the rest will get deleted.

I want to import it into MySQL, but it's a one-off (perhaps 2- or 3-off) task and I can't really be bothered to create an empty table for a LOAD DATA INFILE command.

What is the lowest-effort method of achieving this? Can LOAD DATA INFILE be made to create a table on-the-fly with VARCHAR/TEXT columns or similar?

Answer 1

You have to create a table. But if you only need certain columns, you can select them.

Create a table of the desired columns.

the you can run the LOAD DATA like this.

LOAD DATA LOCAL INFILE 'import.csv' INTO TABLE yournewtable
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'  
(@col1,@col2) set Colum1=@col1,Column2=@col2;

@col1,@col2 are variables from the CSV. Colum1,Column2 represents the columns in the table you created.