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,

[gshockxcc]

The original files are already in CSV format, each with the tab named according to the file name.  I had written a macro to open the files and "Save As" in both XLS and XLSM formats, each only has a single tab.

Thanks,

-Kristan

[gshockxcc]

I tried the ODBC connection for a single Excel file, and none of the tags load.  Because the original data is in .CSV format, it seems like the only means to load it is through brute force.  No?

[stevedark]

CSV files do not have sheet names - there is no where for the sheet name to be stored.

It will be Excel adding the sheet name when the import macro runs.  You can save yourself a bit of pain with the macro and Excel formats simply by loading the CSVs.

I've blogged on a neat way of speeding up loads from multiple CSVs, if you search for "convert drop folder files to qvd" you should find it.

Steve

[gshockxcc]

Steve,

     That was brilliant.  Cheers.  I successfully imported the full month of April's data.  When the column headings came in as Fields, there showed up as @1, @2, @3, ... etc.  How do you change these?

Thanks much for all your help.  This is a great start.

-Kristan     

[stevedark]

Hi Kristan,

Glad you are making steps forward.  You can simply rename columns with an AS statement, as follows:

LOAD

  @1 as [STG1_Inlet_Steam_Flow],

  @2 as [STG1_kW],

  @3 as [STG1_Steam_Press],

  @4 as [STG1_Steam_Temp],

   etc...

- Steve

[gshockxcc]

I am trying to re-start this with a new set of data, and I'm getting an error when Debugging my script.  I don't understand why.

My qvw file is attached.  Thanks in advance.

This could possibly be the most frustrating piece of software I have ever used, AND the most powerful.

[stevedark]

The two commented out lines look like they will cause an error.  Either uncomment them or also comment the two preceding lines of code.

What that error message is telling you is that there is no table called Data.  My best guess would be that no CSV files were found so there was nothing to write.

Suggest that you use the Debug feature in the Script Editor to step through each line of script and see if any files do match the criteria.

To bullet proof the load you could put the following IF statement around the store:

if(Alt(NoOfRows('Data'), 0) > 0 then

     store Data into File.qvd (qvd);

end if

Hope that helps.

Steve

[gshockxcc]

Thanks for the help.  I got rid of the error, but now I'm getting a new error for loading each of the .csv files.  The files were there, but for some reason it won't load with the statement:

..\UMass\STG1_2013\*.csv

I have to put the full path in the script. 

But then I get this error.

The error is repeated for each of the 369 .csv files I have in the folder.

Thanks,

-Kristan

[stevedark]

Hi Kristan,

In the file you sent me the following lines were commented out:

//now() as DateLoaded

//AUTOGENERATE(1)

Those will be required for the STORE statement to work.

Steve

[rojaraoqlik]

load * from (ooxml, embedded labels, table is sheet1);