question

vidya avatar image
vidya asked

Error while importing data from Excel to SQL server 2000 table

Hi,

I am getting error when i am importing data from excel to SQL server table. Error is:

Error Source: Microsoft JET Database Engine. Description : External table is not in the expected format. (Microsoft JET Database Engine)

When i copy the data of excel file which is coming from Oracle database to a new excel file then it is working fine. But with same excel file giving error. I have checked the format of each column of the excel getting from Oracle.

sql-server-2000exceldtsdata-import
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
RickD avatar image
RickD answered

You have extra rows in the excel file. Run this with OPENROWSET():

SELECT *             
FROM            
OPENROWSET('Microsoft.Jet.OLEDB.4.0',            
'Excel 8.0;Database=<location of file and name>.xls',            
'SELECT * FROM [<worksheet>$]')

and you should see the extra rubbish in the workbook.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

megamanblue avatar image
megamanblue answered
Workaround assuming you are using DTS: Save the excel file as a CSV or other delimited text file and import with that instead. This can be done quite easily in Excel.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mhance avatar image
Mhance answered
A possible workaround is to try connecting the data from ORACLE to a Access database instead of an Excel file - then export out to SQL Server. Maybe you will be able to see what's causing your problem in the access database also.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.