How to write script to merge spreadsheets to a spreadsheet?

[#1]

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!

[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

[#3]

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.

[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?

[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