Problem with editing CSV file

VijayB · 02-07-2015, 06:50 AM

Hi,

I am very new to working with CSV files with bash.
Currently i have a CSV file Which has values in first column & based on the part of values in 1st column i need to fill constant values in corresponding columns. I can do that when all the Enteries in first column are unique but when the type of values are of same type then only one of the value is populated.

Sample CSV file with the name vija.csv:

Car_new
Mobile_new
Mobile_new_1
Bike_new
pen_new
pen_new_1

I Tried following code :

Code:

#!/bin/bash

homeDir="$HOME"

OLDIFS=$IFS
IFS=","

while read item

do

a="$(echo $item | awk -F_ '{print $1}')"

if [ "$a" == "Car" ] 
then
echo -e "$item,honda,delhi,india" > ~/test_113.csv

elif [ "$a" == "Mobile" ]
then
echo -e "$item,Samsung,kolkata,india" > ~/test_114.csv

elif [ "$a" == "Bike" ]
then
echo -e "$item,Bajaj,delhi,india" > ~/test_115.csv

elif [ "$a" == "pen" ]
then
echo -e "$item,Jetter,Mumbai,India" > ~/test_116.csv

else

echo "Please chek the item names"

fi

cat ~/test_113.csv ~/test_114.csv ~/test_115.csv ~/test_116.csv  > ~/items.csv

done < ~/vija.csv

IFS=$OLDIFS

The Output file that i get is :

Car_new honda delhi india
Mobile_new_1 Samsung kolkata india
Bike_new Bajaj delhi india
pen_new_1 Jetter Mumbai India

As you can see the file has only one entry starting from Mobile & pen but i want all the enteries in the file.

Can somebody please help me on this.

Thanks,
VijayB

AnanthaP · 02-07-2015, 07:27 AM

Naturally. Use >> (append) instead of > (overwrite) to allow duplicates multiples.

But yours seems a roundabout way. Can you give an example with data of the input and desired output?

OK

VijayB · 02-07-2015, 07:35 AM

Thanks AnanthaP.

Input is a csv file that has item names:

Car_new
Mobile_new
Mobile_new_1
Bike_new
pen_new
pen_new_1

& the Desired output is a csv file with following data:

Car_new,honda,delhi,india
Mobile_new,Samsung,kolkata,india
Mobile_new_1,Samsung,kolkata,india
Bike_new,Bajaj,delhi,india
pen_new,Jetter,Mumbai,India
pen_new_1,Jetter Mumbai,India

Thanks in Advance.
VijayB

AnanthaP · 02-07-2015, 08:03 AM

Its roundabout since you are having too many intermediate files. A simple awk will do it.

The logic will be
if(substr($0,1,3)==pen") printf("%s,Jetter,Mumbai,India\n" $0)
and so on.

Another thing is that you can save the awk script as a text file and run it iwth the -f (not -F) option.

I leave you to check the syntax and write the actual code yourself.

OK

grail · 02-07-2015, 09:59 AM

Maybe it is obvious, but the input file is not a csv file at all, ie. it has zero commas in it. This would also imply the setting and unsetting of IFS is also not required, at least not to a comma (hint).

As for the solution, either bash (if it has to be) or awk on its own could solve this issue. There would be no reason to combine both in such a simple script.

Should you choose to use just bash, may I suggest looking into the case statement, instead of your several if's.

I also agree with AnanthaP, that not only is there too much use of additional files, but they are not required and also you never clean up after yourself so every time you run your current script
you will have 4 additional files left lying around.