Python: creating a output CSV from multiple ones. Vlookup

Question

I am trying to fetch data from one CSV file and put the respective information of that data which is in another CSV file and merge them in a Output file.

lets say this is my lookup file:

Name.csv:

+------+
|CI_Name|
+------+
| a    |
| b    |
| c    |
| ...  |
+------+

This is another csv file from where I will get the related information of the server, Ip address, etc

Main.csv

+------+--------+------------+
|CI_Name | Server | IP-Address |
+------+--------+------------+
| a    |        |            |
| b    |        |            |
| c    |        |            |
| ...  |        |            |
+------+--------+------------+

What I want is to merge this two information into a new excel sheet(csv), basically a VLOOKUP, Please help Python Coders! I need a code and explanation, so that I will be able to do Vlookup in future with ease.

This is the code that I have tried:

import csv

with open('Name.csv', 'r') as csvinput:
    with open('Main.csv', 'r') as lookuplist:
        with open('Output.csv', 'w') as output:

            reader = csv.reader(lookuplist)
            reader1 = csv.reader(csvinput)
            writer = csv.writer(output)

            for CI_Name in reader1:
                for SERVER, FQDN, AUTOMATION_ADMINISTRATOR, IP_ADDRESS, PRIMARY1, MHT_1, MHT_2, MHT_3, MHT_4 in reader: #this are the columnss i need to match from the Main and import it to the output.csv
                    if CI_Name[0] == FQDN[0]:
                        CI_Name.append(FQDN[1:])
                        writer.writerow(CI_Name)

The Error that I'm getting is:

for SERVER, FQDN, AUTOMATION_ADMINISTRATOR, IP_ADDRESS, PRIMARY1, MHT_1, MHT_2, MHT_3, MHT_4 in reader:
ValueError: too many values to unpack

Python 2.7 is what I am using. But I have no alternatives. So will have to script in this one only. — Karan M, May 22 '18 at 16:22

score 0 · Accepted Answer · answered May 23 '18 at 08:46

I've not worked specifically for CSV, however I have had to use python to generate documents for google sheets, when doing so the data would look something like this, where essentially each list is a row:

names = [['Name'], ['a'], ['b'], ['c']]

Going back to your problem, from my understanding, you want to essentially join each of these if they match.

names = [['Name'], ['a'], ['b'], ['c']]
data = [['Name', 'Server', 'IP-Address'], ['a', 'server1', '127.0.0.1'], ['b', 'server1', '127.0.0.1'], ['c', 'server2', '0.0.0.1']]

joined_data = []
for name, data in zip(names[1:], data[1:]):
    if name in data:
        joined_data.append(data)

print joined_data

Which produces the following output

[['a', 'server1', '127.0.0.1'], ['b', 'server1', '127.0.0.1'], ['c', 'server2', '0.0.0.1']]

You can make this more pythonic with a list comprehension though:

names = [['Name'], ['a'], ['b'], ['c']]
data = [['Name', 'Server', 'IP-Address'], ['a', 'server1', '127.0.0.1'], ['b', 'server1', '127.0.0.1']

joined_data = [data for name, data in zip(names[1:], data[1:]) if name[0] in data]
print joined_data

Which produces the following output

[['a', 'server1', '127.0.0.1'], ['b', 'server1', '127.0.0.1'], ['c', 'server2', '0.0.0.1']]

Final Points

As quoted in this post:

In Python 2, zip returns a list of tuples. This is fine when foo and bar are not massive. If they are both massive then forming zip(foo,bar) is an unnecessarily massive temporary variable, and should be replaced by itertools.izip

Which would look somewhat like this:

import itertools

names = [['Name'], ['a'], ['b'], ['c']]
data = [['Name', 'Server', 'IP-Address'], ['a', 'server1', '127.0.0.1'], ['b', 'server1', '127.0.0.1']

joined_data = [data for name, data in itertools.izip(names[1:], data[1:]) if name[0] in data]
print joined_data

Finally, the reason I do ...zip(names[1:], data[1:]), is to eliminate the first "row" (list) of the data which is the headers as they aren't really the data we're dealing with.

Python: creating a output CSV from multiple ones. Vlookup

1 Answers1