Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have a doubt can you clarify me i have a data in which i have to retrieve date from excel name i have done using
left(right(filenmae)2)1)
but i have doubt if have date in 29-jan-2016 how can i get date without using left and right function how ever the data may be i should get it dynamicly
Use this.... to extract date field... it should work..
Date(Alt(
Date#(SubField(FileBaseName(),'_',-1),'DD-MMM-YYYY'),
Date#(SubField(FileBaseName(),'_',-1),'DD-MMMM-YYYY'),
Date#(SubField(FileBaseName(),'_',-1),'DD-MM-YYYY')
)) as Date
What is the filename?
MAC_R2_CR_PBTV_ACCOUNTS_04-july-2016
LOAD
Date(Date#(SubField(FileBaseName(),'_',-1),'DD-MMM-YYYY')) as Date
FROM
YourFile
or
LOAD
Date(Date#(SubField(FileBaseName(),'_',-1),'DD-MMMM-YYYY')) as Date
FROM
YourFile
thanks Manish i have doubt i want to retrieve date dynamically what ever the format the date is like
MAC_R2_CR_PBTV_ACCOUNTS_04-july-2016
MAC_R2_CR_PBTV_ACCOUNTS_04-07-2016
LOAD
Date(Alt(
Date#(SubField(FileBaseName(),'_',-1),'DD-MMM-YYYY'),
Date#(SubField(FileBaseName(),'_',-1),'DD-MMMM-YYYY'),
Date#(SubField(FileBaseName(),'_',-1),'DD-MM-YYYY')
)) as Date
FROM
[MAC_R2_CR_PBTV_ACCOUNTS_*.xlsx]
(ooxml, embedded labels, table is Sheet1);
no Manish i am asking the file name may be either like this
MAC_R2_CR_PBTV_ACCOUNTS_04-july-2016
or
MAC_R2_CR_PBTV_ACCOUNTS_04-07-2016
however it is it should work in a single expression
Use this.... to extract date field... it should work..
Date(Alt(
Date#(SubField(FileBaseName(),'_',-1),'DD-MMM-YYYY'),
Date#(SubField(FileBaseName(),'_',-1),'DD-MMMM-YYYY'),
Date#(SubField(FileBaseName(),'_',-1),'DD-MM-YYYY')
)) as Date
great manish tanks for your help
If you have got the correct solution, kindly close the thread by selecting correct answer.. this will help others having same issue.