Import Script for Codes from Excel that could be Scientific Notation
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...???
