topic Re: How to load multiple xls files in script in QlikView

How to load multiple xls files in script

gshockxcc — Sat, 05 Apr 2014 16:59:44 GMT

Hi,

I am VERY new to Qlikview, and I don't fully understand all of the scripting functions. So I'm really struggling to understand how to load multiple xls files. I did quite a bit of searching here, and I have only been able to find this link that is somewhat relevant. When I copy that code into my script editor, I get errors that I don't know how to debug.

I have loaded a single table, so I am somewhat familiar with how to build the script But I don't know how to do this for all files in a directory.

Any help would be greatly appreciated.

Thanks,

Re: How to load multiple xls files in script

maxgro — Sat, 05 Apr 2014 17:17:23 GMT

you can

- post some of your excel files (just 2 files, few lines with the header) so we can better help you

- or start loading one file in the script editor (button "table files") and follow the file wizard

-or start from this script (multiple files in same folder of qlikview document, same structure, load from Sheet1, one column A)

Directory;

FOR Each File in filelist ('.\Company*_sales.xlsx')

LOAD A

FROM

'$(File)'

(ooxml, no labels, table is Sheet1);

NEXT File;

Re: How to load multiple xls files in script

its_anandrjs — Sat, 05 Apr 2014 17:25:03 GMT

You have to a load a script like for multiple file loads

LOAD *

FROM

(ooxml, embedded labels, table is Sheet1);

1. Give folder location C:\Users

2. Put *.xlsx for file multiple file names

Hope this helps

Re: How to load multiple xls files in script

gshockxcc — Sat, 05 Apr 2014 17:57:04 GMT

Anand,

Thanks for the help. I have loaded single tables previously, so I know what the column headings need to be, and those are incorporated below. I don't understand what is now working or why. Thanks for your help.

Here's is the content of my script

Directory D:\LapData\KSears\MIDA\PHI\UMass\ftp data\STG1_2013\XLSX;

LOAD ,

[STG1_Inlet_Steam_Flow],

[STG1_kW],

[STG1_Steam_Press],

[STG1_Steam_Temp],

[HPST_Act_Thrust_Brg_Temp],

[HPST_Axial_Vibr_A],

[HPST_Axial_Vibr_B],

[HPST_EE_Brg_Temp],

[HPST_EE_X_Radial_Vibr],

[HPST_EE_Y_Radial_Vibr],

[HPST_Exh_Press],

[HPST_GDE_Brg_Temp],

[HPST_GEE_Brg_Temp],

[HPST_Gen_kW],

[HPST_GHSDE_Brg_Temp],

[HPST_GHSDE_X_Radial_Vibr],

[HPST_GHSDE_Y_Radial_Vibr],

[HPST_GHSNDE_Brg_Temp],

[HPST_GHSNDE_X_Radial_Vibr],

[HPST_GHSNDE_Y_Radial_Vibr],

[HPST_GLSDE_Brg_Temp],

[HPST_GLSDE_X_Radial_Vibr],

[HPST_GLSDE_Y_Radial_Vibr],

[HPST_GLSNDE_Brg_Temp],

[HPST_GLSNDE_X_Radial_Vibr],

[HPST_GLSNDE_Y_Radial_Vibr],

[HPST_GPA_Stator_Temp1],

[HPST_GPA_Stator_Temp2],

[HPST_GPB_Stator_Temp1],

[HPST_GPB_Stator_Temp2],

[HPST_GPC_Stator_Temp1],

[HPST_GPC_Stator_Temp2],

[HPST_In_Press],

[HPST_InAct_Thrust_Brg_Temp],

[HPST_LubeOil_Press],

[HPST_MB_GMR_Freq],

[HPST_MB_GMR_MVa],

[HPST_MB_GMR_MVar],

[HPST_MB_GMR_MW],

[HPST MB GMR MWHr],

[HPST MB GMR PF],

[HPST MB GMR PhA Amps],

[HPST MB GMR PhAB Volts],

[HPST MB GMR PhB Amps],

[HPST MB GMR PhBC Volts],

[HPST MB GMR PhC Amps],

[HPST MB GMR PhCA Volts],

[HPST MB GMR Volts Avg],

[HPST SE Brg Temp],

[HPST SE X Radial Vibr],

[HPST SE Y Radial Vibr],

[STG1 In BTU],

[STG1 Out BTU],

