Excel load optimisation

[Peony]

Hi all.

I am loading data from an excel file. Usually it looks something like this:

Excel_Table:
Load

[2018-11] as [Data_2018-11],
[2018-12] as [Data_2018-12],
[2019-01] as [Data_2019-01],
[2019-02] as [Data_2019-02]

From Excel_file;

When the user managing this file removes columns from it or adds new ones, I need to manually edit the script.
Is there any way to optimize the process?
It is possible to pick up columns through an asterisk and use their names to automatically generate aliases?

[tresesco · Accepted solution]

Try like:

Excel_Table:

Load * From <>;

For f = 1 to NoOfFields('Excel_Table')
        Let vField=FieldName($(f),'Excel_Table');
        Let vFieldNew='Data_' & '$(vField)';
        RENAME Field $(vField) to $(vFieldNew);
Next f;

[Peony]

Thank you! This is exactly what I was looking for.

The only thing I have added is single quotes around the variables on this line: RENAME Field '$(vField)' to '$(vFieldNew)';