CODEWITHSUNDEEP
mysqlsqlshell
user3259040
user3259040

Reputation: 155

Add two columns in text file and load to sql

I am trying to add two columns and then enter them into mysql on my linux system.

id , v1 , v2 , v3 , v4 , v5 , v6
5  , 8  , 1  , 9  , 9  , 9  , 6
1  , 4  , 9  , 3  , 5  , 1  , 7
7  , 7  , 2  , 9  , 9  , 2  , 3
8  , 1  , 5  , 1  , 6  , 4  , 7
1  , 3  , 5  , 6  , 4  , 7  , 3
5  , 10 , 6  , 8  , 6  , 3  , 5
4  , 5  , 2  , 1  , 9  , 4  , 2

I would like to add v1 and v2 and enter this to mysql. ie the actual data loaded to sql would be this:

id , newval , v3 , v4 , v5 , v6
5  , 9      , 9  , 9  , 9  , 6
1  , 13     , 3  , 5  , 1  , 7
7  , 9      , 9  , 9  , 2  , 3
8  , 6      , 1  , 6  , 4  , 7
1  , 8      , 6  , 4  , 7  , 3
5  , 16     , 8  , 6  , 3  , 5
4  , 7      , 1  , 9  , 4  , 2

Any idea how I can use this directly using LOAD TABLE? My current workaround is thus:

cat a.txt  | awk -F "," '{print $1","$2+$3","$4","$5","$6","$7}' > b.txt
mysql> LOAD DATA LOCAL INFILE '/path/b.txt' INTO TABLE values;

Thanks in advance DY

Upvotes: 1

Views: 83

Answers (1)

Jordan Running
Jordan Running

Reputation: 106027

This is covered in the documentation for LOAD DATA INFILE... which is unfortunately really poorly organized. The short answer is that you can assign columns to variables (e.g. @v1) and then use those variables in a SET clause to set column values programmatically. I'll just quote the relevant section from the docs:

The column list can contain either column names or user variables. With user variables, the SET clause enables you to perform transformations on their values before assigning the result to columns.

User variables in the SET clause can be used in several ways. The following example uses the first input column directly for the value of t1.column1, and assigns the second input column to a user variable that is subjected to a division operation before being used for the value of t1.column2:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @var1)
  SET column2 = @var1/100;

So in your case you'd want something like this:

LOAD DATA LOCAL INFILE '/path/b.txt'
  INTO TABLE values
  (id , @v1 , @v2 , v3 , v4 , v5 , v6)
  SET newval = @v1 + @v2;

This sets the id, v3, v4, v5, and v6 columns from the 1st and 4th through 7th columns in the input file, respectively, and assigns the 2nd and 3rd columns to the variables @v1 and @v2. Then it sets the newval column to the result of @v1 + @v2.

I hope that's helpful.

Upvotes: 2

Related Questions