[STG1 kW Daily],

[STG1 kW Hourly],

[STG1 StmF Daily],

[STG1 StmF Hourly]

FROM

[Directory\*.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: How to load multiple xls files in script

maxgro — Sat, 05 Apr 2014 18:07:54 GMT

are you sure you can use Directory in

FROM

[Directory\*.xlsx]

try with

FROM

[*.xlsx]

Re: How to load multiple xls files in script

its_anandrjs — Sat, 05 Apr 2014 18:13:13 GMT

If there are many files in your directory path and from sheet1 you want to load data from every excel and if there is same no of columns so write a code like and In From location Put * like

FROM

[$(vPath)\*.xlsx]

//Code start here

Let vPath = 'D:\LapData\KSears\MIDA\PHI\UMass\ftp data\STG1_2013\XLSX';

LOAD ,

[STG1_Inlet_Steam_Flow],

[STG1_kW],

[STG1_Steam_Press],

[STG1_Steam_Temp],

[HPST_Act_Thrust_Brg_Temp],

[HPST_Axial_Vibr_A],

[HPST_Axial_Vibr_B],

[HPST_EE_Brg_Temp],

[HPST_EE_X_Radial_Vibr],

[HPST_EE_Y_Radial_Vibr],

[HPST_Exh_Press],

[HPST_GDE_Brg_Temp],

[HPST_GEE_Brg_Temp],

[HPST_Gen_kW],

[HPST_GHSDE_Brg_Temp],

[HPST_GHSDE_X_Radial_Vibr],

[HPST_GHSDE_Y_Radial_Vibr],

[HPST_GHSNDE_Brg_Temp],

[HPST_GHSNDE_X_Radial_Vibr],

[HPST_GHSNDE_Y_Radial_Vibr],

[HPST_GLSDE_Brg_Temp],

[HPST_GLSDE_X_Radial_Vibr],

[HPST_GLSDE_Y_Radial_Vibr],

[HPST_GLSNDE_Brg_Temp],

[HPST_GLSNDE_X_Radial_Vibr],

[HPST_GLSNDE_Y_Radial_Vibr],

[HPST_GPA_Stator_Temp1],

[HPST_GPA_Stator_Temp2],

[HPST_GPB_Stator_Temp1],

[HPST_GPB_Stator_Temp2],

[HPST_GPC_Stator_Temp1],

[HPST_GPC_Stator_Temp2],

[HPST_In_Press],

[HPST_InAct_Thrust_Brg_Temp],

[HPST_LubeOil_Press],

[HPST_MB_GMR_Freq],

[HPST_MB_GMR_MVa],

[HPST_MB_GMR_MVar],

[HPST_MB_GMR_MW],

[HPST MB GMR MWHr],

[HPST MB GMR PF],

[HPST MB GMR PhA Amps],

[HPST MB GMR PhAB Volts],

[HPST MB GMR PhB Amps],

[HPST MB GMR PhBC Volts],

[HPST MB GMR PhC Amps],

[HPST MB GMR PhCA Volts],

[HPST MB GMR Volts Avg],

[HPST SE Brg Temp],

[HPST SE X Radial Vibr],

[HPST SE Y Radial Vibr],

[STG1 In BTU],

[STG1 Out BTU],

[STG1 kW Daily],

[STG1 kW Hourly],

[STG1 StmF Daily],

[STG1 StmF Hourly]

FROM

[$(vPath)\*.xlsx]

(ooxml, embedded labels, table is Sheet1);

Hope this helps

Re: How to load multiple xls files in script

gshockxcc — Sat, 05 Apr 2014 18:14:43 GMT

Massimo,

I was not able to load the files with

FROM

[*.xlsx] or [*.xls]

Re: Re: How to load multiple xls files in script

gshockxcc — Sat, 05 Apr 2014 18:24:00 GMT

Anand,

I am still getting a script error when I try to load. I abbreviated the column headings for simplicity here.

Syntax error, missing/misplaced FROM:

LOAD ,

[STG1_Inlet_Steam_Flow],

[STG1_kW],

[STG1_Steam_Press],

...

[STG1 In BTU],

[STG1 Out BTU],

[STG1 kW Daily],

[STG1 kW Hourly],

[STG1 StmF Daily],

[STG1 StmF Hourly]

FROM

(ooxml, embedded labels, table is Sheet1)

LOAD ,

[STG1_Inlet_Steam_Flow],

[STG1_kW],

[STG1_Steam_Press],

...

[STG1 In BTU],

[STG1 Out BTU],

[STG1 kW Daily],

[STG1 kW Hourly],

[STG1 StmF Daily],

[STG1 StmF Hourly]

FROM

(ooxml, embedded labels, table is Sheet1)

Re: Re: How to load multiple xls files in script

its_anandrjs — Sat, 05 Apr 2014 18:29:23 GMT

In below line i found error in the script there is comma (,) after load key word i think that is the problem remove that one

LOAD , <<------------------

[STG1_Inlet_Steam_Flow],

[STG1_kW],

[STG1_Steam_Press],

///////

LOAD

[STG1_Inlet_Steam_Flow],

[STG1_kW],

[STG1_Steam_Press],

...

[STG1 In BTU],

[STG1 Out BTU],

[STG1 kW Daily],

[STG1 kW Hourly],

[STG1 StmF Daily],

[STG1 StmF Hourly]

FROM

(ooxml, embedded labels, table is Sheet1)

and use this above code once if columns are same because you use *.xlsx..

Re: How to load multiple xls files in script

stevedark — Sat, 05 Apr 2014 18:35:42 GMT

I tend to find that doing a loop is a better bet.

for each vFile in FileList('..\*.xlsx')

TableName:

load

'$(vFile)' as SourceFile,

from [$(vFile)]

(ooxml, embedded labels, table is Sheet1);

next

There are a number of reasons I prefer this approach. For one, if you do a preceding load when using a straight * load it will fail with a massive composite key. Also, in a loop you can put extra logic in. Something I tend to add is:

if index(vFile, '~') = 0 then

This then skips over any MS temporary files that may otherwise get picked up by a wild card.

Hope that is helpful.

Re: How to load multiple xls files in script

maxgro — Sat, 05 Apr 2014 18:39:28 GMT

this works for me

Directory C:\Users\mgrossi\Desktop\tmp;

LOAD A

FROM

[Company*_sales.xlsx]

(ooxml, no labels, table is Sheet1);

so I suppose there were 2 problems

1) comma after LOAD

