How to load multiple xls files in script

[gshockxcc]

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,

[maxgro]

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]

[gshockxcc]

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.    

[gshockxcc]

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

next

I am completely lost.  Thanks again.

[its_anandrjs]

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

next

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

Regards

[gshockxcc]

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

next

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

[stevedark]

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

[stevedark]

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

[gshockxcc]

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?

[stevedark]

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

[stevedark · Accepted solution]

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