0 Replies Latest reply: Jun 6, 2017 2:03 PM by Lee Mychajluk RSS

    Import Script for Codes from Excel that could be Scientific Notation

    Lee Mychajluk

      I have a relatively simple load that I'm trying to do from an XLSX file:

       

      LOAD DISTINCT Value as ID,

           Description as Desc

      FROM

      [$(path_File_Data)\myfile.xlsx]

      (ooxml, embedded labels, table is LIST);

       

      In the XLSX file, the IDs are in the format of 0x## (zero, a letter, then 2 or maybe 3 digits. Ex. - 0D45). These are Segment codes coming from Oracle Financials, FWIW. For most of the values this isn't a problem. But, it seems that in the Excel sheet the values that start with 0E## are considered numeric (and Excel precedes the value with a single quote to force it to appear as a text value, which it doesn't do for the other IDs). When I try to import them with the script above, I get a 0 as the ID for all the 0E## values, presumably because it's a tiny number that just gets rounded to 0. If I try to force the import as Text with the Text() function, all the IDs come in as '0000'.

       

      I'm not generating the XLSX file, so I can't easily modify my source data.

       

      Is there anything I can do in the load script itself to get the ID values to load as '0E01', '0E02', etc...???

       

      Thanks for any help!

       

      -Lee