Limit csv columns per row by row value

[stapuff]

I have 3 sql inputs that are unioned, sorted by OrdNum & Position then output to csv (no header row). The example below is a simplified version of my data.

The csv needs to have a specific number of fields per row. Current output and required output is shown.

Example: 

Any row with Position=1 would have 4 fields (field1-field4) Any of the 4 fields could be blank or contain data

Any row with Position=2 would have 6 fields (field1-field6) Any of the 6 fields could be blank or contain data

Any row with Position=3 would have 3 fields (field1-field3) Any of the 3 fields could be blank or contain data

In the image below I indicate the fields that need to be removed by {XXX}.  I could hard code that in if that gives a possible solution.

Any thoughts would be appreciated.

Stapuff

[danilang]

Hi @stapuff 

Here's a workflow that gives what you're looking for. 

The top branch uses a formula to build the comma separated strings based on the Position number and including the trailing comma.  The output tool is configured to write a CSV file with no delimiters (\0) so it essentially writes every line as is, separated by a carriage return.  If your requirements ever change, you'll need to manually edit the formula to change the output

However, I noticed that your output consisted exactly of the fields that where not null.  If that's the case, and not just a coincidence, the bottom branch is completely dynamic and will handle any number of columns on any row.  It starts with a transpose that places all the fields that you care about into 2 columns, Name(field names) and Value(field values), grouped by OrdNum and position.  The filter strips out any null values

and the Summarize builds the comma delimited string, incl the trailing comma. 

Both give exactly the same output, namely

but the dynamic version should adapt to your actual data with very little modification, probably limited to the Group By fields in the Summarize tool

Dan

[stapuff]

Thanks, @danilang for your response back.

I went with your top example.

Is there a way to maintain fields that have comma's already in it?  I know the example did not show the use of comma's.

Stapuff

[danilang · Accepted solution]

Hi @stapuff 

You'd need a prestep that looks for comma's in the 6 main fields and adds double quotes(") before and after the item if found.  The attached contains a Multi field tool on the top branch that does just that

Dan

[stapuff]

Excellent.... I appreciate all you helped me with.

Stapuff