Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading xls or xlsx

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

4 Replies
Miguel_Angel_Baeyens

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

Not applicable
Author

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

Not applicable
Author

...just noticed a typo

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

should read as

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

Not applicable
Author

Thanks guys!

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