LOAD , <<------------------ as Anand suggest

2) Directory after FROM

FROM

[Directory\*.xlsx]

Re: How to load multiple xls files in script

gshockxcc — Sat, 05 Apr 2014 18:46:00 GMT

Once I correct those errors, I get a new error, "BAD ZIP file."

Any thoughts?

I have both .xlsx, and .xls files that were saved from the original .csv files.

Re: How to load multiple xls files in script

gshockxcc — Sat, 05 Apr 2014 18:50:12 GMT

Steve, thanks for your guidance. I tried this as well. It seemed to work without error, but none of the data was loaded. Here's my script:

Let vPath = 'D:\LapData\KSears\MIDA\PHI\UMass\ftp data\STG1_2013\XLSX';

for each vFile in FileList('..\*.xlsx')

TableName:

load

'$(vFile)' as SourceFile,

[STG1_Inlet_Steam_Flow],

[STG1_kW],

[STG1_Steam_Press],

[STG1_Steam_Temp],

[HPST_Act_Thrust_Brg_Temp],

[HPST_Axial_Vibr_A],

[HPST_Axial_Vibr_B],

[HPST_EE_Brg_Temp],

[HPST_EE_X_Radial_Vibr],

[HPST_EE_Y_Radial_Vibr],

[HPST_Exh_Press],

[HPST_GDE_Brg_Temp],

[HPST_GEE_Brg_Temp],

[HPST_Gen_kW],

[HPST_GHSDE_Brg_Temp],

[HPST_GHSDE_X_Radial_Vibr],

[HPST_GHSDE_Y_Radial_Vibr],

[HPST_GHSNDE_Brg_Temp],

[HPST_GHSNDE_X_Radial_Vibr],

[HPST_GHSNDE_Y_Radial_Vibr],

[HPST_GLSDE_Brg_Temp],

[HPST_GLSDE_X_Radial_Vibr],

[HPST_GLSDE_Y_Radial_Vibr],

[HPST_GLSNDE_Brg_Temp],

[HPST_GLSNDE_X_Radial_Vibr],

[HPST_GLSNDE_Y_Radial_Vibr],

[HPST_GPA_Stator_Temp1],

