Import data from multiple .xls and .xlsx files in same folder

[#1]

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));

[#2 · Accepted solution]

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

[#3]

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