awk and sed problem

Shell Programming and Scripting
1

Hi,
I am able to generate a csv file which has 3 columns. But the header is not coming out exactly i.e it doesnot have a comma in between it but the data is getting produced with the comma.
The first line i.e the header is coming out as:-
Counter Number Counterparty1 Counterparty2

where Counter Number is the first column, Counterparty1 is the 2nd and Counterparty2 is the 3rd column

The process that i followed is:-
HEADER1="Counter Number"
HEADER2="Counterparty1"
HEADER3="Counterparty2
HEADER="$HEADER1,$HEADER2,$HEADER3"

Then I am deleting the first line from the csv file and then
echo $HEADER > output1.csv
cat input.csv >> output1.csv
mv output1.csv output.csv

It works , but I know that this is not the correct way. I think it can be done with awk and sed. So can somebody help me to put commas inbetween the headers in the first line of the csv...

Thanks

2

There's a missing double-quote at the end of HEADER3 value. It should throw an error in Bash -

$
$
$ cat input.csv
a,b,c,d
e,f,g,h
i,j,k,l
$
$ cat cp_with_header.sh
HEADER1="Counter Number"
HEADER2="Counterparty1"
HEADER3="Counterparty2
HEADER="$HEADER1,$HEADER2,$HEADER3"
echo $HEADER > output1.csv
cat input.csv >> output1.csv
mv output1.csv output.csv
$
$
$ . cp_with_header.sh
bash: cp_with_header.sh: line 4: unexpected EOF while looking for matching `"'
bash: cp_with_header.sh: line 10: syntax error: unexpected end of file
$
$

I am able to see the expected output after fixing it:

$
$
$ cat cp_with_header.sh
HEADER1="Counter Number"
HEADER2="Counterparty1"
HEADER3="Counterparty2"
HEADER="$HEADER1,$HEADER2,$HEADER3"
echo $HEADER > output1.csv
cat input.csv >> output1.csv
mv output1.csv output.csv
$
$ . cp_with_header.sh
$
$
$ cat output.csv
Counter Number,Counterparty1,Counterparty2
a,b,c,d
e,f,g,h
i,j,k,l
$
$

A shorter way in the shell could've been thus -

$
$
$ echo $HEADER
Counter Number,Counterparty1,Counterparty2
$
$
$ echo $HEADER | cat - input.csv
Counter Number,Counterparty1,Counterparty2
a,b,c,d
e,f,g,h
i,j,k,l
$
$
$ # just redirect the pipeline of commands to output.csv
$
$

Or if you want awk -

$
$
$ cat input.csv
a,b,c,d
e,f,g,h
i,j,k,l
$
$
$ awk -v HDR="$HEADER" 'BEGIN {print HDR} {print}' input.csv
Counter Number,Counterparty1,Counterparty2
a,b,c,d
e,f,g,h
i,j,k,l
$
$
$ # just redirect the output of the awk one-liner to output.csv
$
$

tyler_durden

3

@Tyler, I think he want to replace the first line with a new line.

awk 'NR==1{$1="Counter Number, Counterparty1, Counterparty2"}1' output.csv > tempfile && mv tempfile output.csv

If your sed version supports the -i option:

sed -i '1 s/.*/Counter Number, Counterparty1, Counterparty2/' output.csv

otherwise:

sed '1 s/.*/Counter Number, Counterparty1, Counterparty2/' output.csv  > tempfile && mv tempfile output.csv
4

@Franklin & Tyler,

Thanks for the reply.
Still it is hard coding in the script giving the header names directly. If that the case then my lines were working fine.

But i want a generic one which puts a comma between the header names i.e the first line of any csv file.
I just want to put a comma in column1, column2 and column3.
I had to generate 10 csv files and may be there can be more than three columns. So hard coding is not the option for me...

Thanks

Thanks

5

Difficult if the header has more names with spaces.

6

Atleast please tell me for the above three columns that I have posted, so that i can get some insight and idea....

Another thing that i can do is, I can delete the spaces between any header names as it comes from a stored procedure, so now if the header becomes..
CounterNumber Counterparty1 Counterparty2

then what will be the solution...

Thanks

7 
awk -vh="$HEADER" 'NR==1{$0=h"\n"$0}1' input.csv > output.csv
8

Hi,
I don't want to hard code the things.

I am able to generate a csv file where the data is coming in all the columns properly from the procedure.I am running the procedure in the shell script. But the header is not able to come in its respective columns. What i see that there is no comma in between the headers in the csv file.

I tried to do it with hard coding it- As i had did shown in the above post.

But i need more generic one because if somebody changes the stored procedure and the number of columns or the name of the columns then the script will fail...

9

Is that an Oracle stored procedure? Any way you can return a comma-delimited header from the stored procedure itself?

@Franklin52 - Thanks for the clarification.

tyler_durden

10

It is a sybase stored procedure...

Some lines of the stored procedure are:-

select
"Counter Number" ,",",
"Counterparty1" ,",",
.........
........
from table

This gives the data perfectly in columns. But the header is not able to come out in a perfect way.

When I open the csv file, I can see that commas are present in each and every line but the first line i.e the header doesnot contain any commas..

11

I don't know much about Sybase, but assuming it implements ANSI SQL, you might be able to use something like this -

select "Counter Number,Counterparty1,Counterparty2"
union all
select column1 || "," || column2 ||","|| column3
from table;

tyler_durden