How to create a CSV file based on row in shell script?

Question

I have a text file /tmp/some.txt with below values

JOHN              YES     6          6            2345762
 SHAUN             NO     6          6            2345748

I want to create a csv file with below format (i.e based on rows. NOT based on columns).

JOHN,YES,6,6,2345762
SHAUN,NO,6,6,2345748

i tried below code

for i in `wc -l /tmp/some.txt | awk '{print $1}'`
do
awk  'NR==$i' /tmp/some.txt | awk '{print $1","$2","$3","$4","$5}' >> /tmp/some.csv
done

here wc -l /tmp/some.txt | awk '{print $1}' will get the value as 2 (i.e 2 rows in text file). and for each row awk 'NR==$i' /tmp/some.txt | awk '{print $1","$2","$3","$4","$5}' will print the 5 fields into some.csvfile which is separated by comma.

when i execute each command separately it will work. but when i make it as a shell script i'm getting empty some.csv file.

why don't you just replace all blocks of spaces with a single comma? — fedorqui
– fedorqui, Commented Jan 9, 2017 at 14:07

RavinderSingh13 · Accepted Answer · 2017-01-09 14:12:00Z

2

@Kart: Could you please try following.

awk '{$1=$1;} 1' OFS=,   Input_file  > output.csv

I hope this helps you.

answered Jan 9, 2017 at 14:12

RavinderSingh13

135k14 gold badges61 silver badges100 bronze badges

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

Comments

Cyrus · Accepted Answer · 2017-01-09 14:14:07Z

2

I suggest:

sed 's/[[:space:]]\+/,/g' /tmp/some.txt

edited Jan 9, 2017 at 14:14

answered Jan 9, 2017 at 14:08

Cyrus

90.2k15 gold badges112 silver badges173 bronze badges

Comments

charli · Accepted Answer · 2017-01-09 14:13:27Z

1

You almost got it. awk already process the file row by row, so you don't need to iterate with the for loop.

So you just need to run:

awk '{print $1","$2","$3","$4","$5}' /tmp/some.txt >> /tmp/some.csv

answered Jan 9, 2017 at 14:13

charli

1,7881 gold badge13 silver badges23 bronze badges

1 Comment

charli Over a year ago

That's true, it's a better answer. I just used the same command @Kart wrote.

heemayl · Accepted Answer · 2017-01-09 14:14:33Z

1

With tr, squeezing (-s), and then transliterating space/tab ([:blank:]):

tr -s '[:blank:]' ',' <file.txt

With sed, substituting one or more space/tab with ,:

sed 's/[[:blank:]]\+/,/g' file.txt

With awk, replacing one ore more space/tab with , using gsub() function:

awk 'gsub("[[:blank:]]+", ",", $0)' file.txt

Example

% cat foo.txt
JOHN              YES     6          6            2345762
SHAUN             NO     6          6            2345748


% tr -s '[:blank:]' ',' <foo.txt                     
JOHN,YES,6,6,2345762
SHAUN,NO,6,6,2345748

% sed 's/[[:blank:]]\+/,/g' foo.txt                   
JOHN,YES,6,6,2345762
SHAUN,NO,6,6,2345748

% awk 'gsub("[[:blank:]]+", ",", $0)' foo.txt
JOHN,YES,6,6,2345762
SHAUN,NO,6,6,2345748

answered Jan 9, 2017 at 14:14

heemayl

42.5k10 gold badges86 silver badges87 bronze badges

1 Comment

fedorqui Over a year ago

Nice! I wasn't aware that you can squeeze and replace in one shot with tr.

Collectives™ on Stack Overflow

How to create a CSV file based on row in shell script?

4 Answers 4

Comments

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related