Skip to Content

Excel files not properly read into SAP Business Object Data Services: Decimal places are lost

Hi,

I was trying to add an Excel file format (.xlsx) as a data source. As my Excel sheet has multiple tables that need to be ingested, I have to read in all the columns as varchar(255) when importing. However, when this happens, my decimal data will lose all its decimal places, and only show integer string values.

Are there anyway to read the Excel files and all the data with their decimal places?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Feb 26 at 03:35 PM

    Xiaoming Wu Ravi kiran Pagidi

    Thanks for your replies!

    I managed to find a workaround, by manually formatting all the numeric cells in my Excel file to "General" format before ingesting into the job. However, I was wondering if there is a less "manual" approach (instead of having to take the additional step everytime to change all cells to "General") to allow me to keep the decimal places.

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 21, 2017 at 01:12 PM

    What is your source column datatype and target column datatype?

    Add comment
    10|10000 characters needed characters exceeded

    • Source datatype is VARCHAR(255) and target is DECIMAL(13,4).

      I cannot keep the source datatype to match the same as the target as I need to ingest the column headers as well to do validation checks.

  • Nov 24, 2017 at 05:43 AM

    When create excel file format, you need to assign correct data type to the source data columns in case there is data lost.

    Add comment
    10|10000 characters needed characters exceeded

    • Ideally I should be setting the source column data type to decimal. However, I cannot do that as I will need to ingest the column headers to do validation checks.