DBMS error when importing XLSX into ArcGIS Pro

Question

I am working with an XLSX, and consistently receiving either a "underlying DBMS error", or associated with the file.

I have worked through all the suggestions and limitations from ESRI to the best of my understanding. My field names start with a letter, have no spaces, are less than 30 characters, and only letters and underscores. I have also made sure all my columns were consistently formatted: date, text, or number. Further, I have resaved using different filenames, and have copied the data to new workbooks in case of corruption. I have also restarted PRO as well as my computer.

I have 1391 rows, and 23 columns.

Is there a limit to the number of columns an imported worksheet might be allowed or potentially another issue I have not considered?

I have noticed that every time I copy the data to a new workbook, I get the error. When I take the new worksheet and make sure all the column formatting is consistent, I don't get the error, but the worksheet does not show up (image above). When I take that same newly created and column-formatted workbook, copy and paste it into yet another new workbook, the error returns, until I re-format the columns, etc, etc.

Due to data sensitivity, I can't provide an image example, but if necessary I will rename things to post.

I know https://gis.stackexchange.com/questions/312470 has previously been asked here, but no response was given, and that question is now deleted.

Note: I recently imported the same spreadsheet into MAP and it works just fine. While I'd like to exclusively use MAP as it has been so much easier to use for everything I do, I am trying to adopt ArcGIS Pro due to the phasing out of MAP support.

Answer 1

I too have had all sorts of issues with data in Excel xlsx format. It feels like ESRI does not appreciate that most of the non-GIS world use this non-GIS friendly format...

If you can, I would move your sheets over into an xls workbook, this seems to be more stable. Well that's my experience.

Finally I find that if you are querying data that is a join it can be painfully slow and the only solution I came to was to export the data into a file geodatabase table. This may not be acceptable to your workflow especially if your Excel file is updated daily. You can ease the pain of conversion by automating it all in say modelbuilder.

Answer 2

I've had a lot of trouble using and XLS or XLSX formats with ArcGIS as well as QGIS. This problem usually goes away when I change my XLSX format to a CSV file. This can be done in the save as menu. Is there some concern of a loss of functions there? Or another reason a CSV file wouldn't work?

Answer 3

I know this is a bit of an older thread, but for those encountering the same issue in ArcGIS Pro 2.4 or newer, make sure you download and install the Microsoft Access Database Engine driver as documented here. This requirement isn't clearly documented by ESRI and is a stumbling block for those of used to the built-in Excel functionality of ArcMap. Excel imports (both *.xls and *.xlsx) work much better once you have the required drivers.