cancel
Showing results for 
Search instead for 
Did you mean: 

Merge files with varying column numbers

heidi_andrew
Contributor

Continuing the discussion from Csv file with varying number of columns in each row:

Here is what the file looked like when i started:

EEID|55555|xxxxxxxxx|MICHAEL|C|xxxxx|M|06/21/xxxx
OFFR|55555|01/01/2023-55|OE|01/01/2023|01/01/2023|01/01/2023|12/31/2023|02/06/2023 05:00:00.000000 AM
ELIG|55555|01/01/2023-55|CDHU02500L|BLUE BASIC|EEONLY|EMPLOYEE ONLY|Y|103.28|511.62|Y|Y|Y|Y|Y|||
COVG|55555|OE|01/01/2023|||||||01/01/2023|||||Y|06|UNKNOWN|02/06/2023 05:00:00.000000 AM|01/01/2023-55
EEID|7777777|xxxxxxxxx|KEITH|E|xxxxxxxx|M|11/11/xxxx
OFFR|7777777|01/01/2023-55|OE|01/01/2023|01/01/2023|01/01/2023|12/31/2023|02/06/2023 05:00:00.000000 AM
ELIG|7777777|01/01/2023-55|CDHU02500L|BLUE BASIC|EEONLY|EMPLOYEE ONLY|Y|203.28|411.62|Y|Y|Y|Y|Y|||
COVG|7777777|OE|01/01/2023|||||||01/01/2023|||||Y|06|UNKNOWN|02/06/2023 05:00:00.000000 AM|01/01/2023-55

Now it is a set of files with the rows above separated like this. The solution i showed worked fine. I am not able to put the files
back togther into one file now because of the same error i received and displayed on the screen above.

EEID|55555|xxxxxxxxx|MICHAEL|C|xxxxx|M|06/21/xxxx
EEID|7777777|xxxxxxxxx|KEITH|E|xxxxxxxx|M|11/11/xxxx

OFFR|55555|01/01/2023-55|OE|01/01/2023|01/01/2023|01/01/2023|12/31/2023|02/06/2023 05:00:00.000000 AM
OFFR|7777777|01/01/2023-55|OE|01/01/2023|01/01/2023|01/01/2023|12/31/2023|02/06/2023 05:00:00.000000 AM

ELIG|55555|01/01/2023-55|CDHU02500L|BLUE BASIC|EEONLY|EMPLOYEE ONLY|Y|103.28|511.62|Y|Y|Y|Y|Y|||
ELIG|7777777|01/01/2023-55|CDHU02500L|BLUE BASIC|EEONLY|EMPLOYEE ONLY|Y|203.28|411.62|Y|Y|Y|Y|Y|||

COVG|55555|OE|01/01/2023|||||||01/01/2023|||||Y|06|UNKNOWN|02/06/2023 05:00:00.000000 AM|01/01/2023-55
COVG|7777777|OE|01/01/2023|||||||01/01/2023|||||Y|06|UNKNOWN|02/06/2023 05:00:00.000000 AM|01/01/2023-55

1 ACCEPTED SOLUTION

koryknick
Employee

Pulling back my original pipeline, I think this is what you’re asking for.
Community 15513 - Multi-row CSV_2023_06_20.slp (31.6 KB)

In the Mapper is the following expression:
$.values().join('|')
This simply gets the list of fields from the input document, which will vary depending on how it was parsed, and joins the values together with the pipe character, creating a single string as a pipe-delimited list of values. Then the pipeline is using the Fixed Width Formatter to write that string as-is to the file.

View solution in original post

3 REPLIES 3

koryknick
Employee

Pulling back my original pipeline, I think this is what you’re asking for.
Community 15513 - Multi-row CSV_2023_06_20.slp (31.6 KB)

In the Mapper is the following expression:
$.values().join('|')
This simply gets the list of fields from the input document, which will vary depending on how it was parsed, and joins the values together with the pipe character, creating a single string as a pipe-delimited list of values. Then the pipeline is using the Fixed Width Formatter to write that string as-is to the file.

i removed the header check box in the fixed format snap and it works! Yea! Thanks so much