Converting txt file to Excel file

Shell Programming and Scripting
1

Hi All,

I have a text file with below content.

TradeDate Name    SecurityMnc  ReasonDesc        
=======================================
20120501   Robin     ABC      FO System Defect  
20120502   Robin     ABC      FO System Defect

I would want this in an excel file in 4 columns, am trying some thing with AWK but problem is am getting like below. This is causing excel file to display it in 6 columns.. last field is considering as 3 columns :frowning:

TradeDate,Name,SecurityMnc,ReasonDesc        
=======================================
20120501,Robin,ABC,FO,System,Defect  
20120502,Robin,ABC,FO,System,Defect

But i need like below.

TradeDate,Name,SecurityMnc,ReasonDesc        
=======================================
20120501,Robin,ABC,FO System Defect  
20120502,Robin,ABC,FO System Defect

Any help would be greately appreciated. Thanks.

2

it will come like that only because is has space between that field. either separate that file with valid separator or use double quotes for the last field.

another way you can do is -

store first three fields in three variables and for 4th field try -

`cut -d" " -f4-`

then try to put every variable in excel file. hope that will work.

1 Like
3

Try this ...

awk '{
if(NR>2){
if(NF==4) { print $1,$2,$3,$4 } else {
printf $1","$2","$3",";
for(i=4;i<=NF;i++) {
printf i==NF?$i"\n":$i FS
}
}} else {print} }' OFS="," filename
1 Like
4

Assuming you can remove the ===, as I'm not really sure why they're there in a csv. Would make your headers 2+ spaces apart as well for consistency:

$ cat test
TradeDate Name    SecurityMnc  ReasonDesc
20120501   Robin     ABC      FO System Defect
20120502   Robin     ABC      FO System Defect

$ cat test | sed 's/\ \ \ */~/g'
TradeDate Name~SecurityMnc~ReasonDesc
20120501~Robin~ABC~FO System Defect
20120502~Robin~ABC~FO System Defect

$ cat test | sed 's/\ \ \ */~/g' | awk -F"~" '{print $1","$2","$3","$4}'
TradeDate Name,SecurityMnc,ReasonDesc,
20120501,Robin,ABC,FO System Defect
20120502,Robin,ABC,FO System Defect

Could make ~ whatever character or whatnot, I just used that arbitrarily.

1 Like