Trim Field Name

[khaycock]

I have a data set that I am unable to link up to a date apart from with the file name. The file name in this case is named SHIP_Night_Week2.xls for each week etc. I need to change it to just 2 so need to trim either sides and all of the text from it. Is that possible?

[beck_bakytbek]

Hi Kathryn,

could you give more information or screenshot, what do you expect to achieve?

Beck

[tamilarasu · Accepted solution]

Hi Kathryn,

Here is a method.

=TextBetween('SHIP_Night_Week2.xls','Week','.xls')

[tamilarasu]

Or

=Replace(SubField('SHIP_Night_Week1212.xls','Week',2),'.xls','')

[Peter_Cammaert]

Or this one:

=KeepChar('SHIP_Night_Week965123.xls','0123456789') & '.xls'

[khaycock]

How can I use this in the script to load for each file? As currently SHIP_Night_Week is loaded in as a wildcard with many different weeks?

[Peter_Cammaert]

I'm not sure I understand.

If you mean that you want a link field to some other WeekNr field, just create it during your load. Like in:

LOAD ...

     KeepChar(FileName(), '0123456789') AS WeekNumber,

     ...

FROM [*.xls] (options);

[khaycock]

How can I use this in the script to load for each file? As currently SHIP_Night_Week is loaded in as a wildcard with many different weeks?

[tamilarasu]

In that case, you can try something like

Load *,

Textbetween(FileName(),'Week','xls') as Filename

From

....

OR

Load *,

Replace(SubField(FileName(),'Week',2),'.xls','') as Filename

From

....