Importing Excel 2010 data to SQL2008 Express

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Importing Excel 2010 data to SQL2008 Express

Author

Topic

kkrishna
Starting Member

23 Posts

Posted - 2010-11-07 : 07:39:42

I installed SQL Server 2008 Express on my computer and I am trying to import data from an Excel 2010 file. I created an empty table in the database and I am using the following code:

INSERT INTO dbo.tblPlantMetrics
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Extended Properties=Excel 14.0 Xml;HDR=YES;Database=C:\Users\krishna\Documents\TblPerformanceData_2010_FromAccess.xlsx',
'SELECT * FROM [CopyTblPerformanceData_Corr$]');

I get the following error.

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

I did some search on the internet and most of the discussions are with JET OLEDB connections. From the search, I found that quite a few have encountered this problem and I am hopeful that someone has found an easy solution for the same.

I am not an expert on T-SQL and I am putting in efforts to understand the various useful functions of SQL server. I can try to use the 'Import and Export Data'feature, but I want to use the T-SQL code in the query.

Can someone suggest what could be the fix for the above error?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-07 : 10:00:17

if it's a one time shot, I find it easiest to save the spreadsheet as a tab delimited text file, then bulk insert it.

BULK INSERT database..table FROM 'pathToTextFile'
WITH (
DATAFILETYPE = 'char',
   FIELDTERMINATOR = '\t',
   ROWTERMINATOR = '\n'
);
GO

Sachin.Nand

2937 Posts

Posted - 2010-11-07 : 10:16:18

Try the solution russell suggested or create a linked server & use openquery to import the data.

PBUH

kkrishna
Starting Member

23 Posts

Posted - 2010-11-20 : 22:30:12

I am sorry for the delayed response.
Thanks, this worked. I have to remove the column headings and format the text file so that it does not carry the blank rows from the end of the Excel worksheet.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-20 : 23:02:24

Glad to hear it worked out for you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources