ODBC import error

[Werner Osterkamp]

Dear All,

I try to import some data from an external data source via odbc.The source includes various tables. Most of them can be imported without any problems (I use the corresponding code below). One table, however, which is very important to me does not realy work. When I try this code:

odbc load, table("data.odbc_loan_real") noq

the following error occurs:

The ODBC driver reported the following diagnostics
ERROR:invalid input syntax for Typ double precision: �11157,2943�;
Error while executing the query
SQLSTATE=22P02
r(682);

Unfortunately, I have no possibility to change any options in the data source. Does anyone have any idea what I could change in Stata? I would be very happy about that.

Thank you very much!
Werner

[William Lisowski]

Welcome to Statalist, Werner.

My guess is that the ODBC driver may be trying to convert a string to a double precision number, and is not recognizing the comma as the decimal marker.

A workaround might be to add the allstring option to your odbc load command and if that succeeds, then use the destring command to convert variables to numeric as appropriate.

Note: I'm not an expert on any of this; it's at best an "educated guess" - but it at least offers something to try to see if it works around your problem.

[Werner Osterkamp]

Dear William,

thank you for your advice. Unfortunately, adding the options "allstring" or/and "datestring" to my code does not change the error. I guess I need to force Stata to accept the comma.

[William Lisowski]

For the record, destring is not an option on the odbc load command, it is a separate command as described by help destring. And to be as clear as I can, my recommendation was to replace your odbc load command with

odbc load, table("data.odbc_loan_real") noq allstring

I will add that I am unfamiliar with the noquote option on odbc load and am assuming that you know you need it for your purpose; it is documented as a rarely-used option. It seems unlikely that it would cause the error you are experiencing.

Assuming that is the odbc load command you tried, and it failed with the same error message that you reported in post #1, your options seem limited. Since your problem apparently originates in the ODBC driver, it seems unlikely that you will be able to overcome it in Stata. One laborious possibility is first adding the sqlshow option to the odbc load command to tell you what SQL is being submitted to ODBC. Then you could work at creating and testing different SQL by feeding in your own SQL select statement using the exec() option. All these options are described in help odbc.

Another option would be to see if you can load the data into Excel using ODBC. If that succeeds, you could then use Stata's import excel command to import the data from Excel into Stata. And if it fails, your problem lies with whomever set up the data source whose options you are unable to change.

[Joseph Coveney]

Just to amplify William's good advice, you could find what the data storage type is for the troublesome column by using odbc describe and compare what datatype it is against what Stata reports with the sqlshow. Then you can devise your own SQL query using the proper data type and use the exec() option as William recommends.

[Werner Osterkamp]

Yes, I tried that code. The noquote option is essential for accessing that ODBC driver.

I will try my own SQL query now. If that does not work out, I will try to contact the data owner.

Thank you very much!