cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

This product reached the end of support date on March 31, 2021.

Automating Site Import CSV

jose_colella
Inactive

Hello all,

When doing site import via a csv file, more often than not, I find myself spending time trying to clean up the data from the customer's excel, or having to translate network to ranges to have it in the appropriate format. This week, I decided to automate the task creating a little commandline program in python. Given an excel or csv file as input, it will generate a standardised csv document that you can import into your CAS.

An example of executing the commandline is as follows:

python main.py -i subnets_16032017.xlsx -o file4.csv -t True -m '{"Id": "", "Domains": "Network", "Name": "Description", "Site Type": "Manual", "Region": "Madrid", "Area": "Site", "Comment": "Location", "UDL": "false", "WAN": "false", "Link Speed In": "", "Link Speed Out": ""}' 

Basically you just need to provide an input file with -i or --input, an output file with -o or --output, a mapping JSON, which is a way to tell the program which columns in the sites csv are to be mapped to the input file's columns. In the example, the column "Id" would be filled with blanks, the Domains columns with the input file's Network column.

The result csv will only contain the following columns:

-"Id", "Name", "Site Type", "Region", "Area", "UDL", "WAN", "Link Speed In", "Link Speed Out", "Comment", "Domains"

The script is in python and uses the pandas library to efficiently manage large datasets. In my case, my customer had used network masks and I added an option that will output the range for the csv saving you time. I have attached the script and a xlsx so that other can test it out. If anyone has any enhancements or wants to contribute you can find the code in the following link.

Removing duplicates and finding overlapping networks in the output csv is on my roadmap.

subnets-16032017.xlsx

dcrum-site-converter-master.zip

Hope this is useful.

Feel free to reach out to me if you have any questions,

José Miguel Colella

1 REPLY 1

miguel_martinez
Advisor

Nice one Jose!! 🙂