Union except that every table keeps it's column names

[LukeH]

I am trying to write a series of asymmetric tables into a single excel/csv file. The tables will generally have 4 columns where the names match, but ideally, I want to completely avoid using column names. I want the column names for each table written above that table.

Example:

Table 1

Name	Age	Income
John	30	40000
Barb	35	50000
Tom	40	45000

Table 2

Name	State	Height	Weight
John	Ohio	185	82
Sarah	New York	170	65
Carla	Nevada	181	74

Output

Field1	Field2	Field3	Field4
Name	Age	Income
John	30	40000
Barb	35	50000
Tom	40	45000
Name	State	Height	Weight
John	Ohio	185	82
Sarah	New York	170	65
Carla	Nevada	181	74

This final table will get written to excel. The resulting column names (Field1, Field2, etc) will get dropped.

[CharlieS]

Hi @LukeH 

I suggest using the Reporting tools to produce this layout. Each table of value should go into a Table tool to create a layout field for that table. Then Union those table fields together and output with the Render tool.

See the attached workflow for an example and let me know if you have any questions.

[LukeH]

Hi @CharlieS 

That is very interesting.  But there are a few additional issues with what I'm seeing here.

First, the resulting Excel File has some odd column positioning. The smaller table has merged columns. I don't know if this will be a problem further down the line.

I am saving the result as excel, but I was previously doing this with the "Run Command" tool, which allowed me to save the input as Excel and then call a python script using that saved file. Is there any way to parse the Unioned tables without the Render tool, which has no pass-through functionality?

[CharlieS]

- Rendering to Excel will default to aligning fields right-to-left. To ensure a left-to-right alignment, you'd need to add the necessary fields ahead of time. 

- The Render and Output tools do not have passhrough, but for processes like this, you can use a Block Until Done tool to write a to a pre-determined path, and then pass that path to a subsequent connection on the Block Until Done tool, like the Run Command.

In the updated attachment, I also added a Layout tool so that the tables are merged to a single record before passing on to the Render/Run Command tools. 

[danilang · Accepted solution]

Hi @LukeH 

A non-Reporting solution would look like this

For each table, use a Field Info, RecordID, Crosstab sequence to generate a row consisting of the the field names.  Union all the header and data rows for each table with a spacer in between them specifying the Output Order in the Union tool

  

Dan