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]

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;

[its_anandrjs]

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

[gshockxcc]

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

[maxgro]

are you sure you can use Directory in

FROM

[Directory\*.xlsx]

try with

FROM

[*.xlsx]

[its_anandrjs]

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

[gshockxcc]

Massimo,

     I was not able to load the files with

FROM

[*.xlsx] or [*.xls]

[gshockxcc]

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)

[its_anandrjs]

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..

[stevedark]

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.