none
Why does Open XML API Import Text Formatted Column Cell Rows Differently For Every Row? RRS feed

  • Question

  • I am working on an ingestion feature that will take a strongly formatted .xlsx file and import the records to a temp storage table and then process the rows to create db records.  One of the columns is strictly formatted as "Text" but it seems like the Open XML API handles the columns cells differently on a row-by-row basis.  Some of the values while appearing to be numeric values are truly not (which is why we format the column as Text since others true text) - some examples are "211377", "211727.01", "209395.388", "209395.435", "309036.FP", "212767.TM" - what these values represent is not important but what happens is that some values (using the Open XML API v2.5 library) will be read in properly as text whether retrieved from the Shared Strings collection or simply from InnerXML property while others get sucked in as numbers with what appears to be appended rounding or precision.  For example the "211377", "211727.01", "209395.435", "309036.FP", "212767.TM" all import exactly as they are in the spreadsheet but the "209395.388" value (shows exactly this way if you open the spreadsheet natively in Excel) is being pulled in as "209395.38800000001" (there are others that this happens to as well).  There seems to be no rhyme or reason to which values get messed up and which ones import fine.  What is really frustrating is that if I use the native Import feature in SQL Server Management Studio and ingest the same spreadsheet to a temp table this does not happen - so how is that the SSMS import can handle these values as purely text for all rows but the Open XML API cannot.
    Saturday, February 28, 2015 3:29 PM

Answers

  • Hi Michael,

    According to the description, when you insert text into a cell some text turned into numbers. Would you mind sharing the code snippet that you are developing with?

    As far as I test, the code provide by link below works well for me:
    How to: Insert text into a cell in a spreadsheet document (Open XML SDK)

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 2, 2015 3:09 AM
    Moderator

All replies

  • Hi Michael,

    According to the description, when you insert text into a cell some text turned into numbers. Would you mind sharing the code snippet that you are developing with?

    As far as I test, the code provide by link below works well for me:
    How to: Insert text into a cell in a spreadsheet document (Open XML SDK)

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 2, 2015 3:09 AM
    Moderator
  • The values must be stored as text, not as values formatted as text. And at every step the value must be explicitly written or stored as  text. Fei's link uses the explicit writing of values as text which solves one step.

    I fight a similar battle regarding what are text values that get messed up before I get the data. While I can't stop the error from happening in my case, its consistent enough for me to adapt easily.

    Friday, March 13, 2015 3:12 PM