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.

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

                Could you provide one csv and one xls? You may shorten our scramble the data as you like, but keep the structure such as heading and delimiters intact.

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

                    Hi Vegar,

                     

                    I have attached the data in xls and csv format. both the files have same data type and will be in same folder.Please help on how to extract all file data in one script.

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

                        Try something like this. Note that the 'xls' will return both xls and xlsx files. You attaced an xlsx-file which is not read as biff but ooxml.

                         

                         

                        
                        
                        sub DoDir1 (Root)
                          for each Ext in  'xls', 'csv'
                          for each File in filelist (Root&'*.' &Ext)
                          let vL.FileExtension = SubField('$(File)', '.', -1) ; //filelist(*.xls) returns both xlsx and xls
                          switch vL.FileExtension
                             CASE 'xlsx'  //XLSX are ooxml format
                                set delimet=(ooxml, embedded labels, table is Sheet1);
                             CASE 'xls' //XLS is biff format
                                set delimet=(biff, no labels, table is Sheet1$);
                             CASE 'csv'
                                set delimet=(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
                          ENDSWITCH
                        
                          gateway:
                          Load
                          Date,
                             Time,
                             Machine,
                             code,
                             Text,
                             ID,
                             Name,
                             Department,
                             FileName() as test
                          FROM
                            [$(File)]
                            $(delimet);
                                 ;
                          next File
                          next Ext
                        end sub
                        call DoDir1 ('.\')