SAP Knowledge Base Article - Public

3195000 - SAC : CSV Export - Automatic conversion from text to number

Symptom

- From Stories or Analytics Designer, when exporting in CSV format, some text values are automatically converted into number format.
ex: '00' becomes '0', '01' becomes '1'
export_csv_01.jpg

- '00' should stay '00' and export should allow to keep formatting when exporting in CSV format.

Environment

  •  SAP Analytics Cloud - Analytics Designer

Cause

A CSV file is a raw file type and, in general, does not hold type information or any other meta data information on the data it provides. The interpretation of the text is done by the tool used (Excel) to look at the file. A simply text editor for example will show you exactly what was exported from SAC. SAC exports the data as you would expect - with all the zeros. However, by opening that file Excel interprets it as a number (and not as text) and removes the leading zeros.
SAC cannot influence the behavior of Excel. Instead of CSV export, using an .XLSX export should meet the expected behavior when dealing with leading zeros.

Resolution

Workaround :

  1. Make a note of where you saved/exported the CSV file, but do not open it.
  2. Open Excel
  3. Select the Data tab.
  4. Click on the From text icon to display the Import Text File dialog box.
  5. Select the CSV file to import and click Import
  6. From the Text Import Wizard, select the Delimited radio button -- Text Import Wizard, Step 1 determines that your data is delimited.
  7. Select the row number where you want the import to start.
  8. Check the My data has headers. box if true. then review the preview of the file at the bottom of the box.
  9. Click Next
  10. Check "Comma" as a delimiter (column dividers will appear in preview)-- Step 2 lets you set delimiters
  11. Click Next
  12. Highlight the column(s) with leading zeros in Step 3
  13. Mark those columns format as "text" by clicking the radio button in the Column Data Format section.
    NOTE: You will need to do this for each column where the data contains leading zeros.
  14. Click Finish
  15. From Import Data dialog box opened, click OK.

The leading zeros will still be there in the new worksheet with the imported data. The columns with real numbers will still be able to be used with calculations.

Keywords

export CSV, decimal format, convert , KBA , LOD-ANA-TAB , Table , Problem

Product

SAP Analytics Cloud all versions