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?
7 13038
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.
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!
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.
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 2,851
Expert Mod 2GB
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']
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?
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
-
Post your reply Sign in to post your reply or Sign up for a free account.
Similar topics
9 posts
views
Thread by Ksenia Marasanova |
last post: by
|
5 posts
views
Thread by Pai |
last post: by
|
1 post
views
Thread by bryanilton |
last post: by
|
4 posts
views
Thread by washoetech |
last post: by
|
1 post
views
Thread by david shapiro |
last post: by
|
1 post
views
Thread by Usarian Skiff |
last post: by
|
3 posts
views
Thread by Bharathi Kumar |
last post: by
|
reply
views
Thread by MKH |
last post: by
|
12 posts
views
Thread by Alexnb |
last post: by
| | | | | | | | | | |