By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,359 Members | 1,481 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,359 IT Pros & Developers. It's quick & easy.

Searching and pulling data out of an Excel CSV created file

P: 13
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?
Apr 15 '07 #1
Share this Question
Share on Google+
7 Replies


bartonc
Expert 5K+
P: 6,596
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
  1. >>> 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"
  2. >>> key = "20"
  3. >>> for line in sampleData.split('\n'):
  4. ...     data = line.split(',')
  5. ...     print data
  6. ...     if data[2] == key:
  7. ...         print 'found: value = %s' %key
  8. ...         
  9. ['100', '102', '20', '0', 'FC', '0', '0', '0', 'C', '0', '0', '0', '140000']
  10. found: value = 20
  11. ['295', '343', '83', '25', 'FC', '3', '1', '0', 'C', 'D342', '0', '0', '530019']
  12. >>> 
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.
Apr 15 '07 #2

P: 13
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!
Apr 15 '07 #3

P: 13
Okay, here's the code I tried:
Expand|Select|Wrap|Line Numbers
  1.              csvFile = getComponent().filePath
  2.                             pos = csvFile.rfind('Components\\')
  3.                             csvFile = csvFile[:pos] + 'Scripts\\Cal Tables.csv'
  4.  
  5.                             myfile = open(csvFile)
  6.                             myfile.seek(0)
  7.  
  8.                             for line in myfile.split('\n'):
  9.                                 data = line.split(',')
  10.                                 print data
  11.                                 if data[2] == agv_O_Cal.value and data[3] == agv_O_Mod.value:
  12.                                     print 'found: value = %s' %agv_O_Cal.value, agv_O_Mod.value
  13.                                     agv_P1 = data[5]
  14.                                     agv_P2 = data[6]
  15.                                     agv_P3 = data[7]
  16.                                     agv_WS1 = data[9]
  17.                                     agv_WS2 = data[10]
  18.                                     agv_WS3 = data[11]
  19.  
  20.  
  21.                                     print agv_P1, agv_P2, agv_P3, agv_WS1, agv_WS2, agv_WS3
  22.  
  23.  
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.
Apr 15 '07 #4

Expert 100+
P: 511
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
  1. for line in open("yourcsv"):
  2.      line = line.split(",")
  3.      thirdcol = line[2]
  4.      if thirdcol == something: 
  5.           print "do something"
  6.  
Apr 15 '07 #5

bvdet
Expert Mod 2.5K+
P: 2,851
Okay, here's the code I tried:
Expand|Select|Wrap|Line Numbers
  1.              csvFile = getComponent().filePath
  2.                             pos = csvFile.rfind('Components\\')
  3.                             csvFile = csvFile[:pos] + 'Scripts\\Cal Tables.csv'
  4.  
  5.                             myfile = open(csvFile)
  6.                             myfile.seek(0)
  7.  
  8.                             for line in myfile.split('\n'):
  9.                                 data = line.split(',')
  10.                                 print data
  11.                                 if data[2] == agv_O_Cal.value and data[3] == agv_O_Mod.value:
  12.                                     print 'found: value = %s' %agv_O_Cal.value, agv_O_Mod.value
  13.                                     agv_P1 = data[5]
  14.                                     agv_P2 = data[6]
  15.                                     agv_P3 = data[7]
  16.                                     agv_WS1 = data[9]
  17.                                     agv_WS2 = data[10]
  18.                                     agv_WS3 = data[11]
  19.  
  20.  
  21.                                     print agv_P1, agv_P2, agv_P3, agv_WS1, agv_WS2, agv_WS3
  22.  
  23.  
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
  1. '''
  2. csv file contents:
  3. 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
  4. '''
  5.  
  6. # read the csv file using readline() file method, yields a list of lines found in the file
  7. # use list comprehension to split the data on ','
  8. # dataList = [line.split(',') for line in open(csvData.csv).readlines()]
  9.  
  10. # I added another line of data for the example
  11. dataList = [['100', '102', '20', '0', 'FC', '0', '0', '0', 'C', '0', '0', '0', '140000'], \
  12.             ['295', '343', '83', '25', 'FC', '3', '1', '0', 'C', 'D342', '0', '0', '530019'],\
  13.             ['300', '359', '50', '40', 'FC', '2', '2', '0', 'D', '0', '0', '0', '366484']]
  14.  
  15. # These are your variables
  16. agv_O_Cal = 83
  17. agv_O_Mod = 25
  18.  
  19. # convert to string, data types must match
  20. key = map(str, (agv_O_Cal, agv_O_Mod))
  21.  
  22. for item in dataList:
  23.     if key == item[2:4]:
  24.         break
  25.  
  26. # item is the data line you want
  27. print item
Expand|Select|Wrap|Line Numbers
  1. >>> ['295', '343', '83', '25', 'FC', '3', '1', '0', 'C', 'D342', '0', '0', '530019']
Apr 15 '07 #6

P: 13
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?
Apr 15 '07 #7

Expert 100+
P: 511

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
  1. ...
  2. for line in open("file"):
  3.      if "something" in line:
  4.           print "do something here"
  5.           break
  6.  
Apr 15 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.