Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I would appreciate some help on the following question:
I am importing data from a number of Excel files from a folder using the ..\*.xls] concurrently. The files (and sheets) content is exactly the same
There are however also .xlsx files (Excel 2007 format) files in the folder, but the script do not allow importing these format files together with the .xls files.
Is there a way (possible with a "if" statement) how I can import both file formats.
Many thanks
My full script is:
//////////////////////////////////////////////
Main:
LOAD
Template_No,
Template_Name,
Template_Owner,
Template_CreatedOn,
Template_LastModifiedOn,
Template_Version,
Template_JobFamily,
Template_JobPosition,
Employee_Surname,
Employee_Name,
Employee_StartDate,
Employee_Unit,
Doc_DateTimeFileStarted,
Doc_SubmitID,
Doc_SavedPath,
Doc_WindowsVersion,
Doc_ExcelVersion,
Template_ItemNo,
Trans_Type,
Trans_Category,
Trans_Item,
Trans_AssessedBy,
Month1_Status,
Month1_DateAssessed,
Month1_Comment,
Month2_Status,
Month2_DateAssessed,
Month2_Comment,
Month3_Status,
Month3_DateAssessed,
Month3_Comment
FROM
(biff, embedded labels, header is 2 lines, table is General$)
WHERE
(not IsNull(Template_ItemNo));
/////////////////////////////////////////////////////////////////////////////////////////////////////
Concatenate ("Main")
LOAD
Template_No,
Template_Name,
Template_Owner,
Template_CreatedOn,
Template_LastModifiedOn,
Template_Version,
Template_JobFamily,
Template_JobPosition,
Employee_Surname,
Employee_Name,
Employee_StartDate,
Employee_Unit,
Doc_DateTimeFileStarted,
Doc_SubmitID,
Doc_SavedPath,
Doc_WindowsVersion,
Doc_ExcelVersion,
Template_ItemNo,
Trans_Type,
Trans_Category,
Trans_Item,
Trans_AssessedBy,
Month1_Status,
Month1_DateAssessed,
Month1_Comment,
Month2_Status,
Month2_DateAssessed,
Month2_Comment,
Month3_Status,
Month3_DateAssessed,
Month3_Comment
FROM
(biff, embedded labels, header is 2 lines, table is Occupation$)
WHERE
(not IsNull(Template_ItemNo));
//////////////////////////////////////////////////////////////////////////////////////////////////////////////
Concatenate ("Main")
LOAD
Template_No,
Template_Name,
Template_Owner,
Template_CreatedOn,
Template_LastModifiedOn,
Template_Version,
Template_JobFamily,
Template_JobPosition,
Employee_Surname,
Employee_Name,
Employee_StartDate,
Employee_Unit,
Doc_DateTimeFileStarted,
Doc_SubmitID,
Doc_SavedPath,
Doc_WindowsVersion,
Doc_ExcelVersion,
Template_ItemNo,
Trans_Type,
Trans_Category,
Trans_Item,
Trans_AssessedBy,
Month1_Status,
Month1_DateAssessed,
Month1_Comment,
Month2_Status,
Month2_DateAssessed,
Month2_Comment,
Month3_Status,
Month3_DateAssessed,
Month3_Comment
FROM
(biff, embedded labels, header is 2 lines, table is [Level 1 - Technical Basic$])
WHERE
(not IsNull(Template_ItemNo));
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Concatenate ("Main")
LOAD
Template_No,
Template_Name,
Template_Owner,
Template_CreatedOn,
Template_LastModifiedOn,
Template_Version,
Template_JobFamily,
Template_JobPosition,
Employee_Surname,
Employee_Name,
Employee_StartDate,
Employee_Unit,
Doc_DateTimeFileStarted,
Doc_SubmitID,
Doc_SavedPath,
Doc_WindowsVersion,
Doc_ExcelVersion,
Template_ItemNo,
Trans_Type,
Trans_Category,
Trans_Item,
Trans_AssessedBy,
Month1_Status,
Month1_DateAssessed,
Month1_Comment,
Month2_Status,
Month2_DateAssessed,
Month2_Comment,
Month3_Status,
Month3_DateAssessed,
Month3_Comment
FROM
(biff, embedded labels, header is 2 lines, table is [Level 2 - Technical Competence$])
WHERE
(not IsNull(Template_ItemNo));
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Concatenate ("Main")
LOAD
Template_No,
Template_Name,
Template_Owner,
Template_CreatedOn,
Template_LastModifiedOn,
Template_Version,
Template_JobFamily,
Template_JobPosition,
Employee_Surname,
Employee_Name,
Employee_StartDate,
Employee_Unit,
Doc_DateTimeFileStarted,
Doc_SubmitID,
Doc_SavedPath,
Doc_WindowsVersion,
Doc_ExcelVersion,
Template_ItemNo,
Trans_Type,
Trans_Category,
Trans_Item,
Trans_AssessedBy,
Month1_Status,
Month1_DateAssessed,
Month1_Comment,
Month2_Status,
Month2_DateAssessed,
Month2_Comment,
Month3_Status,
Month3_DateAssessed,
Month3_Comment
FROM
(biff, embedded labels, header is 2 lines, table is [Level 3 - Technical Advanced$])
WHERE
(not IsNull(Template_ItemNo));
The simpliest way would to do in 2 passes ; once for the xls and once for the xlsx. The 2 formats are different (at least to QV) so you need 2 load starements.
Good luck
The simpliest way would to do in 2 passes ; once for the xls and once for the xlsx. The 2 formats are different (at least to QV) so you need 2 load starements.
Good luck
Thanks Bruce
I followed your advise + added the following on the second pass where .xlsx files is loaded:
1. Started script with set ErrorMode=0;
The reason for doing this is that otherwise error will be given if .xls and .xlsx files in the same folder (when it starts reading script it will bounce when the *.xls loops to a .xlsx file)
2. Replace "biff" in FROM lines with "ooxml"
3. Replace "embedded labels, header is 2" with "embedded labels, header is 3"
4. Remove $ sign next to sheet names
Thus, one of the load statements on second pass will be:
Concatenate ("Main")
LOAD
Template_No,
Template_Name,
Template_Owner,
Template_CreatedOn,
Template_LastModifiedOn,
Template_Version,
Template_JobFamily,
Template_JobPosition,
Employee_Surname,
Employee_Name,
Employee_StartDate,
Employee_Unit,
Doc_DateTimeFileStarted,
Doc_SubmitID,
Doc_SavedPath,
Doc_WindowsVersion,
Doc_ExcelVersion,
Template_ItemNo,
Trans_Type,
Trans_Category,
Trans_Item,
Trans_AssessedBy,
Month1_Status,
Month1_DateAssessed,
Month1_Comment,
Month2_Status,
Month2_DateAssessed,
Month2_Comment,
Month3_Status,
Month3_DateAssessed,
Month3_Comment
FROM
(ooxml, embedded labels, header is 3 lines, table is General)
WHERE(not IsNull(Template_ItemNo));
remember the "Set ErrorMode=0;" at top of script
Thanks again for your help