topic Re: Query the fields of an attribute table of a .shp file (ArcMap) from an .xlsm file with Python in Python Questions

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

BriamR — Thu, 03 Jun 2021 18:30:18 GMT

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,

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

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)

Re: Consult ArcMap attribute table or shapefile file (.shp) from Excel through Python

JoeBorgione — Thu, 03 Jun 2021 15:57:44 GMT

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?

Re: Consult ArcMap attribute table or shapefile file (.shp) from Excel (.xlsm) through Python

BlakeTerhune — Thu, 03 Jun 2021 16:03:30 GMT

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.

Re: Consult ArcMap attribute table or shapefile file (.shp) from Excel (.xlsm) through Python

BlakeTerhune — Thu, 03 Jun 2021 16:08:33 GMT

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.

Re: Consult ArcMap attribute table or shapefile file (.shp) from Excel (.xlsm) through Python

BriamR — Thu, 03 Jun 2021 16:22:49 GMT

@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

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

Re: Consult ArcMap attribute table or shapefile file (.shp) from Excel (.xlsm) through Python

BlakeTerhune — Thu, 03 Jun 2021 18:15:13 GMT

@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)?

Re: Consult ArcMap attribute table or shapefile file (.shp) from Excel (.xlsm) through Python

BriamR — Thu, 03 Jun 2021 22:22:04 GMT

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

Re: Consult ArcMap attribute table or shapefile file (.shp) from Excel (.xlsm) through Python

BlakeTerhune — Thu, 03 Jun 2021 18:31:57 GMT

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?

Re: Consult ArcMap attribute table or shapefile file (.shp) from Excel (.xlsm) through Python

BriamR — Thu, 03 Jun 2021 19:10:44 GMT

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

Re: Consult ArcMap attribute table or shapefile file (.shp) from Excel (.xlsm) through Python

BlakeTerhune — Thu, 03 Jun 2021 21:09:22 GMT

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.

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

BriamR — Tue, 08 Jun 2021 14:54:09 GMT

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