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 1Column Name 2 Data 1Data 2Data 11Data 22Data 11Data 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 1Header column 1 line 2Header column 2 line 2Header column 1 line 3Header column 2 line 3Header column 1 line 4Header column 2 line 4Header column 1 line 5Header column 2 line 5Data 1Data 2 File 2: Header column 1 line 1 Header column 2 line 1Header column 1 line 2Header column 2 line 2Header column 1 line 3Header column 2 line 3Header column 1 line 4Header column 2 line 4Header column 1 line 5Header column 2 line 5Data 11Data 22 And File 3: Header column 1 line 1 Header column 2 line 1Header column 1 line 2Header column 2 line 2Header column 1 line 3Header column 2 line 3Header column 1 line 4Header column 2 line 4Header column 1 line 5Header column 2 line 5Data 111Data 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?

Split output file (excel) with multiple-line header

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?

Workflow

Datasets

Reporting

Output

Accepted answers

All comments

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

CommunityOutput.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

hhaigh1 280