topic Re: Impossible to convert XY to Point in ArcGIS Pro Questions

Impossible to convert XY to Point

JasonBOCQUET — Mon, 23 Oct 2023 13:43:57 GMT

Hi Community

I want to convert a table with an X and Y couple who is hosted in a database, but when i try to use the "Display XY Data" tool, i have this error :

What does it means ?

Re: Impossible to convert XY to Point

Brian_Wilson — Mon, 23 Oct 2023 15:24:09 GMT

The connection to your database is failing.

First try writing to a shapefile instead of a geodatabase to test the tool then fix your database connection.

You have the ODBC 18 driver installed. If you are using ArcMap that will fail. It won't work with ODBC version > 17.

Re: Impossible to convert XY to Point

JasonBOCQUET — Mon, 23 Oct 2023 15:35:54 GMT

Hi, I'm using ArcGIS Pro

I can connect to my database because i can use the table from my database. It's a SQL database with only table files.

I don't understand your point, can you explain me what I have to do ?

Re: Impossible to convert XY to Point

Robert_LeClair — Mon, 23 Oct 2023 16:24:29 GMT

@JasonBOCQUET - my first question is how are you connecting to your SQL Server database? Are you suing an OLE DB connection or a database connection to MS SQL Server using this approach?

Re: Impossible to convert XY to Point

Brian_Wilson — Mon, 23 Oct 2023 16:48:53 GMT

You added a table to your map. Then you used Display XY Data by right clicking??

My first thought was that you specified the Output Feature Class wrong. I still suggest you write to a shapefile to test it but I also see another message about "empty_out_bounds" and the error suggests a typo in one of your "field" parameters. Can you take a screenshot of the dialog and post it here? Like this....

In my simple CSV file test it was able to find the column names on its own, no chance for me to type it wrong.

Re: Impossible to convert XY to Point

JasonBOCQUET — Tue, 24 Oct 2023 08:45:41 GMT

Hi, for the screenshot you ask :

I test with ".shp" in the name on the Output Feature Class, but it change nothing.

Here the error message :

Re: Impossible to convert XY to Point

JasonBOCQUET — Tue, 24 Oct 2023 08:48:59 GMT

Hi @Robert_LeClair

I'm connecting to my SQL server following this path :

I click on Database > New Database Connexion

Database Platform "SQL Server" and Authentication Type : "Database authentication".

This is not the good way to connect to my database ?

Re: Impossible to convert XY to Point

Robert_LeClair — Tue, 24 Oct 2023 14:38:03 GMT

Hi @JasonBOCQUET - yes, this is the preferred way to connect to an enterprise geodatabase and then creating a *.sde connection file in ArcGIS Pro to see your data. When you create the *.sde connection file for your eGDB, do you see feature classes, feature datasets and/or more? Based upon the GP error message, it does seem there is a connection problem. This tech support article/workflow may assist.

Re: Impossible to convert XY to Point

JasonBOCQUET — Tue, 24 Oct 2023 14:49:03 GMT

I see only table in my SQL database because we host only table with X/Y column in our database.

That's why I have to use the "Display X/Y data" tools.

Re: Impossible to convert XY to Point

Robert_LeClair — Tue, 24 Oct 2023 14:59:40 GMT

Thank you the additional information and view of the non-spatial tables. I do still think there's a connection issue based upon the GP tools error message. In this table, you can see the SQL Server version and supported SQL Server clients required to make a connection. What SQL Server version and client version are you using?

Re: Impossible to convert XY to Point

JasonBOCQUET — Tue, 24 Oct 2023 15:08:26 GMT

It's Microsoft ODBC Driver 18 for SQL Server . I don't know for MS SQL Server if it is 2019 or 2022 version.

Re: Impossible to convert XY to Point

Robert_LeClair — Tue, 24 Oct 2023 15:14:37 GMT

Great - thank you. Regardless of 2019 or 2022, the MS ODBC Driver 18 is correct. One key here on the help - "** Microsoft changed the default encryption setting for the ODBC driver to yes with the release of ODBC Driver 18. To ensure existing database connections continue to work even if you update to ODBC Driver 18, encryption is set to no by default." Do you know if the encrption is set to no?

Re: Impossible to convert XY to Point

JasonBOCQUET — Tue, 24 Oct 2023 15:17:52 GMT

I'm not in charge of the installation of our solution so i don't know. I have a easy way to know that ?

Re: Impossible to convert XY to Point

Robert_LeClair — Tue, 24 Oct 2023 15:34:58 GMT

A workaround is listed here that may work for you.

Re: Impossible to convert XY to Point

Brian_Wilson — Tue, 24 Oct 2023 15:38:52 GMT

BTW I am just some guy so feel free to totally ignore me 🙂 Robert will eventually figure this out.

Looking at the actual code (since it's Python we can do that),

if empty_out_bounds: arcpy.AddIDMessage("WARNING", 100160) arcpy.AddIDMessage("WARNING", 192, "{}: {}".format(str(arcpy.GetIDMessage(84526)).lower(),

It is saying "if there is something in the list empty_out_bound, then send a warning message". It has some code that is inside a try/except that's firing... it's doing an insertRow so that's where it dies. It looks like up near the top it creates the feature class. Is that happening? If so then you have write permissions and you have database access. The line about "invalid syntax near 'voie'" is weird... that's why I asked for the screenshot.

What's a "voie" and where is that coming from??

Since the error is tripping on an "insert" call it means output is still going to a database. I don't get that.

I would try generating a subset of the input data (like, 3 rows?) and put it into a CSV file and write to a shapefile therefore removing ODBC from the whole thing and see what happens then.

Keep breaking the problem down into smaller and smaller pieces.

Wheee fun fun.

Re: Impossible to convert XY to Point

JasonBOCQUET — Tue, 24 Oct 2023 15:40:26 GMT

I try with Encrypt=no, i can connect to my database, but i have the same error.

And if I try with Encrypt=yes i can't connect to my database.

Re: Impossible to convert XY to Point

JasonBOCQUET — Tue, 24 Oct 2023 15:44:33 GMT

"voie" is a french term to said "road"

I have this term in my header column some times :

Re: Impossible to convert XY to Point

Brian_Wilson — Tue, 24 Oct 2023 15:57:23 GMT

Could a special character in the column name throw the SQL error??

I saw that degree marker in there. A cold chill ran down my spine. (Then I realized my windows is open.)

Try a file with safer column names.

Re: Impossible to convert XY to Point

Brian_Wilson — Tue, 24 Oct 2023 16:01:55 GMT

Warning about drivers, don't do what I did, I installed ODBC 17 without removing ODBC 18. Turns out you can have both installed at the same time and it will pick 18. In my experience 18 works just fine with Pro but not with ArcMap.

Re: Impossible to convert XY to Point

JasonBOCQUET — Wed, 25 Oct 2023 14:04:25 GMT

The error was very stupid.

The space between the name of some columns made the error. I removed the spaces and the "°" and it works fine

Thanks for your help !