4 Replies Latest reply: Sep 24, 2014 4:34 PM by sneha jha RSS

    Verify column headers before reload

    sneha jha

      My requirement is that, I have large number of excel files which I need to reload. But before loading, I need to check if all the column headers in all these files are correct (These are data files and in same format. All should have same column headers).

      So, my question is how can I read column headers from excel in qlikview for each file and match with a reference array of column headers?

      My Try: As I am very new to Qlikview and new to scripting, so couldn't get far. All I can do now is get all files one by one in my code from the directory where it is stored but not sure how to read column headers from them and compare it.

      Thanks for any help

        • Re: Verify column headers before reload
          Manish Kachhia

          Let me have some clarification first....

          You have multiple excel files having same type of data (may be for different dates or for different months) and want to load them in one lot but before loading you want to check whether all the files are having the same headers...

           

          Considering that all your files are having headers in the same order...you can ignore the column header and load all other data but at the same time can give column header name using alias...

           

          Let me give you one example...

          You have Customer vs Sales data for every day....

          The files are in

          Sales22092014

          Sales23092014

          Sales24092014

           

          now if you want to load all the files using column header... it looks like as

          Load

               Customer,

               Country,
               Region,

               Sales,

               InvoiceDate

          From

          Sales22092014.xlsx

          (ooxml, embedded labels, table is Sheet1);

           

          Instead of this you can use as below

          LOAD

             A as Customer,

              B as Country,

              C as Region,

              D as Sales

          FROM

          Sales*.xlsx

          (ooxml, explicit labels, table is Sheet1)

          Where RecNo() <> 1;

          =========================================================

          Looks like I am still missing something... may be you don't want to load those files where the column headers are not matching... Kindly clarify the same.... will work on that...

          =======================================================

          If possible, please provide some sample files...

          • Re: Verify column headers before reload
            Gysbert Wassenaar

            See this blog post: qlikviewcookbook.com/2008/09/loading-multiple-excel-sheets/

            Note, the part you're interesting in is the paragraph that starts with:

            How about this case? I want to load any sheet that contains the three columns “Sales”, “Year” and “Quarter”:

            In your case you're not interested in just three fields, but your reference array of fields.

            • Re: Verify column headers before reload
              sneha jha

              Hi Manish,

              Thanks for your reply. I have standard file which I use as benchmark to load other files and I wish to match the column headers with repect to the benchmarked file. If there is any blank header  or column names do not match(wrong data) then data reload will fail stating the message why the reload failed and due to which file(i.e.name of the file) . If the validation is correct I should get a  message 'validation is successful.'

              Can you suggest any wider approach ?