Service Desk Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

SqlError: [Microsoft][ODBC Excel Driver] The Microsoft Jet database ..ErrorNumber: 5005

SOLVED
Go to solution
Highlighted
Charles Egbon
Regular Collector

SqlError: [Microsoft][ODBC Excel Driver] The Microsoft Jet database ..ErrorNumber: 5005

Hi Guys,

I am trying to import data from excel to sd4.5 sp19. I followed the manual and everything seems to be going on fine but on reaching the Data Exchange Task, I tried to export the data and it terminates with this error:
SqlError: [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'DNSSDIMPORT'. Make sure the object exists and that you spell its name and the path name correctly.
ErrorNumber: 5005
All help will be appreciated.
Thanks.

Charles.
17 REPLIES
Raghunathan Tik
Frequent Visitor

Re: SqlError: [Microsoft][ODBC Excel Driver] The Microsoft Jet database ..ErrorNumber: 5005

Hi Charles,

I assume that the application name that you have mentioned in the ini file not matching with the class name that you have mentioned in the Mapping file. Try modifying the application name and or the class name.

raman
Charles Egbon
Regular Collector

Re: SqlError: [Microsoft][ODBC Excel Driver] The Microsoft Jet database ..ErrorNumber: 5005

I did the requested modification nad retry the process and I got a different error:
Section[SDIMPORT]: Does not contain a valid tablename
ErrorNumber: 2004
Below is the content of my sdimport.ini file:

[DSN]
NAME=DNSSDIMPORT
USR=
PWD=

[SYSTEM]
LOG=TRUE
XML=TRUE
LOG_FILE=\sdimport.log
XML_OUTPUT_FILE=\sdimport.xml
APPLICATION_NAME=SDIMPORT

[CLASSES]
NAME=SDIMPORT

[CLASSSDIMPORT]
SOURCE=DNSSDIMPORT
ATT=[LASTNAME],[FIRSTNAME],[JOBTITLE],[NAME],[SEARCHCODE]
COLUMNS=[LASTNAME],[FIRSTNAME],[JOBTITLE],[NAME],[SEARCHCODE]
LOADTABLE=TRUE

Also attached is the excel file.

More help help pls.

Charles.
Raghunathan Tik
Frequent Visitor

Re: SqlError: [Microsoft][ODBC Excel Driver] The Microsoft Jet database ..ErrorNumber: 5005

Hi Charles,

try the following:

[DSN]
NAME=DNSSDIMPORT
USR=
PWD=

[SYSTEM]
LOG=TRUE
XML=TRUE
LOG_FILE=\sdimport.log
XML_OUTPUT_FILE=\sdimport.xml
APPLICATION_NAME=SDIMPORT

[CLASSES]
NAME=SDIMPORT

[SDIMPORT]
SOURCE=DNSSSDIMPORT
ATT=[LASTNAME],[FIRSTNAME],[JOBTITLE],[NAME],[SEARCHCODE]
COLUMNS=[SDIMPORT].[LASTNAME] AS [LASTNAME],[SDIMPORT].[FIRSTNAME] AS [FIRSTNAME],[SDIMPORT].[JOBTITLE] AS [JOBTITLE],[SDIMPORT].[NAME] AS [NAME],[SDIMPORT].[SEARCHCODE] AS [SEARCHCODE]
LOADTABLE=TRUE



raman
Raghunathan Tik
Frequent Visitor

Re: SqlError: [Microsoft][ODBC Excel Driver] The Microsoft Jet database ..ErrorNumber: 5005

Hi Charles,

Also to check with you. Have you defined the name for the data selection made in the excel?

Raman
Charles Egbon
Regular Collector

Re: SqlError: [Microsoft][ODBC Excel Driver] The Microsoft Jet database ..ErrorNumber: 5005

Yes I have done that. Meanwhile here is the error and I am now getting:
SqlQuery : SELECT "SDIMPORT"."LASTNAME" AS "LASTNAME","SDIMPORT"."FIRSTNAME" AS "FIRSTNAME","SDIMPORT"."JOBTITLE" AS "JOBTITLE","SDIMPORT"."NAME" AS "NAME","SDIMPORT"."SEARCHCODE" AS "SEARCHCODE" FROM DNSSSDIMPORT
Section[SDIMPORT]: SqlError: [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'DNSSSDIMPORT'. Make sure the object exists and that you spell its name and the path name correctly.
ErrorNumber: 5005

Pls find in the attachment screenshot of the import mapping.

More help pls.

Charles
Raghunathan Tik
Frequent Visitor

Re: SqlError: [Microsoft][ODBC Excel Driver] The Microsoft Jet database ..ErrorNumber: 5005

Charles,

Rename the Item mapping Name in the import mapping from "CLASSSDIMMPORT" to "SDIMPORT"

Raman
Charles Egbon
Regular Collector

Re: SqlError: [Microsoft][ODBC Excel Driver] The Microsoft Jet database ..ErrorNumber: 5005

Hey, I have done that and the error repeat. Looking at '..The Microsoft Jet database engine could not find the object 'DNSSDIMPORT'.. It seems that I need to specify this name else where. Though that was Data Source Name from ODBC driver. Find screenshot in the attach.
The problem seems to be arround the ODBC connection.
What possibly I have done wrong?

Charles.
Charles Egbon
Regular Collector

Re: SqlError: [Microsoft][ODBC Excel Driver] The Microsoft Jet database ..ErrorNumber: 5005

Hey, I have done that and the error repeat. Looking at '..The Microsoft Jet database engine could not find the object 'DNSSDIMPORT'.. It seems that I need to specify this name else where. Though that was Data Source Name from ODBC driver. Find screenshot in the attach.
The problem seems to be arround the ODBC connection.
What possibly I have done wrong?

Charles.
Raghunathan Tik
Frequent Visitor

Re: SqlError: [Microsoft][ODBC Excel Driver] The Microsoft Jet database ..ErrorNumber: 5005

Charles,

Currently i am away from office and do not have the confi file that I used to import data. Tomorrow I shall share with you the sample ini and the mapping file that I used.

raman
Charles Egbon
Regular Collector

Re: SqlError: [Microsoft][ODBC Excel Driver] The Microsoft Jet database ..ErrorNumber: 5005

Ok my brother, I am expectant.
Thanks

Charles.
Gerry Allardice
Honored Contributor
Solution

Re: SqlError: [Microsoft][ODBC Excel Driver] The Microsoft Jet database ..ErrorNumber: 5005

You need to name the spreadsheet with DNSSDIMPORT.
Click the square button that is at the intersection to the column letters and the row numbers.
The whole sheet becomes selected. The box above this button shows A1 as the cell. Over type this with DNSSDIMPORT and hit enter.
The spread sheet then has a name, equivalent to the TABLE for other ODBC sources.

Attached file has been updated.
Raghunathan Tik
Frequent Visitor

Re: SqlError: [Microsoft][ODBC Excel Driver] The Microsoft Jet database ..ErrorNumber: 5005

Hi Charles,

I have attached the sample ini as well as data mapping that i used in my environment. Also following are the steps that i would ask you to check once. i am sure that you have done most or all of the above tasks, still this is just to refresh once.

1) Select the entire data that is availble in the Excel (along with the column names), goto Insert --> Name --> Define and type in the name of the source (this is the name that you are going to use in the INI file)
2) create ODBC Source configuration against chosen excel file
3) edit the ini file wherever appropriate
4) create data exchange mapping
5) now go ahead and import /export the data


