Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I have two .xlsx files named
Global IT Assesment - vilot Apps 20171029 V1 -Oct Metrics
Global IT Assesment - vilot Apps 20171118 V1 -Nov Metrics
From these excel file names need to extract the Date,Month,Year and YearMonth fields in the script.
Please help me how to extract the above date fields from the file name.
Please find the excel source files.
Thanks in advance.
use FileName() Funcion to get the xlsx name and then use string and date functions to get the value you want.
Global IT Assesment:
LOAD
OR,
R,
A,
G,
makedate(mid(FileName(),34,4),mid(FileName(),38,2),mid(FileName(),40,2)) as Date,
monthname(makedate(mid(FileName(),34,4),mid(FileName(),38,2),mid(FileName(),40,2))) as MonthName,
mid(FileName(),38,2)&'/'&mid(FileName(),34,4) as Year_Month,
mid(FileName(),34,4) as Year,
mid(FileName(),38,2) as Month,
mid(FileName(),40,2) as Day
FROM
(ooxml, embedded labels, table is Sheet1);
Apply the same logic for the second xlsx
First you need to load all data to make good data model. That model may from fact table. From fact table, You can extract and develop only one date field after you can use Month(), Year() .... for DateField..
use FileName() Funcion to get the xlsx name and then use string and date functions to get the value you want.
Global IT Assesment:
LOAD
OR,
R,
A,
G,
makedate(mid(FileName(),34,4),mid(FileName(),38,2),mid(FileName(),40,2)) as Date,
monthname(makedate(mid(FileName(),34,4),mid(FileName(),38,2),mid(FileName(),40,2))) as MonthName,
mid(FileName(),38,2)&'/'&mid(FileName(),34,4) as Year_Month,
mid(FileName(),34,4) as Year,
mid(FileName(),38,2) as Month,
mid(FileName(),40,2) as Day
FROM
(ooxml, embedded labels, table is Sheet1);
Apply the same logic for the second xlsx
May be this
SET DateFormat='YYYYMMDD';
LOAD *,Month(Date) as Month,Year(Date) as Year,MonthName(Date) as MonthName
Where Not IsNull(Date);
LOAD *,Date(Alt(Date#(SubField(FileName(),' ')))) as Date
FROM
[Global IT Assesment - vilot Apps 20171029 V1 -Oct Metrics.xlsx]
(ooxml, embedded labels, table is Sheet1);