Join data from 2 CSV files

Hi Jussihi,

To handle your first requirement, you can use a Script connector. The script will process the invoicer.csv file, retrieve its content, and prepend a header to the file. If the headers are fixed, you can define them directly in the script otherwise you can use one more script connector to fetch the value from invoice.csv file. Below is an example implementation:

<arc:set attr="input.file" value="[filepath]" />
<arc:set attr="outfile.data" value="InvoicerID,CompanyName,Address,City,State,Zip,Country,ContactEmail\r\n)" />
<arc:call op="fileRead" in="input" out="result" >
  <arc:set attr="outfile.data" value="[outfile.data | concat([result.file:data])]" />
</arc:call>
<arc:push item="outfile"/>

For your second use case, To fetch files using the URL from invoice.csv , you can convert the CSV file to XML for easier processing, extract the Link field using xmlDOMGet , and then use the httpGet operation to download the file.

If you have any further questions, please feel free to reach out to us at [email protected].

<arc:set attr="input.file" value="[filepath]" />
<arc:set attr="outfile.data" value="InvoicerID,CompanyName,Address,City,State,Zip,Country,ContactEmail\r\n)" />
<arc:call op="fileRead" in="input" out="result" >
  <arc:set attr="outfile.data" value="[outfile.data | concat([result.file:data])]" />
</arc:call>
<arc:push item="outfile"/>

Your answer to the first part is not what I want. I don’t want to add a header to my csv.

Let’s say I have 2 files:

invoice.csv:

423432;123;500,56;20250109

The first column above is the invoicer ID.

invoicers.csv:

Invoicer A;423432;IBAN;otherinfo
Invoicer B;696962;IBAN2;otherinfo2

How can I concatenate the ID corresponding row from invoicers.csv to my invoice.csv using CData Arc, so that the end result would look something like (doesn’t need to be 100% like this, but how can I copy required data from the right row inside the other file to this invoice.csv file):

423432;123;500,56;20250109;Invoicer A;423432;IBAN;otherinfo

The main point is that I want to enrich the invoice.csv file with additional data found from the invoicers.csv file’s row with the same invoicer ID.

With some exceptions, CData Arc is not intended for the processing of separate files and merging the contents (the BatchCreate and BatchMerge connectors allow you to take parallel XML files and merge them together, but not correlate values to one another like you are proposing), so the use case that you are proposing is not one that you would be able to achieve without applying some programming. 

If the invoicers.csv that you are looking up from is a static file located in a single location, there is a csvListRecords operation in ArcScript that you can use to list the records in the file:

CData Arc - CSV Connector | Version 24.3.9133

<arc:call op="csvListRecords?file=/path/to/invoicers.csv">
  <arc:if exp="[csv(c1) | equals('somelookup')]">
    <!-- this row matches your key, and [csv(columnname)] will evaluate other columns 
         in this row -->
  </arc:if>
</arc:call>

But this method of lookup will always read the CSV line by line for every operation to lookup, which may not be very efficient. If you were to sync that CSV file with a database of some type, you would find that the Lookup action in the corresponding Database connector:

CData Arc - Database Lookup Configuration | Version 24.3.9133

Would be a more efficient mechanism for looking up this value. The Lookup action does enrich a data set with the values returned from the lookup, either as headers on the message or as additional XML elements, so a simple approach would be to use a Lookup to check for the columns that match your ID and use the CSV connector to convert your original CSV to XML and back. 

Also, one of the fields in the invoice.csv is a html link to said invoice. How can I download this file with CData Arc?

The REST connector in CData Arc can be used for basic HTTP operations, and operates similarly to Postman: 

CData Arc - REST Connector | Version 24.3.9133

I would recommend that if you want to continue processing with that invoice in a subsequent processing step in your flow, you promote the URL that you wish to use as a header on your message and use the REST connector to GET that URL based on that header:

CData Arc - REST Connector | Version 24.3.9133

If you simply want to use ArcScript to download those resources to a folder on disk, there is an httpGet operation in ArcScript that you can use to trigger downloads:

CData Arc - httpGet | Version 24.3.9133

<arc:call op="csvListRecords?file=/path/to/invoicers.csv">
  <arc:if exp="[csv(c1) | equals('somelookup')]">
    <!-- this row matches your key, and [csv(columnname)] will evaluate other columns 
         in this row -->
  </arc:if>
</arc:call>

Thank you. Yes, it seems quite cumbersome to do. I think I will create an external python program that will handle this and just call that from Arc.