Date formate

[#1]

Hi All,

I have many tables among which i have  data by name transaction date  in a folder which has 6 months of data (that is there are again 6 folders from Jan to jun ) and each folder has 30 excel sheets for that particular month  iam attaching the screen shot so that u can understand it clearly and the date formate is like 02-12-2014.

What i want to know is how do i fetch all 30 exzcel sheets at a time and convert the dtae formate as Jan-02-2014 for all the months and days .

I hope iam clear  .

Thanks In Advance .

[its_anandrjs]

Hi,

Load all csv file and in the load script mention FileName() for get which is the file you loaded data and use date function for convert file name into date format.

LOAD

FileName() as FileName,

Date(Date#(Left(FileName(),10),'DD.MM.YYYY'),'MMM-DD-YYYY') as Date

FROM

*.csv

(txt, utf8, embedded labels, delimiter is ',', msq);

Regards

Anand

[Gysbert_Wassenaar]

You can use the date() function to change the format of a date: date(MyDateField, 'MMM-DD-YYYY') will format dates like you want. If your date field contains text values instead of date values you first have to use the date#() function to change the text value into a date value. For example date(date#('02-12-2014','DD-MM-YYYY'),'MMM-DD-YYYY')

[Anonymous]

Hello,

You can achieve this using special character '*'.

Such as :

test:

LOAD

    A

FROM [lib://TEST/*.xlsx]

(ooxml, embedded labels, table is Sheet1);

As long as the formats match (columnames etc), there is no problem.

BR

Serhan

[jagan]

Hi,

If you want to load all files with a single script statement then use wildcard character for file name like below and for changing date format use Date() and Date#() functions.

TableName:

LOAD

    *,

Date(Date#(DateFieldName, 'DD-MM-YYYY'), 'MMM-DD-YYYY') AS FormattedDate

FROM [lib://Folder/*.xlsx]

(ooxml, embedded labels, table is Sheet1);

OR

TableName:

LOAD

*,

Date(DateFieldName, 'MMM-DD-YYYY') AS FormattedDate

FROM [lib://Folder/*.xlsx]

(ooxml, embedded labels, table is Sheet1);

Hope this helps you.

Regards,

Jagan.