Extract month and year from filename

[capriconuser]

HI 

i have several files in folder

i.e.

Employee Jan-2008

Employee Feb- 2009

and so on 

now i am trying to extract all months and year from file name i load script like thi s

load

id,

name 

from 

[data\Employee*.xlsx]
(ooxml, embedded labels, table is employee);

now how i extract all months and year from filenames 

any solutoon 

[Lech_Miszkiewicz]

• run the below code.
• adjust the format of your date - I have used mine as 'MMM-YYYY'

LOAD
Date#(Trim(Replace(FileBaseName(),'Employee','')),'MMM-YYYY') as date_transformedd, // this converts date text to actual date
Trim(Replace(FileBaseName(),'Employee','')) as filedate, // this leaves only string which is hidden behind your *
FileBaseName() as filename, // this will return your file name
id,
name
from
[data\Employee*.xlsx]
(ooxml, embedded labels, table is employee);

[capriconuser]

@Lech_Miszkiewicz  HI 

I did not get month and year when i try your code .

[Lech_Miszkiewicz]

so what did you get?

[NitinK7]

try like below

ABC:
LOAD * inline [
name
Employee Jan-2008
Employee Feb-2009
];
PRQ:
Load
*,
Date(Date#(monthyear,'MMM-YYYY'),'MMM') as month,
Date(Date#(monthyear,'MMM-YYYY'),'YYYY') as year;

Load
name,
SubField(name,' ',2)as monthyear
Resident ABC;
drop Table ABC;

[MarcoWedel]

Hi,

maybe something like this might work:

tabEmployee:
LOAD id,
     name,
     Month(Date#(Mid(FileName(),10,3),'MMM'))	as Month,
     Mid(FileName(),14,4)						as Year,
     Date#(Mid(FileName(),10),'MMM-YYYY')		as MonthYear  
From [data\Employee*.xlsx] (ooxml, embedded labels, table is employee);

hope this helps

regards

Marco

[capriconuser]

@Lech_Miszkiewicz  now i get it from your solution..  if we want max month against year then how i do this 

like if we month like this 

jan 2020

feb 2021

then here max month is feb