Bad filed name - how to deal with wrapped field name

[#1]

Hello everyone,

I have a Excel file containing wrapped filed names, so when I load the file, it shows as follows in the script editor

LOAD F1,

     [initial

Stock],

     [Stock

final]

FROM

(ooxml, embedded labels, header is 1 lines, table is Sheet2);

When I use them in the Expression Editor, it shows "bad field name" error.

ex: =Sum([initial

Stock])

What should be done to make it work? I have many files with many columns like this so I don't think renaming all the columns by using "as [initial Stock]" would be the best idea, and I am not supposed to change my excel file format. How can I load the columns with fileds' names unwrapped?

Thank you very much all for your help!

[#2]

Hi,

If you have same wrapped field names repeated and you don't want to rename them in each load statement you can define the aliases once at the beginning of your script.

For example:

ALIAS [initial

Stock] as InitialStock;

ALIAS [Stock

final] as FinalStock;

Hope this helps a bit.

[kuba_michalik]

If you want to automate it, you can use this code snippet at the end of your loadscript:

FOR i=0 to NoOfTables()-1;
     LET t=TableName($(i));
     FOR j=1 to NoOfFields('$(t)');
          LET f=FieldName($(j),'$(t)');
          fnames:
          LOAD
               '$(f)' as field,
               Replace(Replace(Replace('$(f)',Chr(13),' '),Chr(10),' '),'  ',' ') as purgedfield
          AutoGenerate(1);
     NEXT;
NEXT;
SET t=;
SET f=;
SET i=;
SET j=;

fmap:
Mapping LOAD * Resident fnames;

DROP Table fnames;
RENAME Fields using fmap;