Different results exporting to CSV or Excel

Question

I have a simple report that I want to export to a CSV file. There is only the detail line that is grouped by one field, no group header, and a group footer for totals. The problem is when I export to CSV format, the total row for a group is listed in front of every record?

If I export to Excel and then save as a CSV file, the total row is where it belongs. However one field is spread across 3 columns then those columns are "merged and centered" which adds two commas in the middle of the line. And one column is added at the beginning of the record and two at the end of the record, for 3 more extra commas.

It would be easy enough to write a macro to "clean up" the spread sheet and export as a csv file for my end users. However corporate "insecurity" will not allow the end users to have macros.

Any help, suggestions, pointers to where else to look greatly appreciated.

cheers

bob

Answer 1

The CSV generated by any standard reporting tool does a flat data structure and hence would repeat all data set. The XLS generated by the reproting tools are typically to be opened in the XLS and its XLS default behaviour to put additional commas for every merged cell. The best way is to create a report with a layout that has equal data length columns even for the header, ie while formatting the report do not put the header in the center with larger lenght, bold and italics etc, put it as the first column and match the lenght with the data in the detail record. This way you would be able to create a report that does not look presentable in XLS but would give you required data in the CSV