Skip to main content
Announcements
Discover what’s possible with embedded analytics! March 6, 10 AM ET SIGN UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

2 Replies
Not applicable
Author

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

Not applicable
Author

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