5 Replies Latest reply: Nov 24, 2016 5:13 AM by Vegar Lie Arntsen RSS

    loading .xls and .csv files from same folder

    Priyabrata Das

      Hi All,

       

      Currently I am using the below script , which is working fine to load all .xls files from the folder but it is not extracting the csv files from the same folder. Both the files have same data items only the format is different. I am using the below code, please help.

       

      sub DoDir1 (Root)
      for each Ext in 'xls'

      for each File in filelist (Root&'\*.' &Ext)
      //Let vExt=Ext;
      let delimet= if(Ext='xls','biff, embedded labels, table is Sheet1$',if(Ext='csv','txt, codepage is 1252, embedded labels, delimiter is ',', msq');
      gateway:

      Load
          @1,
          @2,
          @3,
          @4,
          @5,
          @6,
          @7,
          @8,
          @9,
          '$(delimet)' as test
         FROM [$(File)](biff, no labels, table is Sheet1$);
              //  Exit Script;
        next File
         next Ext

      end sub

      call DoDir1 ('lib://a')

        • Re: loading .xls and .csv files from same folder
          Vegar Lie Arntsen

          Ext loop

          You are only looking after xls files

          for each Ext in 'xls'


          Extend your for-loop to this:

          for each Ext in 'xls', 'csv'

           

          FROM

          I notice you are declaring a variable delimet, let delimet= if(Ext='xls','biff, embedded labels, table is Sheet1$',if(Ext='csv','txt, codepage is 1252, embedded labels, delimiter is ',', msq');,but you are not using it.

           

          Try someting like this:

            FROM [$(File)] ($(delimet)) ;


          Excel:

          make sure that the load statement works for a single xls-file using your Load statement? My guess is that your 'embedded labels' will give you trouble hence you are writing @1, @2 etc.


          Good luck

          Vegar

          • Re: loading .xls and .csv files from same folder
            Priyabrata Das

            Hi,

            Thanks the reply. But the IF condition is not working fine, since just to test  '$(delimet)' as test field is coming blank even for xls. Could you please help to fix this.