CSV Formatting Challenge

[spencerstone]

Hey all - I need to compose a CSV in Workato that I am not sure is possible, here is my criteria (screenshot below): 

Row 1 has to be as follows: EmployeeManagerImport,,,,,,

Row 2 has to be as follows:  H,EmployeeManager,EmployeeManager,EmployeeXRefCode,ManagerXRefCode,EffectiveStart,EffectiveEnd

Row 3 has to be as follows: D,EmployeeManager,EmployeeManager,{dataPillValue},{dataPillValue},{dataPillValue},{dataPillValue}

As you can imagine, row 1 is really where issues are being caused. Row 2 is effectively the header row, however I need the value in A1 and the subsequent blank cells in order to tell the system what action to perform. 

Has anyone else run into a similar custom CSV formatting challenge like this? 

#automation #hrTech #SFTP

[gary1 · Accepted solution]

Assuming you can make the CSV for rows 2 and onward without issue, all you need to do is add the header row -- like, literally "text add" the row to the beginning of the CSV. 

• Action 1 - Make your CSV using a CSV action or however you want. The output should be a data pill of text
• Action 2 - Make a logger action, switch to formula mode, and do this:

"EmployeeManagerImport,,,,,,\n" + [CSV data pill]

The "\n" is just a line break so it doesn't merge with row 2.

I just tested this and it works.

[gary1 · Accepted solution]

Assuming you can make the CSV for rows 2 and onward without issue, all you need to do is add the header row -- like, literally "text add" the row to the beginning of the CSV. 

• Action 1 - Make your CSV using a CSV action or however you want. The output should be a data pill of text
• Action 2 - Make a logger action, switch to formula mode, and do this:

"EmployeeManagerImport,,,,,,\n" + [CSV data pill]

The "\n" is just a line break so it doesn't merge with row 2.

I just tested this and it works.

[spencerstone · Accepted solution]

@gary1  Ah wonderful, this was exactly what I needed! This worked a charm, thank you. I have one related CSV puzzle I am working through that I was wondering if you had any insight on. I need my header row to remain static (XrefCode,EffectiveStart,Value,Employee_Id), that part is simple enough. However, I need the structure of the CSV to always have 4 rows, with the 4 values in Column A being static and the rest being dynamic. Sample desired CSV output attached below: 

I took what you presented before, using a Logger Action, and pretty much tried to simply build the CSV from scratch (below). That said, I am presented with the fact that the formula has errors. I am not sure where I went wrong here... Let me know if anything jumps out at you! 

 

[shivakumara]

Hi @spencerstone ,

I could see Concatenation operator is missing at the last row. Here I am providing the screenshot for the same.

Add "+" after the comma, it should work.  Please let me know if you need further information.

 

Thanks and Regards,
Shivakumara Avadhani

[spencerstone]

I was looking at it for so long that the commas and plus signs were floating!! Thanks for the sanity check