[HPST_GPA_Stator_Temp2],

[HPST_GPB_Stator_Temp1],

[HPST_GPB_Stator_Temp2],

[HPST_GPC_Stator_Temp1],

[HPST_GPC_Stator_Temp2],

[HPST_In_Press],

[HPST_InAct_Thrust_Brg_Temp],

[HPST_LubeOil_Press],

[HPST_MB_GMR_Freq],

[HPST_MB_GMR_MVa],

[HPST_MB_GMR_MVar],

[HPST_MB_GMR_MW],

[HPST MB GMR MWHr],

[HPST MB GMR PF],

[HPST MB GMR PhA Amps],

[HPST MB GMR PhAB Volts],

[HPST MB GMR PhB Amps],

[HPST MB GMR PhBC Volts],

[HPST MB GMR PhC Amps],

[HPST MB GMR PhCA Volts],

[HPST MB GMR Volts Avg],

[HPST SE Brg Temp],

[HPST SE X Radial Vibr],

[HPST SE Y Radial Vibr],

[STG1 In BTU],

[STG1 Out BTU],

[STG1 kW Daily],

[STG1 kW Hourly],

[STG1 StmF Daily],

[STG1 StmF Hourly]

from [$(vFile)]

(ooxml, embedded labels, table is Sheet1);

I am completely lost. Thanks again.

Re: How to load multiple xls files in script

its_anandrjs — Sat, 05 Apr 2014 18:59:55 GMT

For Steve suggestion you have to use like

///////////////

Let vPath = 'D:\LapData\KSears\MIDA\PHI\UMass\ftp data\STG1_2013\XLSX';

for each vFile in FileList('$(vPath)\*.xlsx')

TableName:

load

'$(vFile)' as SourceFile,

from [$(vFile)]

(ooxml, embedded labels, table is Sheet1);

////////////

Regards

Re: How to load multiple xls files in script

gshockxcc — Sat, 05 Apr 2014 19:22:29 GMT

I don't understand what's wrong. I'm still not able to load the files. Getting a new error.

Bad Zip File

TableName:

load

'D:\LapData\KSears\MIDA\PHI\UMass\ftp data\STG1_2013\XLSX\_STG1_Dresser_Rand_Apr_01_Results.xlsx' as SourceFile,

from

(ooxml, embedded labels, table is Sheet1)

Here's the contents of my script.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Let vPath = 'D:\LapData\KSears\MIDA\PHI\UMass\ftp data\STG1_2013\XLSX';

for each vFile in FileList('$(vPath)\*.xlsx')

TableName:

load

'$(vFile)' as SourceFile,

from [$(vFile)]

(ooxml, embedded labels, table is Sheet1);

LOAD

[F1]

[STG1_Inlet_Steam_Flow],

[STG1_kW],

[STG1_Steam_Press],

[STG1_Steam_Temp],

[HPST_Act_Thrust_Brg_Temp],

[HPST_Axial_Vibr_A],

[HPST_Axial_Vibr_B],

[HPST_EE_Brg_Temp],

[HPST_EE_X_Radial_Vibr],

[HPST_EE_Y_Radial_Vibr],

[HPST_Exh_Press],

[HPST_GDE_Brg_Temp],

[HPST_GEE_Brg_Temp],

[HPST_Gen_kW],

[HPST_GHSDE_Brg_Temp],

[HPST_GHSDE_X_Radial_Vibr],

[HPST_GHSDE_Y_Radial_Vibr],

[HPST_GHSNDE_Brg_Temp],

[HPST_GHSNDE_X_Radial_Vibr],

[HPST_GHSNDE_Y_Radial_Vibr],

[HPST_GLSDE_Brg_Temp],

[HPST_GLSDE_X_Radial_Vibr],

[HPST_GLSDE_Y_Radial_Vibr],

[HPST_GLSNDE_Brg_Temp],

[HPST_GLSNDE_X_Radial_Vibr],

[HPST_GLSNDE_Y_Radial_Vibr],

[HPST_GPA_Stator_Temp1],

[HPST_GPA_Stator_Temp2],

[HPST_GPB_Stator_Temp1],

[HPST_GPB_Stator_Temp2],

[HPST_GPC_Stator_Temp1],

[HPST_GPC_Stator_Temp2],

[HPST_In_Press],

[HPST_InAct_Thrust_Brg_Temp],

