0

I have a 13gb .txt file which I am importing into MySQL, however I don't want to import all of the data. For example there are many columns that are either completely empty or contain irrelevant information - I only want to import ~100/360 I've been provided. If I only create headers for the columns I want, can I select the specific corresponding data from the .txt file to be uploaded?

Normally I would use a text editor to remove the superfluous data, but I do not possess a text editor that can handle a file of this size.

tlaw the outlaw
  • 95
  • 2
  • 10
  • show a sample which lines ok and which not. then you can filter it with grep,awk or sed and direct import this filtered data – Bernd Buffen Feb 09 '17 at 15:19
  • 1
    Refer the second answer of the [thread](http://stackoverflow.com/questions/4202564/how-to-insert-selected-columns-from-a-csv-file-to-a-mysql-database-using-load-da) – SQL.RK Feb 09 '17 at 15:21

1 Answers1

0

You can ignore specific columns in the input file by assigning them to a user-defined variable instead of a database column.

For example if you had a CSV file with 4 columns and just wanted to import columns 1 and 4 into your table you could do something like this:

load data infile '/tmp/so42140337.csv'
into table so42140337
fields terminated by ','
lines terminated by '\n'
(c1,@dummy,@dummy,c2);

Given the size of your input file it may be more efficient to import it in chunks rather than importing the entire file in one command. You can use the pt-fifo-split tool for this, following the pattern in this blog post.

Ike Walker
  • 64,401
  • 14
  • 110
  • 109