Cannot Import data from Excel Through ODBC with Data Connection Wi

[Exotic Hadron]

Gentlemen,

I created System DSN using the Microsoft Excel Driver version
12.00.4518.1014. The workbook that I used a source is a Microsoft Excel
97-2003 XLS workbook. It has a table with 3 columns and 2 rows inside. This
table is created on a user Sheet (not the system sheets 1-3).
The database version I selected is 97-2000. The driver is set to scan 8 rows
with read-only checked off. The data source is given the name UserDB.

1. Now I check if I can import this data into Excel. I fire up Excel 2007,
select Data|From Other Sources|From Data Connection Wizard
On the Welcome page of the Data Connection Wizard I select ODBC DNS and
click Next. On the Connect to ODBC Data Source I select the UserDB data
source and click Next. And…. On the Select Database and Table wizard page I
see nothing in the Connect to a specific table list! It's empty. Okay, I
check the Connect to a specific table list checkbox off and click Finish. And
I get the This data source contains no visible tables message! So after
finishing with data import I get nothing imported!

2. So what I do next.
In Excel, I select Data|Existing Connections. In the Existing Connections
dialog box I click Browse for More, choose the XLS database file used as the
data source and receive the Select Table dialog box. I see there the same
list as I see on the Select Database and Table wizard page but now it's
filled with data. I click OK and get the Import Data dialog box. I click
Properties and get the Connection Properties dialog box opened to me. In the
Connection Properties dialog box I switch to the Definition tab and click the
Export Connection File button. I save the file as UserDB.odc. No I close the
wizard, open the %userprofile%\my documents\my data sources folder with
Windows Explorer and locate the saved UserDB.odc file. I double-click it and
open it with Excel. I answer Enable on the Microsoft Office Excel Security
Notice dialog box and voila! I get the data imported into my new Excel
workbook!!!

So the questions are:

1. How do I import the same data with Data Connection Wizard in bullet 1???
The thing is I need to import the data with another program that (I believe)
does really that what the Data Connection Wizard does. Since the Data
Connection Wizard sees no tables and data inside, I think the program gets
the same result as I cannot make it work.
2. What's the difference between what' s done in bullet 1 when I used Data
Connection Wizard and bullet 2 when I imported the connection file and
imported the data using this file?

Thank you in advance. Hoping for your help as I do really need it.

Yours,
Stanislav.

 

[netrista]

Hello Stanislav,

I commonly perform the ODBC Import ask as you describe in #1. I do not see
any problems with the series of steps that you take. I would suggest that you
do the following:

1. Verify that you can use the System DSN to query the table outside of
Excel. I would recommend that you test with both a Microsoft program such as
Access and a non-Microsoft program. This will enable you to determine the
scope of the visibility problem.

2. I suggest that implement clientside ODBC tracing an look at the
SQLTables() calls to see what is being returned.

A. Close the Excel client application.
B. Open your ODBC Data Sources utility.
C. Enable tracing on the tracing tab.
D. Reopen Excel.
E. Recreate the error condition.
F. DISABLE TRACING ON THE TRACING TAB.
G. Locate the file indicated in the log file path on the tracing tab.
H. Review.


In my experience,I have seen this type of problem when bugs or compatibility
issues are present in the ODBC drivers.

Best regards,
Netrista Khatam
Technical Services Manager
OpenLink Product Support