Picking value after FROM statement

[ipauldur]

Hi,

Is it possible to pick the value after "FROM" satement.

For example,

Load *

From D:\Activity\03082009_DailyActivityReport.xls ;

Here, i need to take the date value '03/08/2009'.

Any help will be appreciated.

Thanks,

Durai.

[#2]

durai_ip wrote:
Hi,
Is it possible to pick the value after "FROM" satement.
For example,
Load *

From D:\Activity\03082009_DailyActivityReport.xls ;
Here, i need to take the date value '03/08/2009'.
Any help will be appreciated.
Thanks,
Durai.

 <div></div>

Hi Durai,

Load *

From D:\Activity\03082009_DailyActivityReport.xls
WHERE date = '03/08/2009'
;

date equals the name of your date column. you must look closely to the format of the date(dd/mm/yyyy or yyyy/mm/dd or something else)

Rey-man

[ipauldur]

Hi,

You might wrongly uderstanding or may be i am not explaining properly.

Anyway, I have lot of excel sheets like this. But i don't have the date column field on that excel sheets.

But every sheet name(Filename) contains that date value.

So i have to derive date field using the file name.

Is it possible?

Thanks,

Durai.

[#4]

Hi,

yes, just use date(filetime(filename( ))) AS DATETIME if its created on the same day.

Else you can use left(filename(),10)AS DATETIME if the date is first or right(filename( ),10) AS DATETIME if the date is at the end.

Rey-man

[#5]

Assuming the filename is like this 03082009_DailyActivityReport.xls

Since the dateformat is different, there is a small change like this

Load A, B, date(date#(left(filename(),6),'DDMMYYYY')) as Date

From D:\Activity\03082009_DailyActivityReport.xls ;

[ipauldur]

Hi Rey-men,

Thanks, Its working fine.

Regards,

Durai.