topic ArcGIS Pro uses the SQLite SQL dialect for expressions on Excel files? in ArcGIS Pro Questions

ArcGIS Pro uses the SQLite SQL dialect for expressions on Excel files?

Bud — Wed, 12 Jun 2024 18:22:26 GMT

Background:

It's interesting that ArcGIS Pro SQL expressions on Excel files use SQLite's SQL dialect, not the ArcGIS SQL dialect. I imagine one could do some pretty useful things with SQLite SQL, even though we're limited to the WHERE clause (as far as I know, we can't write full SQL queries on Excel files using SELECT, FROM, GROUP BY, etc.).

SQL reference for query expressions used in ArcGIS
Query expressions in ArcGIS adhere to standard SQL expressions. The SQL syntax you use within an expression differs depending on the data source. Each data source has its own variant of SQL, which are referred to as SQL dialects, such as the following:
...Mobile geodatabases, ST_geometry SQLite, GeoPackage, and Excel use SQLite SQL dialect.

Question:

How does ArcGIS Pro manage to use SQLite SQL on Excel files? I assume SQLite is not the native SQL dialect for Excel.

Does Pro copy the spreadsheet into a table in memory, and that allows ArcGIS to use SQLite queries on it?

Re: ArcGIS Pro uses the SQLite SQL dialect for expressions on Excel files?

JoshuaBixby — Fri, 05 Jan 2024 14:52:46 GMT

That documentation has an error, one way or the other. If you copy and paste a bit more of that section:

File-based data, including file geodatabases, shapefiles, in-memory table views, text files such as .dbf, .csv, .txt, .xlsx tables, and feature services that use standardized queries use the ArcGIS SQL dialect that supports a subset of SQL capabilities.
Mobile geodatabases, ST_geometry SQLite, GeoPackage, and Excel use SQLite SQL dialect.

The first bullet point says "xlsx tables" use standardized queries, while the second bullet says "Excel use SQLite SQL dialect." Which one is right?

Re: ArcGIS Pro uses the SQLite SQL dialect for expressions on Excel files?

Bud — Fri, 05 Jan 2024 15:49:12 GMT

I did a test where I dragged a .xlsx sheet from Catalog into the map. And then did a Select By Attributes using the following SQL expression:

sqlite_version() is not null

It ran without errors and selected all rows.

That SQL expression uses a function that is specific to SQLite: sqlite_version(). Since the expression worked on the .xlsx, and not on other file types like File Geodatabases that use the ArcGIS SQL dialect, then I think that tells us that ArcGIS Pro uses SQLite SQL on .xlsx files.

So the second line in your quoted documentation seems to be correct, and the first line is incorrect.

I'll ask Esri to fix that documentation. Thanks for pointing that out.

Re: ArcGIS Pro uses the SQLite SQL dialect for expressions on Excel files?

Bud — Fri, 05 Jan 2024 15:52:14 GMT

I submitted feedback:

https://pro.arcgis.com/en/pro-app/3.1/help/mapping/navigation/sql-reference-for-elements-used-in-query-expressions.htm#:~:text=txt%2C%20.-,xlsx%20tables,-%2C%20and%20feature%20services
This bullet point is incorrect; [Excel uses the SQLite SQL dialect, not the ArcGIS SQL dialect]:
"File-based data, including file geodatabases, shapefiles, in-memory table views, text files such as .dbf, .csv, .txt, .xlsx tables, and feature services that use standardized queries use the ArcGIS SQL dialect that supports a subset of SQL capabilities."
.XLSX tables use SQLite SQL, not the ArcGIS SQL.
The next bullet point is correct (and contradicts the first bullet point):
"Mobile geodatabases, ST_geometry SQLite, GeoPackage, and Excel use SQLite SQL dialect."
See this post for more information:
"ArcGIS Pro uses the SQLite SQL dialect for expressions on Excel files?"
https://community.esri.com/t5/arcgis-pro-questions/arcgis-pro-uses-the-sqlite-sql-dialect-for/m-p/1366786/highlight/true#M76990
Could that typo be corrected?
Thanks,

Unfortunately, I've found that submitting feedback is often a waste of time. Feedback is not usually addressed. So we'll see if anything happens or if the typo persists.

Re: ArcGIS Pro uses the SQLite SQL dialect for expressions on Excel files?

JoshuaBixby — Fri, 05 Jan 2024 16:11:02 GMT

I submitted feedback either last week or earlier this week about it already. My experience with submitting feedback on documentation through the web pages is that I get better resolution than submitting a documentation defect with Esri Support. Neither approach results in 100% resolution, not to mention timeliness, but somewhere between 2/3 and 3/4 of my comments get addressed eventually.

Re: ArcGIS Pro uses the SQLite SQL dialect for expressions on Excel files?

Bud — Mon, 08 Jan 2024 07:41:41 GMT

The SQL reference for query expressions used in ArcGIS docs also say:

Subqueries
A subquery is a query nested in another query and is supported by geodatabase data sources only.

I suppose that's not strictly true, since ArcGIS Pro uses SQLite SQL for Excel SQL expressions.

So I'm able to use a subquery (in this case, it's a correlated subquery) in Select by Attributes or a Definition Query on an Excel file. For example, for each ASSET_ID, get the road inspection row that has the latest DATE_. If there is a tie, use the row with the largest CONDITION value.

roadinsptable$.objectid IN (
       SELECT objectid
         FROM roadinsptable$ r2
        WHERE r2.asset_id = roadinsptable$.asset_id
     ORDER BY date_ DESC, condition DESC
        LIMIT 1
              )

Source:

It's strange that Excel spreadsheets (not a database) have more SQL support than file geodatabases in ArcGIS Pro. As we know, FGDBs don't support correlated subqueries.

Re: ArcGIS Pro uses the SQLite SQL dialect for expressions on Excel files?

Bud — Sat, 06 Jan 2024 14:04:17 GMT

Add a "Send me a copy" checkbox to the documentation feedback form — How to suggest an idea about the Esri documentation feedback form