CSV file - numbers question

Shell Programming and Scripting
1

Gurus,

I have the following file in csv format, to be convert from cvs to excel.

abc,summer,tennis,123456789,winter
clearsky,fall,computer,43322,monitor

I need to place commas on numbers so that they be read properly in excel.
The number column output I need to see in excel should have for numbers
as follows

abc summer tennis 123,456,789 winter
clearsky fall computer 43,322 monitor

The problem I have is, if I place commas like 123,456,789 in csv file and export it then it would consider each of these digits as a separate column and that would mess up the output.

PS: I am using csv2excel scrip to convert to excel.

Any ideas would be appreciated.

2

Surely the comma thousands separator is something you change in the column format in the Excel spreadsheet itself not in the input data.

3

Place the numbers within double quotes. Assuming the numbers are in the fourth field:

awk -F, '{$4=sprintf("\"%\047.0f\"",$4)}1' OFS="," file > file.csv
1 Like
4

Franklin52,

Thanks for your reply. Your commad did work corrects and it placed the commas as expected as below in csv file

abc,summer,tennis,"123,456,789",winter
clearsky,fall,computer,43,322",monitor

But when trying to convert to xls using csv2xls script and then viewing in Excel,
it treated 123 as separate columen, 456 as another column, so also 789.

That brings back me to the same point, how to introduce commas in numbers without excel treating as separate columns.

thanks

---------- Post updated at 01:51 PM ---------- Previous update was at 01:49 PM ----------

Methly,

Thanks for you feedback, after converting to Excel I do not want to format columns manually since this script has to run everyday. I was trying to find a way to convert from csv to excel withou manipulting in excel.

thanks.

5

Hi.

I can't be very sure (since I don't have access to a Spreadsheet right now), but if you added an equal sign:

$ awk -F, '{$4=sprintf("=\"%\047.0f\"",$4)}1' OFS="," file

Would that help?

6

Try another OFS (Output Field Separator).

What is your default field separator of csv files?

7

Franklin52,

The default field seapartor is comma. This is how I get the raw file from I need to start processing file.

If I need to change the field separator in my raw file .csv how can I do that?

thanks

8

Strange, in excel the number field is placed in one cell on my windows system...:confused:

9 
awk  '
BEGIN{FS=OFS=","} 
{for (i=1;i<=NF;i++) 
    { 
       if (i==4) $i=sprintf("\"%\047.0f\"",$4)
       else $i="\"" $i "\""
    }
}1' infile

"abc","summer","tennis","123,456,789","winter"
"clearsky","fall","computer","43,322","monitor"