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.
Solved! Go to Solution.
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.
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?
- 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.
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