mcse2007
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?
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?
Are you saying that the csv shows, literally, a value of "," including the quotes?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@peter57r:, yes, but no quotes.
@gavsmith, that's already configured.. no thousands separator..
Please see attached file
test.bmp
@gavsmith, that's already configured.. no thousands separator..
Please see attached file
test.bmp
What is wrong with that comma? It is just the separator from the next value.
mlmcc
mlmcc
ASKER
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
mlmcc
ASKER
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
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
Do you have a field in the report to the right of the last number that is maybe always blank?
mlmcc
ASKER
I checked in CR and nothing seems to indicate that there is a blank column in my report.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
Also if you export the report from Crystal straight to CSV do you get the extra comma?
ASKER
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
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
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
mlmcc
ASKER
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
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.jsp a?threadID =957336