14

I have an Excel file(that I am exporting as a csv) that I want to parse, but I am having trouble with finding the best way to do it. The csv is a list of computers in my network, and what accounts are in the local administrator group for each one. I have done something similar with tuples, but the number of accounts for each computer range from 1 to 30. I want to build a list of lists, then go through each list to find the accounts that should be there(Administrator, etc.) and delete them, so that I can then export a list of only accounts that shouldn't be a local admin, but are. The csv file is formatted as follows:

"computer1"    Administrator    localadmin    useraccount
"computer2"    localadmin       Administrator 
"computer3"    localadmin       Administrator user2account

Any help would be appreciated

EDIT: Here is the code I am working with

import csv 
import sys #used for passing in the argument
file_name = sys.argv[1] #filename is argument 1
with open(file_name, 'rU') as f:  #opens PW file
    reader = csv.reader(f)
    data = list(list(rec) for rec in csv.reader(f, delimiter=',')) #reads csv into a list of lists
    f.close() #close the csv

for i in range(len(data)):
    print data[i][0] #this alone will print all the computer names
    for j in range(len(data[i])) #Trying to run another for loop to print the usernames
        print data[i][j]

The issue is with the second for loop. I want to be able to read across each line and for now, just print them.

Michael Dornisch
  • 177
  • 1
  • 2
  • 8
  • 1
    Can you post any code you already have? Even if the attempt failed - it'll be much more helpful to include the problematic code. – Lix Nov 07 '13 at 14:28
  • 1
    Look at either python's csv library or tablib and give it a shot. – jgranger Nov 07 '13 at 14:30
  • Also is there any way you could use commas as delimiters in the CSV file? It just makes it follow the default, that may be the problem you're facing. Furthermore, what isn't working for you *exactly*, i.e. what is the problem? – Aleksander Lidtke Nov 07 '13 at 14:30
  • Posting the code now. The csv does use commas as a delimiter. I just used tabs to show the format the csv file is saved in. – Michael Dornisch Nov 07 '13 at 14:59
  • Have you considered working directly with the excel files? You could do this by using the Python Excel modules (http://www.python-excel.org/) and this should save you the trouble of having to worry about dealing with a csv – wnnmaw Nov 07 '13 at 15:40
  • FYI, the f.close() is not needed in this case. The "with" construct will automatically close the resource when the "with" code block is exited. – ben_frankly Nov 07 '13 at 16:55
  • It sounds suitable for a dictionary, with computers as the key and a list of users as the values? – beroe Nov 07 '13 at 17:36

3 Answers3

18

This should get you on the right track:

import csv 
import sys #used for passing in the argument
file_name = sys.argv[1] #filename is argument 1
with open(file_name, 'rU') as f:  #opens PW file
    reader = csv.reader(f)
    data = list(list(rec) for rec in csv.reader(f, delimiter=',')) #reads csv into a list of lists

    for row in data:
        print row[0] #this alone will print all the computer names
        for username in row: #Trying to run another for loop to print the usernames
            print username

Last two lines will print all of the row (including the "computer"). Do

for x in range(1, len(row)):
    print row[x]

... to avoid printing the computer twice.

Note that f.close() is not required when using the "with" construct because the resource will automatically be closed when the "with" block is exited.

Personally, I would just do:

import csv 
import sys #used for passing in the argument
file_name = sys.argv[1] #filename is argument 1
with open(file_name, 'rU') as f:  #opens PW file
    reader = csv.reader(f)
    # Print every value of every row. 
    for row in reader:
        for value in row: 
            print value

That's a reasonable way to iterate through the data and should give you a firm basis to add whatever further logic is required.

ben_frankly
  • 9,270
  • 3
  • 18
  • 22
  • 1
    The mode `U` is deprecated since `3.4` and will be removed in `4.0`. Refer to [here](https://docs.python.org/3/library/functions.html#open). – SparkAndShine Mar 02 '16 at 13:27
  • 3
    You don't need a list comprehension. `data = list(csv.reader(f, delimiter=','))` is plenty (each row is already a list). – Martijn Pieters Dec 08 '16 at 18:34
2

This is how I opened a .csv file and imported columns of data as numpy arrays - naturally, you don't need numpy arrays, but...

data = {}

app = QApplication( sys.argv )
fname = unicode ( QFileDialog.getOpenFileName() )
app.quit()
filename = fname.strip('.csv') + ' for release.csv'

#open the file and skip the first two rows of data
imported_array = np.loadtxt(fname, delimiter=',', skiprows = 2)

data = {'time_s':imported_array[:,0]}
data['Speed_RPM'] = imported_array[:,1]
mauve
  • 2,707
  • 1
  • 20
  • 34
2

It can be done using the pandas library.

import pandas as pd

df = pd.read_csv(filename)

list_of_lists = df.values.tolist()

This approach applies to other kinds of data like .tsv, etc.

Iman Kermani
  • 919
  • 9
  • 14