Converting to Date format

[girish2195]

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.

[Karim_Khan]

Hi Girish,

     U r considering this dates for which year

[Anonymous]

Hello Girish Kumar,

Please elaborate on this. We could not understand your query.

[sunny_talwar]

This is only Day information, where is Month and Year?

[PrashantSangle]

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,

[robert99]

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

[adamdavi3s]

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

[adamdavi3s]

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

[adamdavi3s]

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.