Import Script for Codes from Excel that could be Scientific Notation

Anonymous — Tue, 06 Jun 2017 18:03:35 GMT

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

topic Import Script for Codes from Excel that could be Scientific Notation in App Development

Import Script for Codes from Excel that could be Scientific Notation