Hello. I have an excel table which contains a list of wind turbine IDs and their associated sound power levels at various octave bands.
The table roughly looks like this...
ID | 63Hz | 125Hz | 250Hz | 500Hz | 1000Hz | 2000Hz | 4000Hz | 8000Hz |
---|---|---|---|---|---|---|---|---|
T19 | 79.4 | 89.4 | 98.1 | 100.2 | 100.5 | 97.9 | 96.0 | 90.2 |
Previous I have manually input the variables for each turbine as an array in the following format
T19 = [79.4, 89.4, 98.1, 100.2, 100.5, 97.9, 96.0, 90.2]
Now I am looking of a way to automate many lines from an excel table to automatically create a variable like I have done manually. I have successfully been able to print the data in the correct format but obviously this isn't setting it as a variable.
Does anyone have any ideas how I could achieve this?
Many thanks for your help
David
Solved! Go to Solution.
Hi David,
There seems to be some line intend issue when i posted the script in the forum.
I have uploaded the script in this location.Variable_from_excel.py
Hi David,
Below code reads through a Excel datasheet and creates a dictionary. You can reference the values for your key column from the dictionary as key.
import arcpy
import arcgisscripting
gp = arcgisscripting.create()
def Message(sMsg):
print sMsg
gp.AddMessage(sMsg)
# Define the feature class
table = "C:\Users\deenr\Desktop\Data.xlsx\Sheet1$"
keyField = "ID"
keyIdx = -1
fields = arcpy.ListFields(table)
# Get Key column Id
fIdx = 0
for field in fields:
if field.name == keyField:
keyIdx = fIdx
break
fIdx += 1
dictionary = {}
with arcpy.da.SearchCursor(table, ("*")) as cursor:
for row in cursor:
idx = 0
for cell in row:
if idx == fIdx:
if dictionary.has_key(str(cell)) == False:
dictionary[str(cell)] = []
else:
if dictionary.has_key(str(row[fIdx])) == True:
dictionary[str(row[fIdx])].append(cell)
idx += 1
# Print whole dictionary
Message(str(dictionary.items()))
for key in dictionary.keys():
# Print Values
Message(str(dictionary[key]))
Thank you for your quick answer Riyas.
I had to tweak a few of the lines for the for loops (lines 23-26 and 33-41), hopefully I did this correctly. I do get an output from the code. One row from excel comes out as
('T2', []), ('97.9', []), ('97.1', []), ('95.8', []), ('95.9', []), ('95.1', []), ('74.9', [])
Is this what you would expect?
Hi David,
Not sure what change you made to the code. Below is the data used and output for the code I posted.
Line 46 is printing the whole dictionary out, including key and value.
Line 51 is printing the values for each key from the dictionary.
import arcpy
import arcgisscripting
gp = arcgisscripting.create()
def Message(sMsg):
print sMsg
gp.AddMessage(sMsg)
# Define the feature class
table = r"Z:\test1.xls\Sheet1$"
keyField = "ID"
keyIdx = -1
fields = arcpy.ListFields(table)
# Get Key column Id
fIdx = 0
for field in fields:
if field.name == keyField:
keyIdx = fIdx
break
fIdx += 1
dictionary = {}
with arcpy.da.SearchCursor(table, ("*")) as cursor:
for row in cursor:
idx = 0
for cell in row:
if idx == fIdx:
if dictionary.has_key(str(cell)) == False:
dictionary[str(cell)] = []
else:
if dictionary.has_key(str(row[fIdx])) == True:
dictionary[str(row[fIdx])].append(cell)
idx += 1
# Print whole dictionary
Message(str(dictionary.items()))
for key in dictionary.keys():
# Print Values
Message(str(dictionary[key]))
this is the updated code (not sure why the extra empty lines have added, that isn't shown on my screen!), again apologies if I've mess with how it works because we seem to have differing outputs. If it helps at all I'm using Python in PythonWin rather than directly through ArcGIS. Thanks for your help
Hi David,
There seems to be some line intend issue when i posted the script in the forum.
I have uploaded the script in this location.Variable_from_excel.py
Perfect! Thanks so much Riyas!
Dear David,
I am not sure for what purpose you would like to create the variable? But if you would like to create this kind of array you can use the Excel formula "concatenate" to create the array from your column values.
If you then like to transfer the Excel data to ArcGIS you can use the GISconnector for Excel. If you afterwards change your data again, in ArcGIS or in Excel, you can synchronize your data again using the GISconnector.
...everythin without Python
Best,
Matthias
Thanks for your answer Matthias. That would appear to work well but we are hoping to keep everything in one self-contain python script. I'll keep it in mind for other work though