Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
Please refer the attached file.
Here I have done cross table to get all those numbers (1,2,3,..... up to 31) into date field.
I should convert those numbers into date(DD-MM-YYYY).
Thanks in advance.
Hi Girish,
U r considering this dates for which year
Hello Girish Kumar,
Please elaborate on this. We could not understand your query.
This is only Day information, where is Month and Year?
makedate() will help you
details from help menu
MakeDate(YYYY [ , MM [ , DD ] ] )
Returns a date calculated from the year YYYY, the month MM and the day DD.
If no month is stated, 1(January) is assumed.
If no day is stated, 1 (the 1:st) is assumed.
Examples:
makedate(1999) returns 1999-01-01
makedate(99) returns 0099-01-01
makedate(1992,12) returns 1992-12-01
makedate(1999,2,14) returns 1999-02-14
Regards,
Hi
You can use make makedate.
Or change the date in the excel file to a full date (eg 01/01/2017 etc)
but often if you do a cross table load it converts the date to a text format. So convert back using date#. This would require doing the cross-table load and then converting the date using a resident load
Take a look at Steve's reply here:
Load multiple excel files from Multiple excel sheetsinto QV
This will allow you to pull the sheetname which you can then split into month / year.
Then just use the makedate function to create your date field.
actually hang on and I'll do it for you
Here you go:
// set the folder
let vDataFolder = '.\Spreadsheets\';
// loop through all the files
for each vFile in filelist('$(vDataFolder)*.xlsx')
// connect to eaach Excel file
OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=Excel Files;DBQ=$(vFile);DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"];
// Read list of sheets
Sheets:
sqltables;
// Get just the file name
let vFileName = mid(vFile, index(vFile, '\', -1) + 1);
// loop over
for iSheet = 0 to NoOfRows('Sheets') - 1
let vSheetName = peek('TABLE_NAME', iSheet, 'Sheets');
let vSheetName = replace(replace(vSheetName, '$', ''), chr(39), ''); // sqltables seems to add a random $ sign and single quotes
// Load the data
tmp_data:
CrossTable([Day], Data, 2)
LOAD
[Employee Name],
[Team Name],
[1],
[2],
[3],
[4],
[5],
[6],
[7],
[8],
[9],
[10],
[11],
[12],
[13],
[14],
[15],
[16],
[17],
[18],
[19],
[20],
[21],
[22],
[23],
[24],
[25],
[26],
[27],
[28],
[29],
[30],
[31]
FROM [$(vFile)]
(ooxml, no labels,embedded labels, table is [$(vSheetName)]);
data:
LOAD *, MakeDate(YearName,MonthName,DayName) ;
LOAD *,
'$(vFileName)' as [File Name],
'$(vSheetName)' as [Sheet Name],
num(trim(Day),'00') as DayName,
num(month(date#(subfield('$(vSheetName)','-',1) ,'MMM')),'00')as MonthName,
num('20'&subfield('$(vSheetName)','-',2)) as YearName
RESIDENT tmp_data;
DROP TABLE tmp_data;
next
DROP TABLE Sheets;
next
Did this resolve your question?
If so please remember to mark this as helpful or the correct answer if I have helped you or answered your question.
This will help users identify the answers should they come across this thread in the future.