Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
vignesh_s
Creator
Creator

populate date through excel file name

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

1 Solution

Accepted Solutions
rahulpawarb
Specialist III
Specialist III

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

View solution in original post

8 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
shiveshsingh
Master
Master

Hi

please try this

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

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

girish2195
Contributor II
Contributor II

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
Creator
Creator
Author

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
Creator
Creator
Author

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
Champion
Champion

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
Contributor II
Contributor II

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
Specialist III
Specialist III

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