.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Import Excel to SQL Server

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :Sql Server
Hi, I want to import spreadsheet from network computer to SQL Server 2008 R2 database, I want to each user can write to database easly their data from excel spreadsheet in real-time, and can refresh their spreadsheet with the new data.

Can explain me how to do this and send me code to try ?


View Complete Post

More Related Resource Links

Import Excel data into SQL Server express 2008

I have recently installed SQL express 08 and created a table to import excel data into.  Im running on a windows 7 64 bit OS i5 processor.  I've disabled the firewall (temporarily) and will set the necessary ports once this is working. I run the following query:  (which i found from multiple sources on the net) Select   * From OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Users\Public\lenovoBkup\contacts\outlookFiles\noib2a.xls', 'Select * From [sheet1$]' )  and receive the following message: Msg 7308, Level 16, State 1, Line 1                                                                                  OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.  (how did they know i was single and living in an apartment? lol) so after research of the following message i restarted the system and relaunched man

How to import Data from Excel to SQL Server Express

 Hi,  please any one is expert in Data Base help me, i would like to import two Data columns from Excel file 2003 instead of two columns in a table in Sql Server Express 2005 , How Can i do that? and what is the way if I have Sql Developer 2005, thank you .  

Import Excel File in SQL Server


Dear all,

I have to create one page where user will upload the excel file in SQl Server and system should check following things

1.Sheet Name should be always sheet1

2.System should check the column names

3.System should show the message that these many rows has been loaded



Import Excel-File (OPENROWSET) on x64 SQL Server


Hi, recently I encountered the following problem:

I tried to execute a stored procedure on the newly installed SQL 2005 Server (now on x64 Win Server 2003) which imports an Excel-File into a DB table.

We use OPENROWSET to access the Excel data. But I recognized this is dependent on Jet OLE DB which seems is not available for x64 windows.


Is there another way to import excel data using a stored procedure.


thank you in advance, rene

How to Import Excel Data into SQL Server 2008(64 bit) Using SQL Query (in Windows Server 2008 64bit

I am developing VB .Net application in Visual Studio 2008(64bit), SQL Server 2008(64bit) and Windows Server 2008(64bit) environment.

I need to import an Excel and Flat files data into SQL Server database using SQL Query. I tried a lot with the following SQL Queries directly with SQL Server Management Studio,

1. SELECT * INTO  dbo.ExcelImportTest FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\PTPLDCT\DCT Test Datasource\DCT_Excel_Test_Sample_1.xls',
'select * from [Sheet1$]')

While executing i got an error as below,

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

2. I have also tried with MSDASQL provider,

