4 Replies Latest reply: Jan 11, 2012 8:12 AM by Thomas Ljungström RSS

    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

        • Re: Loading xls or xlsx
          Miguel Angel Baeyens de Arce

          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
            Gordon Savage

            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