topic Loading xls or xlsx in QlikView

Loading xls or xlsx

Wed, 11 Jan 2012 09:18:52 GMT

Hi!

I'm trying to figure out what file extension a file has before load statement. The file is provided by users and sometimes they use .xls and other times .xlsx, so I need to alter loading depending on extension.

Right now the closest to a good solution is two load scripts and supress errors with

set ErrorMode=0;

But I would like a more flexible solution, perhaps an IF-statement? Check filelist?

Filename are always the same and so is content.

regards

thomas

Re: Loading xls or xlsx

Miguel_Angel_Baeyens — Wed, 11 Jan 2012 12:13:41 GMT

Hi Thomas,

Based on this code, I'd do something like the following

SET vFileIsBiff = '(biff, embedded labels, table is [Sheet1$])';
SET vFileIsOOXML = '(ooxml, embedded labels, table is Sheet1);';
Sub DoDir (Root) 
     For Each vExt In 'xls', 'xlsx' // filetype to search for in current directory 
          For Each vFile In FileList (Root & '\Test.' & vExt) 
               LET vFileType = If('$(vExt)' = 'xls', '$(vFileIsBiff)', '$(vFileIsOOXML)');
               File: 
               LOAD * 
               FROM [$(vFile)] 
               $(vFileType); 
          Next vFile 
     Next vExt
     For Each vDir In DirList (Root & '\*') // search in subdirectories 
          Call DoDir (vDir) 
     Next vDir 
End Sub 
Call DoDir ('C:\Temp'); // Starting point

So depending on the extension you load each format. The code still seems to me too complex, and some directory recursion might not be needed, or even just an If() will do, since you only want to load one file and there is no need to use a For loop...

Hope that helps, anyway.

Miguel

Loading xls or xlsx

Wed, 11 Jan 2012 12:26:44 GMT

Thomas,

You could create a custom function for use in the script.

We must remember that a file could exist as BOTH an .xls and an .xlsx in the same folder and that could cause problems!

In the script you could create a variable that will hold the extension name for the name of the file (without extension) passed to the custom function like this:

LET vExtn = WhichExtn('C:\mypath\filename');

which you could test for a valid value being returned by the function and if OK then use the (substitution) variable in the load statement.

The function would look like this:

Function WhichExtn (fileName)

WhichExtn = ""

set fso = CreateObject("Scripting.FileSystemObject")

on error resume next

set fileExists = fso.GetFile(fileName & ".xls")

if err = 0 then ' file exists as xls
WhichExtn = WhichExtn & "xls"
end if

err.clear

set fileExists = fso.GetFile(fileName & ".xlsx"

if err = 0 then ' file exists as xlsx
WhichExtn = WhichExtn & "xlsx"
end if

on error goto 0

set fso = Nothing ' release memory

End Function

This will trap the file not existing with either extension (vExtn = "") or if the file exists in both formats (vExtn = "xlsxlsx"

Hope this helps.

Gordon

Loading xls or xlsx

Wed, 11 Jan 2012 12:29:29 GMT

...just noticed a typo

set fileExists = fso.GetFile(fileName & ".xlsx"

should read as

set fileExists = fso.GetFile(fileName & ".xlsx")

Loading xls or xlsx

Wed, 11 Jan 2012 13:12:14 GMT

Thanks guys!

Not working out of the box but I have a (two) starting points.