Select * from openrowset('MSDASQL','Driver={Microsoft Excel Driver (*.xls)};
Dbq=DCT_Excel_Test_Sample_1.xls;DefaultDir=D:\PTPLDCT\DCT Test Datasource\', 'Select * from [Sheet1$]')

Then i encountered the following error,

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

3. Finally i have tried the Query with Linked Server,

EXEC sp_addlinkedserver @server = '

Import Excel File to Sql Server on a 64 bit machine


I need to import Excel (xls - 2003-2007) data into a Sql Server 2008 database. Both the Excel file and the database live on a Windows Server 2003 R2 sp2 64 bit machine.  I’m fine with it running in 32 bit mode but it all needs to happen on the same server. Also, I would like it to run in T-Sql but as a fallback we can do this in SSIS.

My T-Sql code which runs fine on a 32 bit machine is as follows:

SELECT * Into #TempTable From OpenRowSet('Microsoft.Jet.Oledb.4.0','Excel 8.0;Database=\\Servername\Folder\filename.xls;HDR=NO;IMEX=1', 'select * from [Main$]')


On the 64 bit server:

Excel 2007 Spreadsheet - need to import it to SQL Server 2005


Trying to import spreadsheet from Excel 2007 to a table in SQL Server 2005, when I follow the steps for a linked server ,, I am able to create a linked server but because the instructions I am following are calling for a Microsoft 4.0 Jet something or another as "Provider" and I do not have that I am getting an error.. I have the a list of providers but the 4.0 Jet is not one of them...


Any help is greatly appreciated

import unstructured data from excel to SQL Server??


I need urgent help with  importing unstructured data from excel to SQL Server

where  columns of sql table are not in first row in the excel sheet

Excel sheet format is like the following

first name  on first row

last name is on second row  and second column B

date on third row and on column D

I will open connection to excel sheet and start copying data into sql Table

please help

Trying to import excel data into dbo schema using odbc connection with sql server


I have written this vb.net code to import an excel spread sheet but need for it to imported into the dbo shema. When I look in the database the table name is me.tablename instead of dbo.tablename.

    Public Sub Main()
        Dim sheetname As String
        excelpath = "S:\Account Management\jkormann\DynamicScoring\GE_Money_MN.xls"
        ServerName = "server"
        DBName = "sf"
        'SchemaName = "dbo"
        InsertedTableName = Dts.Variables("strTableName").Value.ToString
        sheetname = Dts.Variables("strSheet").Value.ToString
        'ImporttoServer(CStr(ExcelPath), CStr(ServerName), CStr(DBName), CStr(SchemaName), CStr(InsertedTableName))
        ImporttoServer(CStr(ExcelPath), CStr(ServerName), CStr(DBName), CStr(InsertedTableName), CStr(sheetname))

        Dts.TaskResult = Dts.Results.Success

Unable to import from Excel on 64-bit Win 7 workstation to a 32-bit Windows 2003 Server running SQL



I recently moved from Windows XP to Windows 7 on my primary workstation, and since then I'm unable to import from Excel to SQL from any of my SSIS scripts when I run them Locally.  The one instance I'm running into now is importing from Excel 2010 on my system (64-bit Windows 7) to SQL Server 2008 running on 32-bit Windows Server 2003. 

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

I even tried creating the import through SSMS and saving it as an SSIS script to get the same results.  The only thing I can think of is the change of architecture on my workstation.  I'm running 64-bit Windows 7 and the server is 32-bit Windows Server 2003 and SQL Server 2008.  Below is the Validation text SSIS returns:

Validation has started
[SSIS.Pipeline] Information: Validation phase is beginning.
Progress: Validating - 0 percent complete
[Source - Sheet1$ [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC00F9304.  There may be error messages posted before this with more

Import excel data to Sql Server 2005 problem




I have tried to import data from Excel 2003 to Sql Server 2005 using the Import Wizard to no avail.  Here is my issue:


Excel column has            After import, database table

these value sets:             has these corresponding value sets:

1.01                              null

1.02                              null

1.04                              1.04

1.05                              1.05

1.06                              null

"SQL Server Import and Export Wizard" (SQL 2005) Excel & ISAM


When I run the “SQL Server Import and Export Wizard” (SQL 2005) on my laptop, I attempt to import an EXCEL file to a table.


If I try both Excel-2007 and Excel-2010 format files, I get the following error.


     “The operation could not be completed” “Could not find installable ISAM. (Microsoft JET Database Engine)”


Consuming External Data Using SharePoint Server 2010 Business Connectivity Services and an Excel 201

Learn how to use BCS in SharePoint Server 2010 to access and update external data by using Microsoft Excel 2010 as a client.

Interacting with the Excel Web Services API for SharePoint Server 2007

Get a quick start with the Excel Web Services API, which enables interaction with published Excel 2007 workbooks in SharePoint Server 2007 from a remote application. Learn considerations around session state, security, and performance.

Publishing Excel 2007 Workbooks to SharePoint Server 2007 (Visual How To)

Watch the video and explore code as you learn how to publish Excel 2007 Workbooks to SharePoint Server 2007 programmatically.

Sample: Publishing Excel 2007 Workbooks to SharePoint Server 2007

Explore the code in this visual how-to article as you learn how to publish Excel 2007 Workbooks to SharePoint Server 2007 programmatically.

Linked Server to access Excel 2007



I'm tried SELECT * INTO XXX FROM OPENROWSET alongwith Microsoft.ACE.OLEDB.12.0.

Apparently the query requires the sql account to have SYSADMIN privileges.

Considering that SYSADMIN should not be provided to a database account on a Production Server, I tried using the Linked Server method.

Following is my code.

Exec sp_addlinkedserver 'AB2','Ace 12.0','Microsoft.ACE.OLEDB.12.0','\\\sagarr\Test1\cpc\c2\AB2.xlsx',NULL,'Excel 12.0;IMEX=1'
Exec sp_addlinkedsrvlogin 'AB2','false',NULL,NULL,NULL
Exec sp_dropserver 'AB2','droplogins'

Now i get the following error

Error.15247-User does not have permission to perform this action

My Excel file, Database and Windows Application run on separate machines.

i have provided the following privileges


EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'ad hoc distributed queries', 1

The DisAllowAdHocProcess in
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend