Newbie Question on Extra Space After Numeric Values

  • Thread starter Thread starter Yellowbird
  • Start date Start date
Y

Yellowbird

Newbie question, but can't find an answer.

I have an Excel file created via export from Crystal Reports that now
needs to be saved as a CSV file. I've got that part down, but my
numeric values in my final CSV file have an extra space before the
comma and I'm not sure how to get rid of it.

The problem is that in Excel, the columns are formatted as numeric
values in the format 0.00. So I have three columns of values that look
like:

3.45 0.00 string
1.00 0.00 string
9.50 0.00 string
10.00 0.00 string
4.25 0.00 string

When I export the spreadsheet to the flat file CSV format, I get the
values with an extra space before the comma, as in:

3.45 ,0.00 ,string,
1.00 ,0.00 ,string,
9.50 ,0.00 ,string,
and so on

What I really want is:

3.45,0.00,string,
1.00,0.00,string,
9.50,0.00,string,

The info I found on the Web indicates that Excel inserts that extra
space so that numeric values will be properly aligned on the decimal
point and so that there will be enough space between the last numeric
and the start of the next column. It doesn't do that for string
values.

Is there a way to get this to format properly without the extra space?

Thanks in advance,
Yellowbird
 
I'm not seeing that behavior in Excel 2002. When I export
to CSV, there is no extra space in front of the comma. I
have used the "general" and "Number with 2 decimals"
and "text" formats in the cells, and there is never a
space before the comma. Sorry I can't help
 
Yellowbird,

Try changing your number format in Excel. I used the format #,##0.00 then
exported the data as a CSV file. When I opened the file with Notepad, I got
this result:

3.45,0.00,string
1.00,0.00,string
9.50,0.00,string
10.00,0.00,string
4.25,0.00,string

I think this is what you want.

Charley Kyd
 
Thanks, Charley Kyd. That did it. Because of the way the numbers are
formatted in their native application (Crystal Reports, with data
extracted from a SQL database), it seems that when I export to Excel,
the numbers are formatted as Custom, 0.00_);0.00 and then when I Save
As CSV without reformatting the numbers, the extra space is retained.
It would be nice if the numbers were formatted properly when they came
into Excel (as ###0.00) with no comma separator. I don't suppose there
is a way to set that preference prior to converting the Crystal Report
to Excel, is there?

Thanks.
Yellowbird
 
Back
Top