10 Replies Latest reply: Jul 17, 2017 7:02 AM by Ciarán McGowan RSS

    Load Dynamic Headers from Excel

    Ciarán McGowan

      Hi all,

       

      Can anyone share a good method for loading multiple tables from the same excel tab into QV? Each table has a similar structure except the amount of rows can vary (between 9 and 15) and the last 3 columns have different headers.

       

      Table 1:

       

      DateHeader 1Header 2Header 3Header 4Person 1Person 2Person 3

       

       

      Table 2:

       

      DateHeader 1Header 2Header 3Header 4Person 4Person 5Person 6

       

       

      The tab can contain anywhere between 10 & 30 tables and as I mentioned, the values in each cell are the same format, only the header names.

       

      Thanks.

       

      Update: Apologies if my requirements were too vague, here is a little more detail:

       

      Headers 1-4 fields contain picklists relating to each person.

      Person (n) contains numeric scores and their name is the header.

      The sheet has more than 2 tables (unknown amount)

       

      Message was edited by: Ciarán McGowan

        • Re: Load Dynamic Headers from Excel
          Andrew Walker

          Hi,

          Would it be right to say that for every record like:

           

           

          DateHeader 1Header 2Header 3Header 4Person 1Person 2Person 3
          abcdeCurlyLarryMoe

           

          You want to get to:

           

          DateHeader 1Header 2Header 3Header 4Person
          abcdeCurly
          abcdeLarry
          abcdeMoe

           

          ?

           

          If so then I would load the entire sheet and use the File Wizard transformation step and follow a strategy something like:

           

          Delete all header rows apart from the first one.

          Crosstable with Date and Header 1,2,3 & 4 as Qualifier Fields

          Call the Attribute Field Person

          Call the Data Field something appropriate to the measure.

           

          Good luck

           

          Andrew

            • Re: Load Dynamic Headers from Excel
              Ciarán McGowan

              Hi Andrew,

               

              That's what I was thinking but running into difficulties when I get to the second table, where the people are different. I would need a solution that caters for multiple cross tables.

                • Re: Load Dynamic Headers from Excel
                  Andrew Walker

                  Hi Ciaran,

                  I would load the entire spreadsheet as the first step , all tables, like

                   

                   

                  DateHeader 1Header 2Header 3Header 4Person 1Person 2Person 3
                  abcdeCurlyLarryMoe
                  sdwafgafCurlyLarryMoe
                  qrweagwegCurlyLarryMoe
                  gafqwerCurlyLarryMoe
                  qrwerqwrtqwrwCurlyLarryMoe
                  DateHeader 1Header 2Header 3Header 4Person 4Person 5Person 6
                  qrwerqwrtqwrwGeddyAlexNeil
                  dvzsfsfsfsffsfdGeddyAlexNeil
                  qrweagwegGeddyAlexNeil
                  gafqwerGeddyAlexNeil
                  sdwafgafGeddyAlex

                  Neil

                  etc.

                   

                  Then delete all rows where column 1 equals 'Date', starting from row 2:

                   

                   

                  DateHeader 1Header 2Header 3Header 4Person 1Person 2Person 3
                  abcdeCurlyLarryMoe
                  sdwafgafCurlyLarryMoe
                  qrweagwegCurlyLarryMoe
                  gafqwerCurlyLarryMoe
                  qrwerqwrtqwrwCurlyLarryMoe
                  qrwerqwrtqwrwGeddyAlexNeil
                  dvzsfsfsfsffsfdGeddyAlexNeil
                  qrweagwegGeddyAlexNeil
                  gafqwerGeddyAlexNeil
                  sdwafgafGeddyAlexNeil

                   

                  Then carry on with the crosstable part. The file wizard transformation may be a bit awkward to use at first but when you get into the way of using it's very powerful.

                   

                  cheers

                   

                  Andrew

                    • Re: Load Dynamic Headers from Excel
                      Ciarán McGowan

                      Thanks again for your reply,

                       

                      If I load all tables into one and delete all header rows after the first one, then won't I lose the names of all people who aren't in the first table?

                        • Re: Load Dynamic Headers from Excel
                          Andrew Walker

                          Hi Ciaran

                          No you won't. The header lines are loaded just as text. The first one can be used to create the headers names in your QV table (embedded labels) and the rest can be deleted. Use the file wizard. Hit the conditional Delete button and enter the condition shown:

                           

                          cond.jpg

                           

                          don't forget to click Add:

                           

                          cond1.jpg

                          click OK Edit

                           

                          Add the additional condition that we do this from the second row to the last.

                           

                          Select the Range button. Click From and enter 2 from the top. Now click To and enter 1 from the bottom. click Select and just press OK here.

                           

                          don't forget to click Add:


                           

                          cond2.jpg

                          Click OK. As you add transformations you should see the effect on the screen. In a simiar way you can delete blank lines or any other garbage too.


                          Good luck


                          Andrew

                  • Re: Load Dynamic Headers from Excel
                    Gysbert Wassenaar

                    You can force concatenation so the tables are concatenated into one table. In that case you'd get different fields for the last three columns of each excel table. So you'd have person1 through person6 in the final table.

                     

                    Or you can name the fields yourself and don't use the column headers as field names:

                     

                    MyTable:

                    LOAD @1 as Date, @2 as Header1, .... @6 as A, @7 as B, @8 as C

                    FROM abc.xlsx (ooxml, no labels, header is 1 lines).

                      • Re: Load Dynamic Headers from Excel
                        Ciarán McGowan

                        Hi Gysbert,

                         

                        I don't know concatenation could work here as there is an unknown number of tables, each with different People (I've updated my requirements to make it more clear).

                         

                        Unless there was a way to move each Person (n) header into a row?

                        • Re: Load Dynamic Headers from Excel
                          Ciarán McGowan

                          Hi Gysbery,

                           

                          Thanks for pointing me in the right direction. I am loading all files in the excel sheet and then using the following code to store each header name as a field:

                           

                            if(Peek(Header) = 'Date',
                           
                          Peek(Person1),
                            
                          Peek(Person)) AS Person


                          This fills creates a Person field for everyone in the Person1 column and fills in the previous row's data. I repeat & concatenate for the Person2 & Person3 columns. Then drop blank rows between tables and all header rows, manually naming each column.