CODEWITHSUNDEEP
mysqlload-data-infilecsv
StackG
StackG

Reputation: 2858

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

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?

Upvotes: 1

Views: 2067

Answers (2)

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7244

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.

Upvotes: 2

David Soussan
David Soussan

Reputation: 2736

LOAD DATA INFILE http://dev.mysql.com/doc/refman/5.5/en/load-data.html allows you to specify that only certain columns will be imported.

Upvotes: 0

Related Questions