Business Objects exports excel with extra line at the end, but opening excel to edit this results in type mismatch on zip and NAICS code

Question

We are FTPing the results of a report in CSV format to an external system that uses an ETL process to post the results online.

The report is generated in Business Objects which saves the report in excel format but has an extra line at the end which the ETL does not like, so usually the extra line is stripped from the report using excel and resaved to CSV format for FTP.

However after the recent excel updates, it auto converts ZIP code and another column to number format to strip leading zeros. If I add apostrophe to the front of the zip code, then the ETL will fail because there is an extra character to the 5 char zip.

How do I turn the auto conversion off in excel when saving to CSV?

I tried saving to CSV directly in BO but BO saves everything with quotes now in CSV which also makes the ETL fail, some of the columns have to be numeric. Adding apostrophe will show up in the CSV file.

Answer 1

A useful way to avoid number conversion is to use the "TEXT" formula to convert the desired value to a text string with an specific number formating. For example, to convert 5-digit values to a text string would be:

=TEXT(cell_ref, "00000")

Otherwise, you could specify the format of the range of values in the column in the Number format menu, specifing that the value requires five digits, i.e. "00000".

I know this doesn't solve the question to stop number auto-conversion but I hope it helps!

Answer 2

Have you tried exporting from BusinessObjects with a "Blank" text qualifier to eliminate the quotes?