Extract the last 6 characters from a file name? (file will be a table loaded)

[jblomqvist]

Hi all,

How do I get the last 6 characters from a file name?

For example I may have:

C:\Users\Monthly FIle 31012016.xlsx

I would like to get the bold bit from the file path.

Esentially this is a table I am loading but I need the date to record how up to date the data is.

I imagine this can be stored as a variable.

Any idea how I can do this please?

[ahaahaaha]

Hi John,

Maybe something like this?

SET varName = '1234.xls';

LET varFile = Left(varName, Index(varName, '.') - 1);

Regards,

Andrey

[sunny_talwar]

You mean last 8?

LOAD Date(Date#(Right(FileBaseName(), 8), 'DDMMYYYY'), 'DD/MM/YYYY') as Date

[jblomqvist]

Hi guys,

Is there any way to do this using subfield function?

Let's say I had a SET variable vFileLocation which held the file path.

And then using the subfield function and date functions get the date?

And yes Sunny I meant 8 sorry for my title mistake!

[sunny_talwar]

May be this:

LOAD Date(Date#(SubField(FileBaseName(), ' ', -1), 'DDMMYYYY'), 'DD/MM/YYYY') as Date

I am assuming that there is a space before the Date begins.

[jblomqvist]

Hi Sunny,

Yes there is always a space before the Date begins.

The problem is I need this to work by looking at the date after the last space.

So for example I or my colleague might change the file path to something much longer which might have more spaces.

How can I cater for that?

[sunny_talwar]

I am looking for the 1st space from the back.... Doesn't matter if you file name is this

xyz xyz xyz 31102016.qvw

or

xyz xyz xyz xyz xyz xyz 31102016

SubField(FileBaseName(), ' ', -1) will always grab 31102016 because of the input -1

[jblomqvist]

I almost figured this out.

I did this:

Set vFileLocation = 'Some FolderName1/Some file name thats long ddmmyyyy till ddmmyyyy 31012017.xlsx;

Let X = Date#(Subfield(Right('$(vFileLocation)',15),'.',1), 'DDMMYYYY');

And X gives me 31012017.

But as soon as I try and wrap a Date function around my X variable it doesn't give me the date.

Let X = Date(Date#(Subfield(Right('$(vFileLocation)',15),'.',1), 'DDMMYYYY'), 'DD/MM/YYYY');

Any idea how to get this to work?

[sunny_talwar]

If you do this, what do you see

Let X = Date(Date#(Subfield(Right('$(vFileLocation)',15),'.',1), 'DDMMYYYY'), 'DD/MM/YYYY');

TRACE $(X);

Let X = Date#(Subfield(Right('$(vFileLocation)',15),'.',1), 'DDMMYYYY');

TRACE $(X);

Can you share the reload window screenshot for me?

[jblomqvist]

Hi Sunny,

See my reply on a post below this, any idea how to get that to work?