csv output file without additional commas

Question

Hi All,

I have a csv input file that is formatted as follows:

Person,Paul,Smith,Male,36,NYC,Park Avenue,4A

Hobby,Gym,Hiking,

School,University of XYZ

I also have a separate header file that defines the header fields for each of the rows Person/Hobby/School. That csv header file looks like:

Person,FirstName,LastName,Gender,Age,City,Street,HouseNumber

Hobby,HobbyName1,HobbyName2,HobbyName3

School,SchoolName

I read the input file and do various data manipulations. All fine so far.

If I now output again to a csv file, the output csv file looks like:

Person,FirstName,LastName,Gender,Age,City,Street,HouseNumber

Hobby,Gym,Hiking,,,,,

School,University of XYZ,,,,,,

So I am getting additional commas in my output file. The software that reads this output csv file doesn't like the additional commas as it isn't consistent with the header file.

For example, according to the header file, the Hobby line is expected to have 3 fields. Right now, the Hobby line has 7 fields in the output csv file.

Is it possible to create a workflow that creates an output csv file that is consistent with the header file?

Thanks.

RVDL · Answer

@rzdodson It is not straightforward to remove private data from the workflow. But I'm still keen to understand how this could be resolved though. Is it possible to show this using the attached input and header file? These files simply contain the data as described in above query.

header.csv

input.csv

rzdodson · Answer

@RVDL it absolutely is. I suspect that within your workflow that you are joining these two tables, cross-tabbing, or summarizing in such a way to produce a some type of concatenation that is returning ",,,,," in one of your fields due to multiple values existing on a key field (e.g. Last Name), but it having multiple non-unique records in other fields.

If you can share your (anonymized) workflow, I may be able to troubleshoot it for you.