Loading xls or xlsx

[#1]

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

[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

[#3]

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

[#4]

...just noticed a typo

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

should read as

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

[#5]

Thanks guys!

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