Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
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
Highlighted
Not applicable

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

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
Highlighted
Not applicable

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

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

Highlighted
Not applicable

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

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