Import specific columns from a text file into mysql .. is this possible?

Question

I just downloaded a bunch of text files from data.gov, and there are fields in the text file that I really don't need.

Is there a way to import the columns [1,3] and leave the rest?

I believe that I will import using the "upload data to file", but have not seen anything on the mysql page once I import certain columns. http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Fields are limited to ^. I just understand that if the line in the txt file

  00111 ^ first column entry ^ second column entry ^ this would be the 3rd column

I am trying to make the mysql table contain

  first column entry |  this would be the 3rd column

Answer 1

You can always create a table with dummy column (s) that you delete after downloading the file (provided that you do not need to download the file very often).

Something like that:

LOAD DATA LOCAL INFILE '/path/to/file' INTO TABLE table_name FIELDS TERMINATED BY '^' (dummy_column1, column1, dummy_column2, column2); ALTER TABLE table_name DROP dummy_column1; ALTER TABLE table_name DROP dummy_column2; 

Answer 2

You can import specific columns with:

 LOAD DATA LOCAL INFILE 'yourFile' INTO TABLE table_name FIELDS TERMINATED BY '^' (column1, @dummy, column3, @dummy); 

Put all the columns you don't need in @dummy.

Answer 3

Assuming a Unix platform, you can filter fields upstream.

 cut -d^ -f2,4 mygovfile.dat > mytable.txt 

To filter the first and third columns, then import using the preferred method. For instance,

 mysqlimport --local -uxxx -pyyy mydb --fields-terminated-by="^" mytable.txt .... 

Answer 4

The two most common ways to deal with this are:

• To import data as in a step, move what you need into your “real” tables, then truncate the intermediate table.
• Use a text utility to cut just what you need.

My text selection utility is awk. The minimal awk script - which probably won't work for you without any tweaking - will look like this.

 $ awk 'BEGIN { FS="^";OFS=",";}{print $2, $4}' test.dat first column entry,this would be the 3rd column 

What is the setting? This usually includes care of the embedded commas, single quotes, and double quotes.

This part

 BEGIN { FS="^";OFS=",";}{print $2, $4} 

is the whole awk program.

awk.