I have many tables among which i have data by name transaction in a folder which has 6 months of data (that is among that folder there are again 6 folders from Jan to jun ) and each folder has 30 excel sheets for that perticular 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 .
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')
You can use "*" as a wildcard in the filename to load data from multiple spreadheets and the script will automatically concatenate the data to a single table provided the column names in each spreadsheet are identical.
The function filebasename() will bring the filename into a field in the table, then you can use date# to convert the filename data from text to a date, though this may need some additional string slicing.
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);
