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

1680
10
Jump to solution
06-03-2021 08:46 AM
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

#设置本地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)
0Kudos
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?

That should just about do it....
BlakeTerhune
MVP Regular Contributor

Thedocumentationsays

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

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

@BlakeTerhuneThe 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

0Kudos
BlakeTerhune
MVP Regular Contributor

@BriamRwrote:

@BlakeTerhuneThe 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 snippetto 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

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

0Kudos
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"

0Kudos
BlakeTerhune
MVP Regular Contributor

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