[HPST_LubeOil_Press],

[HPST_MB_GMR_Freq],

[HPST_MB_GMR_MVa],

[HPST_MB_GMR_MVar],

[HPST_MB_GMR_MW],

[HPST MB GMR MWHr],

[HPST MB GMR PF],

[HPST MB GMR PhA Amps],

[HPST MB GMR PhAB Volts],

[HPST MB GMR PhB Amps],

[HPST MB GMR PhBC Volts],

[HPST MB GMR PhC Amps],

[HPST MB GMR PhCA Volts],

[HPST MB GMR Volts Avg],

[HPST SE Brg Temp],

[HPST SE X Radial Vibr],

[HPST SE Y Radial Vibr],

[STG1 In BTU],

[STG1 Out BTU],

[STG1 kW Daily],

[STG1 kW Hourly],

[STG1 StmF Daily],

[STG1 StmF Hourly]

FROM

[$(vPath)\*.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: How to load multiple xls files in script

stevedark — Sat, 05 Apr 2014 19:57:23 GMT

That tends to happen if you have the wrong format specified after the Excel file name, or Excel files of the wrong type (eg. a file that was saved as XLS but has been renamed to XLSX).

If you use the Table Files wizard (the button is at the bottom of the script window) to load in one file and test this in isolation you should then be able to modify your script to pull in all files - making sure the format is the same for all files.

If you have a mix if files you will not be able to use the * in the filename but will need to loop for each file with a for statement and chose the correct format specifier in the loop.

Hopefully all your files are of the same type though?

- Steve

Re: How to load multiple xls files in script

stevedark — Sat, 05 Apr 2014 20:02:23 GMT

Sorry, I've just seen you do have a mix of XLS and XLSX files. You will need to use the Table File wizard to provide you with the format specifier for each. You can then use the loop code I provider earlier and extend it like this:

for each vFile in FileList('$(vPath)\*.xl*')

let vFileFormat = if(right(vFile, 1) = 'x', 'ooxml, embedded labels, table is Sheet1', 'biff, embedded labels, table is [Sheet1$]');

TableName:

load

'$(vFile)' as SourceFile,

from [$(vFile)]

($(vFileFormat));

next

Note that the two different file formats you have may differ from the two that I have in the code above - this is why it is a good idea to use the Table File wizard to get the correct formats.

- Steve

Re: How to load multiple xls files in script

gshockxcc — Sat, 05 Apr 2014 20:13:39 GMT

I do, but it was probably a typo on my part. I went back and double-checked all of my scripts to make sure the path and file type was consistent.

The original files came from .CSV. I was able to use the wizard as you described, and load a single table form the .CSV file. Then I expanded it to include all for that month, i.e. April 1 - 30. This was just the brute force method. I successfully repeated this for other months. However, I was unable to get the loop to work using the same headings.

I think I just figured out what the problem is.

It's the "Sheet1);" in the From statement. Each file only has a single sheet, and it's labeled the same as the name of the file. For example, if the file is "..._Apr_01_Results.CSV", the sheet name is "..._Apr_01_Resul", truncated because of the length. Rather than rename 365 sheets, I can write an excel macro to rename the sheet to "Sheet1", and that should solve the issue.

Thoughts?

Re: How to load multiple xls files in script

stevedark — Sat, 05 Apr 2014 20:27:11 GMT

Can you not load from the original CSV files? Using these would avoid all of the issues of sheet names and different Excel formats (thought CSVs can also come in different flavours).

Using an Excel macro should be a real last resort. It is possible to connect to a spreadsheet using an ODBC connector and then enumerate around all the tabs (or the only tab, if that is the case). Or you can simply refer to the sheet number (though I don't know if it works for all Excel file formats):

(ooxml, embedded labels, table is @1)

There is plenty of information online regarding enumerating Excel sheets in QlikView - I'm sure I've blogged about it in the past.

Steve

Re: How to load multiple xls files in script

stevedark — Sat, 05 Apr 2014 20:29:47 GMT

You may also want to see this blog post I put together on pulling in data from a drop folder of CSV or Excel files and persisting the content of each into a QVD. You can then check for the existence of the QVD to decide whether you should pull in data from the source.

http://www.quickintelligence.co.uk/convert-drop-folder-files-qvd/

This can massively speed up load times. Source code is provided in the blog post.

- Steve