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 NameAgeIncomeJohn3040000Barb3550000Tom4045000 Table 2 NameStateHeightWeightJohnOhio18582SarahNew York17065CarlaNevada18174 Output Field1Field2Field3Field4NameAgeIncome John3040000 Barb3550000 Tom4045000 NameStateHeightWeightJohnOhio18582SarahNew York17065CarlaNevada18174 This final table will get written to excel. The resulting column names (Field1, Field2, etc) will get dropped.

Union except that every table keeps it's column names

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.

Output

Preparation

Accepted answers

danilang

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

UnionTablesWithColumnHeaders.yxmd

All comments

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.

20200309-TableUnion.yxmd

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?

Quick Links

This months top contributors

mceleavey 383

AkimasaKajitani 382

mbarone 337

Hollingsworth 335

LanisC 335