01-22-2024 03:54 AM
I'm attempting to create a CSV file with 2 header lines for an integration between D365 and Coupa:
Requisition | Submit For Approval? | Justification | Requested By (Email) | Requested By (Login) | Ship to Location Code | Hide Price | po_email_address | event__work_order | event_start_date | event_end_date | ||||||||||||||||||||||||
Requisition Line | Line Number | Catalog Item Number | Non Catalog Item Description | Supplier Part Number | Quantity | Price | UOM Code | Need By Date | Supplier Number | Commodity Name | Currency Code | Chart of Account Name | Account Code | Account Segment 1 | Account Segment 2 | Account Segment 3 | Account Segment 4 | Account Segment 5 | Payment Term Code | asset_location | asset_tag | asset_id | service_start_date | service_end_date | product | expense_gl | profile_id | users_dept | channel | modality | product_line | activity | category | instructor |
Output should look like below
Requisition | Submit For Approval? | Justification | Requested By (Email) | Requested By (Login) | Ship to Location Code | Hide Price | po_email_address | event__work_order | event_start_date | event_end_date | ||||||||||||||||||||||||
Requisition Line | Line Number | Catalog Item Number | Non Catalog Item Description | Supplier Part Number | Quantity | Price | UOM Code | Need By Date | Supplier Number | Commodity Name | Currency Code | Chart of Account Name | Account Code | Account Segment 1 | Account Segment 2 | Account Segment 3 | Account Segment 4 | Account Segment 5 | Payment Term Code | asset_location | asset_tag | asset_id | service_start_date | service_end_date | product | expense_gl | profile_id | users_dept | channel | modality | product_line | activity | category | instructor |
Requisition | ||||||||||||||||||||||||||||||||||
Requisition Line |
Has anyone faced a similar issue before, and if so, knows the most efficient way to solve this?
Any suggestions welcome, many thanks!
Connor Smith
01-22-2024 09:16 AM
I've never seen this kind of formatting before. Is the final CSV always only 4 lines?
01-23-2024 04:53 AM
Me either, find it very strange. Correct only ever 4 lines.
01-23-2024 08:27 AM - edited 01-23-2024 08:28 AM
Got it. I would construct this one line at a time. If you're comfortable scripting, you can write a single action to make an array of arrays, and then convert the array to CSV.
Here's a quick Ruby snippet that should do the trick. You just need to flesh out the variables before pushing them into the array. Make sure each array variable has the same amount of items (even if blank) so the CSV columns remain aligned.
array = []
req_header = ["Requisition","Submit For Approval?","Justification","..."]
req_line_header = ["Line Number","Catalog Item Number","Non Catalog Item Description","..."]
req_line = ["Requisition","..."]
req_line_line = ["Requisition Line","..."]
array.push(req_header, req_line_header, req_line, req_line_line)
csv = array.to_csv
## output
{csv: csv}
Let me know if you need more help with this.
01-23-2024 09:18 AM
Thanks so much!