Limit csv columns per row by row value

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

Output

Accepted answers

danilang

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

VaryingCSV v2.0.yxmd

All comments

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

VaryingCSV.yxmd

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

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335