Concatenate two CSV files with different columns into one using panda or bash within an Ansible playbook

this is my first post and I'm also very new into programming. Sorry if the terminology I use doesn't always make perfect sense. Feel free to correct any non-sense that would make your eyes bleed.

I am actually a network engineer but with the current trend in my field, I need to start coding and automating but have postponed it until my company had a real use case. Well, that use case arrived and it is called ACI.

I've been learning how to automate many basic things with ansible and so far so good.

My current use case requires a playbook that will concatenate two CSV files with different columns into one single CSV file which will later be used to set variables in other plays.

We mainly work with CSV files containing system names, VLAN IDs and Leaf ports, something like this:

VPC_SYS_NAME, VLAN_ID, LEAF_PAIR
sys1,          3001,     101-102
sys2,          2500,     111-112
... ,          ...,      ... ...

So far what I have tried is to take this data, read it with the read_csv module in ansible, and use the fields in each column as variables to loop in another play:

- name: read the csv file
  read_csv:
       path: list.csv
       delimiter: ','
  register: csv

- name: GET EPG UNI PATH FROM VLAN ID
      aci_rest:
        host: "{{ ansible_host }}"
        username: "{{ username }}"
        password: "{{ password }}"
        validate_certs: False
        method: get
        path:  api/class/fvAEPg.json?query-target-filter=eq(fvAEPg.name,"{{item.VLAN_ID}}")
      loop: "{{ csv.list }}"  
      register: register_as_variable

Once this play has finished, it will register the output into another variable, in this case, called register_as_variable.

I then parse this output with json_query and set it into a new variable:

- set_fact:
     fact1: "{{ register_as_variable | json_query('results[].imdata[].fvAEPg.attributes.dn') }}"

lastly, I copy this output into another CSV file.

With the Ansible shell module and using cat and awk I remove any unwanted characters and change the CSV file from a list with 1 single row to a headerless column, getting something like this:

"uni/tn-tenant/ap-AP01/epg-3001",
"uni/tn-tenant/ap-AP01/epg-2500",
"uni/tn-tenant/ap-AP01/epg-...",

Up to this point, it works as I expect it (even if it is clearly not the cleanest way).

Where I am struggling at the moment is to find a way to merge/concatenate both the original CSV with the system name, VLAN ID etc and the newly created CSV with the output "uni/tn-tenant/ap-AP01/epg-...." into one unique "master" CSV file that would be used by other plays. The "master" CSV file should look something like this:

VPC_SYS_NAME, VLAN_ID, LEAF_PAIR, MO_PATH
sys1,          3001,     101-102, "uni/tn-tenant/ap-AP01/epg-3001",
sys2,          2500,     111-112, "uni/tn-tenant/ap-AP01/epg-2500",
... ,          ...,      ... ..., "uni/tn-tenant/ap-AP01/epg-....",

Adding the MO_PATH header can be done with sed -i '1iMO_PATH' file.csv but merging the columns of both files in a given order is what I'm unable to accomplish.

So far I have tried to use panda and cat but without success.

I would be extremely thankful if anyone could help me just a bit or guide me in the right direction.

Thanks!

Solution

Hello and welcome to StackOverflow! A former network engineer is here to help :)

The easiest way to merge two files line by line (if you are sure that they order is correct) is to use paste utility.

I have the following files:

1.csv

VPC_SYS_NAME,VLAN_ID,LEAF_PAIR
sys1,3001,101-102
sys2,2500,111-112

2.csv

"uni/tn-tenant/ap-AP01/epg-3001",
"uni/tn-tenant/ap-AP01/epg-2500",

Then i came up with

Adding a new header to resulting file 3.csv:
```
echo "$(head -n 1 1.csv),MO_PATH" > 3.csv
```
we are reading header of 1.csv, adding missing column and redirecting output to 3.csv (while overwriting it completely)
Merging two files using paste utility, while skipping the header of 1.csv
```
tail -n+2 1.csv | paste -d"," - 2.csv >> 3.csv
```
Let's split this one:
- tail -n+2 1.csv - reads 1 csv starting from 2nd line to stdout
- paste -d"," - 2.csv - merges two files line by line, using , as delimiter, while getting contents of the first file from stdin (represented as -). We used a pipe | symbol to pass stdout of tail command to stdin of paste command
- >> used to append the content to already existing 3.csv
The result:

VPC_SYS_NAME,VLAN_ID,LEAF_PAIR,MO_PATH
sys1,3001,101-102,"uni/tn-tenant/ap-AP01/epg-3001",
sys2,2500,111-112,"uni/tn-tenant/ap-AP01/epg-2500",

And for pipes to work, don't forget to use shell module instead of command, since this question is marked as ansible