populate date through excel file name

[vignesh_s]

hi every 1,

     hi have excel file with file name xyz_23/1/2018.xsl and i have some feild in it excluding date field,i want populate date field with reference to file(xyz_23/1/2018.xsl) and it used happen dynamicly..can any1 help with ths

[jonathandienst]

A file name cannot contain a '/' character. Are these folders?

[shiveshsingh]

Hi

please try this

   date(date#(replace(right(Filename(),13),'.xls',''),'YYYYMMDD'),'YYYYMMDD')

remove '/' from file name, i guess / cannot be a filename

[girish2195]

Hi Vignesh,

Date(Date#(TextBetween(FileName(),'_','.'),'DD/M/YYYY'),'DD-MM-YYYY') as Date.

Use the above expression in your script.

Note : Filename format should not change.

Thanks & Regards,

Girish Kumar DV

[vignesh_s]

sry,the file name looks like XYZ_22012018

ex:

load Value,Quantity,Price

          12     ,22          ,100

     from XYZ_22012018

concate

load Value,Quantity,Price

          13     ,33          ,200

     from XYZ_23012018

the above loading file contains 3 fields which are same so it is concatinated

the both file are in the different date"22-01-2018" and "23-01-2018"  so i wanna populate date field such that

out put:

Value,Quantity,Price,Date

12     ,22          ,100     22-01-2018

13     ,33          ,200     23-01-2018

[vignesh_s]

sry,the file name looks like XYZ_22012018

ex:

load Value,Quantity,Price

          12     ,22          ,100

     from XYZ_22012018

concate

load Value,Quantity,Price

          13     ,33          ,200

     from XYZ_23012018

the above loading file contains 3 fields which are same so it is concatinated

the both file are in the different date"22-01-2018" and "23-01-2018"  so i wanna populate date field such that

out put:

Value,Quantity,Price,Date

12     ,22          ,100     22-01-2018

13     ,33          ,200     23-01-2018

[tamilarasu]

Something like below.

Data:

Load Value,

         Quantity,

         Price,

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

From XYZ_22012018;

Concatenate

Load Value,

         Quantity,

         Price,

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

From XYZ_23012018;

[girish2195]

Hi Vignesh,

Use the below code.

load Value,Quantity,Price,Date(Date#(TextBetween(FileName(),'_','.'),'DDMMYYYY'),'DD-MM-YYYY') as Date.

          12     ,22          ,100

     from XYZ_22012018

concate

load Value,Quantity,Price,Date(Date#(TextBetween(FileName(),'_','.'),'DDMMYYYY'),'DD-MM-YYYY') as Date.

          13     ,33          ,200

     from XYZ_23012018

[rahulpawarb · Accepted solution]

Hello Vignesh,

Trust that you are doing well!

You can use below given expression to fulfill the requirement.

//Expression

Date(Date#(SubField(FileBaseName(),'_', 2), 'DDMMYYYY'), 'DD-MM-YYYY') AS Date

Decoding:

SubField(FileBaseName(),'_', 2) : This will return the date part from the file name

Date#(SubField(FileBaseName(),'_', 2), 'DDMMYYYY') : This will interpret the extracted part as a date with its present format

Date(Date#(SubField(FileBaseName(),'_', 2), 'DDMMYYYY'), 'DD-MM-YYYY') : This will convert the interpreted date into expected date format.

//Sample Script
Data:
LOAD Value,
    Quantity,
    Price,
    Date(Date#(SubField(FileBaseName(),'_', 2), 'DDMMYYYY'), 'DD-MM-YYYY') AS Date
FROM
Data\XYZ_22012018.xls
(biff, embedded labels, table is Sheet1$);

LOAD Value, 
    Quantity,
    Price,
    Date(Date#(SubField(FileBaseName(),'_', 2), 'DDMMYYYY'), 'DD-MM-YYYY') AS Date
FROM
Data\XYZ_23012018.xls
(biff, embedded labels, table is Sheet1$);

Hope this will help.

Regards!

Rahul Pawar