Searching and pulling data out of an Excel CSV created file

[Markalon]

Greetings. I'm new to Python programming, but I'm coming along. I'm having an issue trying to pull data from a CSV file created by an Excel spreadsheet (save as... csv function).

What I need to do is search through a list (rows) of data for a specific match to one of the columns, then pull that row of data into some variables.

The information looks like this in the Excel Spreadsheet:


100,102,20,0,FC,0,0,0,C,0,0,0,140000
295,343,83,25,FC,3,1,0,C,D342,0,0,530019

The 3rd column (showing 20 in the first row and 83 in the second row) is the data that I am evaluating for a match, and if a match is found I need to pull all the data from the row and put it into individual variables for use in the script.

I could make the 3rd column the 1st column instead, if it will help speed up the search process or make it easier.


Any ideas?

[bartonc]

Greetings. I'm new to Python programming, but I'm coming along. I'm having an issue trying to pull data from a CSV file created by an Excel spreadsheet (save as... csv function).

What I need to do is search through a list (rows) of data for a specific match to one of the columns, then pull that row of data into some variables.

The information looks like this in the Excel Spreadsheet:


100,102,20,0,FC,0,0,0,C,0,0,0,140000
295,343,83,25,FC,3,1,0,C,D342,0,0,530019

The 3rd column (showing 20 in the first row and 83 in the second row) is the data that I am evaluating for a match, and if a match is found I need to pull all the data from the row and put it into individual variables for use in the script.

I could make the 3rd column the 1st column instead, if it will help speed up the search process or make it easier.


Any ideas?

>>> sampleData = "100,102,20,0,FC,0,0,0,C,0,0,0,140000\n295,343,83,25,FC,3,1,0,C,D342,0,0,530019"
>>> key = "20"
>>> for line in sampleData.split('\n'):
...     data = line.split(',')
...     print data
...     if data[2] == key:
...         print 'found: value = %s' %key
...         
['100', '102', '20', '0', 'FC', '0', '0', '0', 'C', '0', '0', '0', '140000']
found: value = 20
['295', '343', '83', '25', 'FC', '3', '1', '0', 'C', 'D342', '0', '0', '530019']
>>> 

If you don't know how to iterate through a text file yet, just post back and we'll get you going.

Thanks for joining.

[Markalon]

Thanks for the quick reply. I'm not sure if it makes a difference, but I'm trying to pull data from a CSV file, not a text file. Granted, it is probably the same thing, as I believe CSV files are really just text files created with commas separating the cell values, right?

Is this the most efficient way of doing this? In any event, I'll give it a try and see if I can get it to work... thanks!

[Markalon]

Okay, here's the code I tried:

             csvFile = getComponent().filePath
                            pos = csvFile.rfind('Components\\')
                            csvFile = csvFile[:pos] + 'Scripts\\Cal Tables.csv'
 
                            myfile = open(csvFile)
                            myfile.seek(0)
 
                            for line in myfile.split('\n'):
                                data = line.split(',')
                                print data
                                if data[2] == agv_O_Cal.value and data[3] == agv_O_Mod.value:
                                    print 'found: value = %s' %agv_O_Cal.value, agv_O_Mod.value
                                    agv_P1 = data[5]
                                    agv_P2 = data[6]
                                    agv_P3 = data[7]
                                    agv_WS1 = data[9]
                                    agv_WS2 = data[10]
                                    agv_WS3 = data[11]
 
 
                                    print agv_P1, agv_P2, agv_P3, agv_WS1, agv_WS2, agv_WS3
 
 

And the error code I got was AttributeError: 'file' object has no attribute 'split'


So, I don't think its going to work that way, or I totally botched it and coded it wrong... lol.

[ghostdog74]

The information looks like this in the Excel Spreadsheet:
100,102,20,0,FC,0,0,0,C,0,0,0,140000
295,343,83,25,FC,3,1,0,C,D342,0,0,530019

this should be what your csv file look like, or is this what you see in your Excel spreadsheet? pls show what your csv file looks like? If its the normal csv, where each field is separated by commas, then what barton showed you is the way to go about doing it. generally:

for line in open("yourcsv"):
     line = line.split(",")
     thirdcol = line[2]
     if thirdcol == something: 
          print "do something"
 

[bvdet]

Okay, here's the code I tried:

             csvFile = getComponent().filePath
                            pos = csvFile.rfind('Components\\')
                            csvFile = csvFile[:pos] + 'Scripts\\Cal Tables.csv'
 
                            myfile = open(csvFile)
                            myfile.seek(0)
 
                            for line in myfile.split('\n'):
                                data = line.split(',')
                                print data
                                if data[2] == agv_O_Cal.value and data[3] == agv_O_Mod.value:
                                    print 'found: value = %s' %agv_O_Cal.value, agv_O_Mod.value
                                    agv_P1 = data[5]
                                    agv_P2 = data[6]
                                    agv_P3 = data[7]
                                    agv_WS1 = data[9]
                                    agv_WS2 = data[10]
                                    agv_WS3 = data[11]
 
 
                                    print agv_P1, agv_P2, agv_P3, agv_WS1, agv_WS2, agv_WS3
 
 

And the error code I got was AttributeError: 'file' object has no attribute 'split'


So, I don't think its going to work that way, or I totally botched it and coded it wrong... lol.

You were not that far off. I probably would go about it something like the following:

'''
csv file contents:
100,102,20,0,FC,0,0,0,C,0,0,0,140000\n295,343,83,25,FC,3,1,0,C,D342,0,0,530019\n300,359,50,40,FC,2,2,0,D,0,0,0,366484
'''
 
# read the csv file using readline() file method, yields a list of lines found in the file
# use list comprehension to split the data on ','
# dataList = [line.split(',') for line in open(csvData.csv).readlines()]
 
# I added another line of data for the example
dataList = [['100', '102', '20', '0', 'FC', '0', '0', '0', 'C', '0', '0', '0', '140000'], \
            ['295', '343', '83', '25', 'FC', '3', '1', '0', 'C', 'D342', '0', '0', '530019'],\
            ['300', '359', '50', '40', 'FC', '2', '2', '0', 'D', '0', '0', '0', '366484']]
 
# These are your variables
agv_O_Cal = 83
agv_O_Mod = 25
 
# convert to string, data types must match
key = map(str, (agv_O_Cal, agv_O_Mod))
 
for item in dataList:
    if key == item[2:4]:
        break
 
# item is the data line you want
print item

>>> ['295', '343', '83', '25', 'FC', '3', '1', '0', 'C', 'D342', '0', '0', '530019']

[Markalon]

Turns out the error was in the for statement. Once I took out the split component in the for statement, it worked fine.

Thanks everybody!

One more question,

for efficiency, how can I stop the file read once a match is found? I am constantly accessing the file, so closing it might not be the best idea. Would it be better just to leave it open?

[ghostdog74]

for efficiency, how can I stop the file read once a match is found?

if you found the match during the iteration and wants to stop, you can use the 'break' keyword. eg

...
for line in open("file"):
     if "something" in line:
          print "do something here"
          break