Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Express with Excel Expand / Collapse
Author
Message
Posted Tuesday, March 11, 2008 11:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Hi All,
Does anyone knows how to connect to Excel from SQL Express?

Thanks.
Post #467660
Posted Wednesday, March 12, 2008 10:09 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 10, 2012 9:49 AM
Points: 800, Visits: 1,759
Hi,
Read about linked servers or openrowset function. There are examples how to connect to excel.

Piotr


...and your only reply is slàinte mhath
Post #468241
Posted Thursday, March 27, 2008 9:02 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, February 26, 2009 3:02 PM
Points: 515, Visits: 655
This Microsoft Knowledge Base Article will give an overview of your options for working with Excel and SQL Server.

How to import data from Excel to SQL Server
http://support.microsoft.com/kb/321686

--

You will need to read up on the topics mentioned in SQL Server Books Online (BOL).

Topics:
SQL Server 2005 Books Online (September 2007)
Linking Servers
http://technet.microsoft.com/en-us/library/ms188279.aspx

--

SQL Server 2005 Books Online (September 2007)
sp_addlinkedserver (Transact-SQL)
http://technet.microsoft.com/en-us/library/ms190479.aspx


Example for Linked Server:
E. Using the Microsoft OLE DB Provider for Jet on an Excel spreadsheet
To create a linked server definition using the Microsoft OLE DB Provider for Jet to access an Excel spreadsheet, first create a named range in Excel by specifying the columns and rows of the Excel worksheet to select. The name of the range can then be referenced as a table name in a distributed query.

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\MyData\DistExcl.xls',
NULL,
'Excel 5.0'
GO

To access data from an Excel spreadsheet, associate a range of cells with a name. The following query can be used to access the specified named range SalesData as a table by using the linked server set up previously.

--

OPENROWSET
SQL Server 2005 Books Online (September 2007)
OPENROWSET (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms190312.aspx


Example for OPENROWSET:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\book1.xls', [Sheet1$])


Hope This Helps,




"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
Post #475491
Posted Friday, May 9, 2008 4:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 4, 2008 2:41 AM
Points: 1, Visits: 8
Hi,

I have SQL Server Management Studio Express in my machine.I want to connect an excel sheet,located in local drive.I have made a database on SQL and write a query to connect the excel:

SELECT *
INTO [dbo].[BG_test_Temp]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=F:\BG Format.xls;IMEX=1',
'SELECT * FROM [BG Registrer$]')

a new table is created on the newly created database,automatically creating the table structure and populating all the data from excel file.

Now problem is,

1. If i want to insert a new row to the excel file,or modify or update it the updated data doesnot reflect to the SQL table.I have to execute the above query again to view the updatation.

Is there any procedure the SQL table will automatically updated whenever i insert data in the excel file?
Post #497707
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse