Create variables from Excel

2539
8
Jump to solution
08-20-2014 02:12 AM
DavidFotheringham1
Occasional Contributor

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

ID63Hz125Hz
250Hz500Hz1000Hz2000Hz4000Hz8000Hz
T1979.489.498.1100.2100.597.996.090.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

0 Kudos
1 Solution

Accepted Solutions
RiyasDeen
Occasional Contributor III

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

View solution in original post

0 Kudos
8 Replies
RiyasDeen
Occasional Contributor III

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

0 Kudos
DavidFotheringham1
Occasional Contributor

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?

0 Kudos
RiyasDeen
Occasional Contributor III

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.

Untitled.png

0 Kudos
DavidFotheringham1
Occasional Contributor

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

0 Kudos
RiyasDeen
Occasional Contributor III

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

0 Kudos
DavidFotheringham1
Occasional Contributor

Perfect! Thanks so much Riyas!

0 Kudos
MatthiasAbele
New Contributor II

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

0 Kudos
DavidFotheringham1
Occasional Contributor

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

0 Kudos