all the best.

raman
Charles Egbon
Regular Collector

Re: SqlError: [Microsoft][ODBC Excel Driver] The Microsoft Jet database ..ErrorNumber: 5005

Thanks alot Gerry & Raman. Gerry got the exact magic wand. The problem is solved now. I simply name the excel spreadsheet with his recommended format and that solved the problem.

Charles.
Raghunathan Tik
Frequent Visitor

Re: SqlError: [Microsoft][ODBC Excel Driver] The Microsoft Jet database ..ErrorNumber: 5005

hi Charles,

excellent..... how about some points if you would like to rate on either solution

thanks

raman
Charles Egbon
Regular Collector

Re: SqlError: [Microsoft][ODBC Excel Driver] The Microsoft Jet database ..ErrorNumber: 5005

Oops I have already isse the points. Please be patient it will soon reflect on your system. Meanwhile thanks a lot. I appreciate the support.

God bless you.

Charles.
Charles Egbon
Regular Collector

Re: SqlError: [Microsoft][ODBC Excel Driver] The Microsoft Jet database ..ErrorNumber: 5005

Oops I have already issue the points. Please be patient it will soon reflect on your system. Meanwhile thanks a lot. I appreciate the support.

God bless you.

Charles.
Charles Egbon
Regular Collector

Re: SqlError: [Microsoft][ODBC Excel Driver] The Microsoft Jet database ..ErrorNumber: 5005

Thanks alot Gerry & Raman. Gerry got the exact magic wand. The problem is solved now. I simply name the excel spreadsheet with his recommended format and that solved the problem.

Charles.
//Add this to "OnDomLoad" event