Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Express with Excel


SQL Express with Excel

Author
Message
ramadesai108
ramadesai108
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 690
Hi All,
Does anyone knows how to connect to Excel from SQL Express?

Thanks.
Piotr.Rodak
Piotr.Rodak
SSC Eights!
SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)

Group: General Forum Members
Points: 802 Visits: 1761
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
Key DBA
Key DBA
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 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
pal.somraj
pal.somraj
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search