Iterate lines on csv to save columns as variables

Question

I have a csv composed as follows:

Column1,Column2,Column3
A Existing text in Column1, A Date in Column2, A Integer in Column3
B Existing text in Column1, B Date in Column2, B Integer in Column3
C Existing text in Column1, C Date in Column2, C Integer in Column3

I'm trying to save each line as a variable with a for Loop as it iterates through the file:

for i in `cat file.csv`
    do
        VARIABLE=$(echo $i && echo ", Another text")
    done

Instead of the variable being saved as (e.g. VARIABLE through first iteration): A Existing text in Column1, A Date in Column2, A Integer in Column3, Another text

It's saving as:

Column1,Column2,Column3
 , Another Text
A
 , Another Text
Existing
 , Another Text
text
 , Another Text
in
 , Another Text
Column1,
 , Another Text
A
 , Another Text
Date
 , Another Text
in
 , Another Text
Column2,
 , Another Text
A
 , Another Text
Integer
 , Another Text
in
 , Another Text
Column3
 , Another Text
B
 , Another Text
Existing
 , Another Text
text
 , Another Text
...(continues)

Is there a specific reason why the for loop is braking each work instead of just treating the entire line as a whole?

Type echo $(cat file.csv) at the shell prompt, and see what that little portion of code translates into as you run it. To get up and running with bash coding, please check the bash guides at www.tldp.org — Hannu, Nov 26 '21 at 22:11
What do you want to do with VARIABLE? Are you writing it directly to another file? — Zach Young, Nov 29 '21 at 23:53

score 1 · Answer 1 · answered Nov 26 '21 at 23:57

The problem is the setting of $IFS:

echo -n "$IFS" | od -t c -t x1
0000000      \t  \n
         20  09  0a

The output of cat is split at each space, tab, or newline. You want it split only at newlines. So this would work:

IFS='
'
for i in `cat file.csv`
    do
        VARIABLE=$(echo $i && echo ", Another text")
    done

Bu that is not a good approach. You should read lines. And you should also not use command substitution and echo for combining variables.

while IFS= read -r line; do
    VARIABLE="${line}, Another text"
done <file.csv

score 0 · Answer 2 · answered Nov 30 '21 at 00:06

If all you want to do is effectively add another column to your CSV, try a tool meant for working w/CSV.

GoCSV's add command can do this easily:

gocsv add -n 'Column4' -t ' Another text' sample.csv

gives me:

Column1,Column2,Column3,Column4
A Existing text in Column1," A Date in Column2"," A Integer in Column3"," Another text"
B Existing text in Column1," B Date in Column2"," B Integer in Column3"," Another text"
C Existing text in Column1," C Date in Column2"," C Integer in Column3"," Another text"

If you don't want to treat it as an actual CSV file (which will require all the double quotes because of the leading spaces), you can try this:

sed 's/.$/, Another text/' sample.csv

Column1,Column2,Column3, Another text
A Existing text in Column1, A Date in Column2, A Integer in Column3, Another text
B Existing text in Column1, B Date in Column2, B Integer in Column3, Another text
C Existing text in Column1, C Date in Column2, C Integer in Column3, Another text

But!, that only works if your file has a trailing/terminal newline. Otherwise, that will mangle the last character (in your last column, in your last line).

Iterate lines on csv to save columns as variables

2 Answers2