Create CSV from specific columns in another CSV using shell scripting

Question

I have a CSV file with several thousand lines, and I need to take some of the columns in that file to create another CSV file to use for import to a database.

I'm not in shape with shell scripting anymore, is there anyone who can help with pointing me in the correct direction?

I have a bash script to read the source file but when I try to print the columns I want to a new file it just doesn't work.

while IFS=, read symbol tr_ven tr_date sec_type sec_name name
do
    echo "$name,$name,$symbol" >> output.csv
done < test.csv

Above is the code I have. Out of the 6 columns in the original file, I want to build a CSV with "column6, column6, column1"

The test CSV file is like this:

Symbol,Trading Venue,Trading Date,Security Type,Security Name,Company Name
AAAIF,Grey Market,22/01/2015,Fund,,Alternative Investment Trust
AAALF,Grey Market,22/01/2015,Ordinary Shares,,Aareal Bank AG
AAARF,Grey Market,22/01/2015,Ordinary Shares,,Aluar Aluminio Argentino S.A.I.C.

What am I doing wrong with my script? Or, is there an easier - and faster - way of doing this?

Edit

These are the real headers:

Symbol,US Trading Venue,Trading Date,OTC Tier,Caveat Emptor,Security Type,Security Class,Security Name,REG_SHO,Rule_3210,Country of Domicile,Company Name

I'm trying to get the last column, which is number 12, but it always comes up empty.

the snippet looks fine to me. I even tested it and worked as expected. You can consider using this awk, though, a bit more straight forward: awk 'BEGIN{FS=OFS=","} {print $6,$6,$1}' test.csv > output.csv — fedorqui
– fedorqui, Commented Jan 24, 2015 at 19:45
You know what, I also found a solution with AWK and tried it, didn't work... but I just tried yours and it did! The other AWK solution I found was like this (only with different columns): awk -F, '{getline f1 <"test.csv" ;print f1,$3,$4}' OFS=, output.csv — andrux
– andrux, Commented Jan 24, 2015 at 19:48
Sure! This getline usage is quite unnecessary, so throw away that snippet :) — fedorqui
– fedorqui, Commented Jan 24, 2015 at 19:53

fedorqui · Accepted Answer · 2015-01-24 19:52:59Z

2

The snippet looks and works fine to me, maybe you have some weird characters in the file or it is coming from a DOS environment (use dos2unix to "clean" it!). Also, you can make use of read -r to prevent strange behaviours with backslashes.

But let's see how can awk solve this even faster:

awk 'BEGIN{FS=OFS=","} {print $6,$6,$1}' test.csv >> output.csv

Explanation

BEGIN{FS=OFS=","} this sets the input and output field separators to the comma. Alternatively, you can say -F=",", -F, or pass it as a variable with -v FS=",". The same applies for OFS.
{print $6,$6,$1} prints the 6th field twice and then the 1st one. Note that using print, every comma-separated parameter that you give will be printed with the OFS that was previously set. Here, with a comma.

answered Jan 24, 2015 at 19:52

fedorqui

293k113 gold badges592 silver badges640 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

andrux Over a year ago

Ok, I used the above CSV sample to simplify, and it does work using the command you said, but when I modify it to the real file, it does't.. these are my headers:

andrux Over a year ago

I updated the question, with the correct headers and the problem that the very last column always comes up empty... any idea what I'm doing wrong? This is my final awk command: awk 'BEGIN{FS=OFS=","} {print $12,$6,$1}' test.csv > output.csv

fedorqui Over a year ago

It is difficult to say from the input. But if you want to print the last field, you can use {print $NF} in awk. As NF refers to the number of fields, $NF contains the last one. So for example, do write the following and check if the file really contains 12 fields: awk '{print NF}' and expect 12. Otherwise, check what is the 12th field saying awk '{print $NF}'. Always with the BEGIN{} block, of course

andrux Over a year ago

Ok, it was newlines that were acting up... I couldn't use dos2unix on my mac, I ran cat test.csv | col -b > x.csv and after that your awk ran great!

andrux Over a year ago

Thanks fedorqui, I will save this awk command for future reference :)

Collectives™ on Stack Overflow

Create CSV from specific columns in another CSV using shell scripting

Edit

1 Answer 1

Explanation

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Edit

1 Answer 1

Explanation

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related