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?

Expand|Select|Wrap|Line Numbers

 
>>> 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:

Expand|Select|Wrap|Line Numbers

 
             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:

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

             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:

Expand|Select|Wrap|Line Numbers

 '''

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

Expand|Select|Wrap|Line Numbers

 >>> ['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

Expand|Select|Wrap|Line Numbers

 
...

for line in open("file"):

     if "something" in line:

          print "do something here"

          break

Searching and pulling data out of an Excel CSV created file

Post your reply

Similar topics

Question stats

	Ask Question
Home Questions Articles Browse Topics Latest Top Members FAQ