Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 .
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
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')
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
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.