Query the fields of an attribute table of a .shp file (ArcMap) from an .xlsm file with Python

447
10
Jump to solution
3 weeks ago
BriamR
by
New Contributor II

I would like to know if there is any way through Python to query the fields of an attribute table of a .shp file (ArcMap) from an .xlsm file (Excel with macros enabled), this since what I currently do repetitively and manual is to copy the attribute table from ArcMap to my Excel file "VINCULACION_S.xlsm".

One of the attribute tables that I want to copy is the one I show in this image,

JosephBriamRamonRodriguez_1-1622745004422.png

Towards the .xlsm file already mentioned, which I show in this image.

JosephBriamRamonRodriguez_0-1622744959302.png

 

This is a good start apparently

 

# Set local variables

inTable = outTable

outXLS = VHFolder + "/Vinculacion_S.xlsx" #this is where I had to give the file path and then the file name

# Execute TableToExcel

arcpy.TableToExcel_conversion(inTable, outXLS)

ersion(inTable, outXLS)

 

 

 

Tags (3)
0 Kudos
1 Solution

Accepted Solutions
BriamR
by
New Contributor II

I got this code as a solution to the post

import arcpy
import openpyxl as px

def main():
    wb = px.load_workbook(r"C:\Users\Hp\Desktop\Ejemplo\VINCULACION_S.xlsm", read_only=False, keep_vba=True)
    ws = wb['VINCULACION_SH_NUE']
    in_features = r"C:\Users\Hp\Desktop\Ejemplo\VH_Dissolve.shp"

    row_num = 3
    with arcpy.da.SearchCursor(
        in_features,
        ["COLOR", "INTERNO_DE", "CLASE_DEMA", "COUNT_AREA", "SUM_AREA", "SUM_LENGTH"],
    ) as cursor:
        for row in cursor:
            ws.cell(row=row_num, column=2).value = row[0]
            ws.cell(row=row_num, column=3).value = row[1]
            ws.cell(row=row_num, column=4).value = row[2]
            ws.cell(row=row_num, column=6).value = row[3]
            ws.cell(row=row_num, column=7).value = row[4]
            ws.cell(row=row_num, column=8).value = row[5]
            row_num += 1
    wb.save(r"C:\Users\Hp\Desktop\Ejemplo\VINCULACION_S.xlsm")

View solution in original post

10 Replies
JoeBorgione
MVP Esteemed Contributor

Please explain "query the fields of an attribute table of a .shp file (ArcMap) from an .xlsm file "

What do you want to do?  Cursor though the excel file and see if some values exist in the attribute table?

 

can't wait to retire....
BlakeTerhune
MVP Regular Contributor

The documentation says

Excel Workbooks (.xlsx) and Microsoft Excel 5.0/95 Workbook (.xls) formats are supported as input.

A quick test with an .xlsm file seems to support this. ArcMap doesn't even show the .xlsm file to interact with. You'll have to save the Excel file as a supported format.

BlakeTerhune
MVP Regular Contributor

I just tested changing the file extension from xlsm to xlsx and ArcMap was able to read the data. If you're comfortable, you could use Python to change the file extension, read the data, then change the file extension back when finished.

BriamR
by
New Contributor II

@JoeBorgione  Thanks for your interest in my post

What I want to do is query the fields of a .shp file from an excel .xlsm file, that is, avoid the repetitive and manual process of copying the attribute table from arcmap to the working .xlsm file,

For example, query and write the values ​​of this attribute table displayed in arcmap and contained in the VHDissolve353.shp file from my Excel VINCULACION.xlsm file

Screenshot 2021-06-03 110625.pngScreenshot 2021-06-03 110738.png

Note: The attribute table and the table in excel have the same fields.

@BlakeTerhune  The process that you propose to me in python sounds interesting, could you help me with the way or an example to be able to achieve it. Thank you

 

 

 

0 Kudos
BlakeTerhune
MVP Regular Contributor

@BriamR wrote:

@BlakeTerhune  The process that you propose to me in python sounds interesting, could you help me with the way or an example to be able to achieve it. Thank you

Here's a snippet to change the file extension with Python. I would put that in a try/finally block to ensure it gets changed back to the correct file extension when the script exits.

However, I'm still not completely clear on what exactly you're trying to do. Are you updating the fields of existing records in the shapefile or creating new records? If they are new records, what are you using for geometry (X,Y)?

BriamR
by
New Contributor II

@BlakeTerhune  The idea is to perform a query in the SHP file and write the results of the query in an existing Excel file. Note: update the question, attaching the .shp and .xlsm files.

0 Kudos
BlakeTerhune
MVP Regular Contributor

So your taking data from the shapefile and writing it to Excel? Can it be a new Excel file or do you have to write it into the existing Excel file?

BriamR
by
New Contributor II

@BlakeTerhune  
Yes, I take the data from the .shp to write it in the .xlsm, the excel book is always an existing file.

.

.

To get the .shp file, I export it from an AutoCAD MAP 3D drawing, then work it in ArcMap. Now the query I am looking for is to take the table attributes from the .shp file to add them to an already existing Excel file "VINCULACION_S.xlsm"

0 Kudos
BlakeTerhune
MVP Regular Contributor

In that case, you'll probably have to use a library like OpenPyXL to write to the existing Excel file. I've never done that so you'll have to explore that on your own. Start with a SearchCursor on the shapefile using a where_clause to query on the records you want. Then iterate over the SearchCursor and find a way to write the rows to Excel.