multiple Excel files

[funcats]

複数のエクセルファイルを一つにまとめてExcel形式でエクスポートする方法はございますか？

TableauやQlikViewでできることは存じ上げております。

よろしくお願いいたします。

[stevedark]

Hi @funcats 

There are two ways you can approach this:

MyData:
LOAD
  *
FROM [lib://DataFiles/*.xlsx]
(ooxml, embedded labels);

Or

for each vFile in FileList('lib://DataFiles/*.xlsx')
  MyData:
  LOAD

    *
  FROM [$(vFile)]
  (ooxml, embedded labels);
next

There are reasons why you would chose one over the other, the main one being if you want to do a preceding load (that requires the second method) or if you need to do some setting of variables within the loop. Also, if all files are not identical you will need the second approach and the CONCATENATE statement. I tend to always go for a loop - just because.

This blog file looks at loading things from a folder in more detail:
https://www.quickintelligence.co.uk/convert-drop-folder-files-qvd/

You can store the combined table after loading it like this:

STORE MyData INTO [lib://DataFiles/MyCSVFile.csv] (txt);

But as you can tell from the syntax, this writes a CSV and can't do Excel. To go to Excel you either need to build a table in Sense and right click to export, or if you are using SaaS you can probably do it with Qlik Automation. On premise NPrinting will do this for you, but it does have an extra price tag.

Hope that helps,

Steve