Link to home
Start Free TrialLog in
Avatar of mcse2007
mcse2007Flag for Australia

asked on

Comma issue

Hi,

When I export my CR report into XL then save it as .CSV, the below formula produces a comma (,) when view from notepad:

if {@Extended_Cost} <> 0
then {@Extended_Cost} * .1 else 0


@Extended_Cost is.......
if {Sheet1_.Net value} <> 0
then {@subtotal} else 0

I cannot find anything in the format field to get rid off the comma.

Any idea on how to overcome this?
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Are you saying that the csv shows, literally, a value of   ","   including the quotes?
SOLUTION
Avatar of gavsmith
gavsmith
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mcse2007

ASKER

@peter57r:, yes, but no quotes.

@gavsmith, that's already configured.. no thousands separator..

Please see attached file
test.bmp
Avatar of Mike McCracken
Mike McCracken

What is wrong with that comma?  It is just the separator from the next value.

mlmcc
hey mlmcc, there is no next value as seen from CR report... the comma shouldn't be there.
Is the CSV file created in Crystal or Excel?

mlmcc
The CSV file is created in Excel.

It works like this...from CR, i export it as Excel (format & data only) and application.

When I view it from Excel, I go then save as .CSV
Excel seems to be exporting 1 extra column.  
Do you have a field in the report to the right of the last number that is maybe always blank?

mlmcc

I checked in CR and nothing seems to indicate that there is a blank column in my report.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
When you export the report from Crystal you should get some format options. Try changing the 'Column width based on objects in the:' option from 'Whole Report' to 'Details'. Does that change it?

Also if you export the report from Crystal straight to CSV do you get the extra comma?
I've tried the below and still get the same result, with comma

Export the report
Delete the column after the one with the last number.
Export to CSV

When I export, it is in details configuration. when I export from CR straight into CSV, it get worst, it produces more commas
Can you upload the report?

mlmcc
The fact that the CR export is worse, would seem to indicate there are fields or something to the right of the last field that is causing Crystal to add blanks tot he columns.  It only takes 1 value in the column to cause all rows to add the comma

mlmcc
the issue was when the report is exported, the HEADER has 1 extra column compare with the DETAIL when viewed in Excel. Once the report is saved as CSV, the comma appears....there is nothing i can do about it but leave with it.


thanks for the effort guys
I did mean to come back to this but I forgot, sorry, you mentioned the export to CSV provides more commas. Have you tried creating FakeGroups which helps sort out export to CSV problems in CR? there is also a reg key that you must set so that it exports correctly. Don't know if it will help you or not just a suggestion... it's explained here: http://forums.sdn.sap.com/thread.jspa?threadID=957336