4 Replies Latest reply: May 12, 2015 10:37 AM by Michael Tarallo RSS

    How to write script to merge spreadsheets to a spreadsheet?

      Hi,All

      I have several spreadsheets from 2012/1--2012/12,one spreadsheet has one month's data.How to merge spreadsheets to a spreadsheet?Does it need to write script?

      Thanks a lot!

        • Re: How to write script to merge spreadsheets to a spreadsheet?
          Ian Wilson

          If all these spreadsheets have the same in structure (field names), then in the load script just use the "concatenate" keyword between each spreadsheet load.  This will create one large table in memory.

           

          more info here: Understanding Join, Keep and Concatenate

          • Re: How to write script to merge spreadsheets to a spreadsheet?
            Jose Federico Moreno Penzo

            You could do this:

             

            LOAD

            myField1,

            myField2,

            ...

            FROM SpredsheetName*.xlsx

            ...

             

            Where SpresheetName is the common part of the file name between the spreadsheets. * is used as a wildcard.

              • Re: How to write script to merge spreadsheets to a spreadsheet?
                Ian Wilson

                Ah yes, Jose brings up a good point.  I was assuming the list of spreadsheets was predetermined.  if you need to scan a folder, for example, for files to load you can use this:

                 

                sub ScanFolder(Root)

                    for each FileExtension in 'xls','xlsx'

                        for each FoundFile in filelist(Root & '\*.' & FileExtension)

                            FileList:

                            Load

                            '$(FoundFile)' as [FilenameWithPath],

                            '$(FileExtension)' as [Extension]

                            Autogenerate(1);

                        next FoundFile

                    next FileExtension

                    for each SubDirectory in dirlist(Root & '\*')

                        call ScanFolder(SubDirectory)

                    next SubDirectory

                end sub

                 

                //Call ScanFolder('.');

                Call ScanFolder('C:\Users\user1\Documents');

                 

                more info here: loop through to load all files from a folder and its subfolders?

              • Re: How to write script to merge spreadsheets to a spreadsheet?
                Michael Tarallo

                Hello Li,

                 

                Just to clarify what Ian stated - if the structure is the same (# of columns and field names)  - they will automatically CONCATENATE together in to one common data table in the Qlik Sense app - there is no need to use the CONCATENATE keyword.

                 

                 

                See the attached example - using a simple .xls file with two tabs - each tab is the same.

                 

                Additional info:

                 

                However YOU CAN ALSO (if need be) add a new column to the data identify the appropriate data using script. For example - tab1 is year 2000 data, tab2 is year 2001. Note I used script to add the appropriate year for each tab in the sheet. So it looks like this:

                 

                 

                LOAD
                    '2000' as Year,
                    ID,
                    amount
                FROM [lib://QlikSense/test_data.xlsx]
                (ooxml, embedded labels, table is [Tab1-2000]);
                
                LOAD
                    '2001' as Year,
                    ID,
                    amount
                FROM [lib://QlikSense/test_data.xlsx]
                (ooxml, embedded labels, table is [Tab2-2001]);
                

                 

                 

                Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

                 

                Regards,

                Mike Tarallo

                Qlik