Split output file (excel) with multiple-line header

[kwieto]

Hi,
I have data which I need to output into several excel files (simple cut every X records).
Usually I do that using the Tile or Multi-Field Binning function and then adding proper suffix to the output file path for each batch of records (like: C:\Output\File_1.xlsx, C:\Output\File_2.xlsx, etc.)
But this time I need to add a multi row header for each file (same for all output files), and have no idea how to proceed with that other way than creating separate streams for each "batch" and creating separate output tool for each of them.

Visual example:

Let say I have table with three rows:

Column Name 1	Column Name 2
Data 1	Data 2
Data 11	Data 22
Data 11	Data 222

And I want each row to be saved into separate file, but with the header containing five lines, so I should have three separate files:

File 1:

Header column 1 line 1	Header column 2 line 1
Header column 1 line 2	Header column 2 line 2
Header column 1 line 3	Header column 2 line 3
Header column 1 line 4	Header column 2 line 4
Header column 1 line 5	Header column 2 line 5
Data 1	Data 2

File 2:

Header column 1 line 1	Header column 2 line 1
Header column 1 line 2	Header column 2 line 2
Header column 1 line 3	Header column 2 line 3
Header column 1 line 4	Header column 2 line 4
Header column 1 line 5	Header column 2 line 5
Data 11	Data 22

And File 3:

Header column 1 line 1	Header column 2 line 1
Header column 1 line 2	Header column 2 line 2
Header column 1 line 3	Header column 2 line 3
Header column 1 line 4	Header column 2 line 4
Header column 1 line 5	Header column 2 line 5
Data 111	Data 222

The number of splits/files can be different, but each file should receive the same header on top.

Any ideas how to make it work?

[AdamR]

You will need to build a batch macro to write out the data in the format you want with the multi-line header. Then as you did before use Tile or Multi-Field Binning function to create the batches and group by this as you pass the records into your batch macro.

[kwieto]

I thought about that, but maybe there is more simple way?

[JReid]

You could probably do a group by your file grouping fields and have a text input with your 5 headers (or more accurately 4 headers since one is the "real" header). You can then use an append fields with the file groups and then union that in and sort. You might need to add an index to make sure the 4 "headers" are at the top of each file.

[dreldrel]

An iterative macro or batch macro should be a good fit to achieve it. Or otherwise you may need to use a python tool

[kwieto]

For now I did batch macro (thought about iterative, but decided that batch will be better), but just out of curiosity, can't this be achieved via reporting tools?

[flying008]

Hi, @kwieto 

After you output to excel, the Header column 1 line 1 ~ 5 are all in 1 cell (with 5 rows) or 5 cells?

[kwieto]

You mean, if they are merged? No, each cell in column/row is separate.

[OllieClarke]

Hey @kwieto 

edit: Apologies, I missed @JReid's post which is the same as mine. I'll leave my post below, as it includes a workflow unlike theirs, but it is functionally identical.

If you've only got 2 columns, then you shouldn't need a macro. Using the dummy data you provided, I output to 3 excels (or as many as you need) all with the metadata headers at the top



Hope that helps,

Ollie