nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Create variables from Excel

DavidFotheringham1

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

Najděte více příspěvků označených jako

Accepted answers

RiyasDeen

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

All comments

DavidFotheringham1

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

MatthiasAbele

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

DavidFotheringham1

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

RiyasDeen

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.

DavidFotheringham1

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?

RiyasDeen

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]))

Připojte se k diskusi

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.