August 1, 2008 at 3:57 am
I need to know how to make a stored procedure that can read excel file and then load the data of the excel to a table..
Thanks, I am only just a newbie,....
😀
August 1, 2008 at 5:01 pm
There are a few options, but using a SP may not be the best for you. Take a look at this article: http://support.microsoft.com/kb/321686. If the Excel file is always the same one and it needs to be updated, it may be best to set up a dataconnection in Excel where the data flows back and forth dynamically. If it's only going one way (to MSSQL) but the file is always the same and it needs to be triggered from the SQL Server, a linked server or distributed query might work well. If you need to sync with several Excel files, give SSIS a look.
Hope this helps!
Chad
December 2, 2009 at 5:52 am
Will this work for you?
select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
December 2, 2009 at 11:27 pm
before running this query you need to configure with the following query
sp_configure 'show advanced options', 1
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
--sushil
December 3, 2009 at 8:06 am
before running this query you need to configure with the following query
sp_configure 'show advanced options', 1
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
Can you provide an explanation for running the procedures you mention.
I find it beneficial to understand why there is a need to do so.
Thanks!
EDITED for clarity
"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
December 3, 2009 at 9:19 am
To keep things really simple, I would create a simple SSIS package. That way I could schedule it to fire when needed, or even have it fire manually from within SSMS, or even from a web page.
No need to get too deep with it, that is exactly what ETL, and SSIS are designed to do.
Andrew SQLDBA
August 7, 2010 at 10:29 pm
Hi.
Jumping in because I have a similar requirement. I need to import data from an Excel spreadsheet. It will always have the same name and path. The data needs to be brought into SQL Server 2005. But the import gets triggered from a command button on a form in Access 2007 project (adp linked to SQL Server).
I don't know anything about linked servers, and prefer to avoid it, since this program will be deployed to various clients, and I have no control over their settings.
My approach is to write vba code that will run the stored procedure (with connection, not docmd.....).
My questions are:
Is the syntax for Excel (5.0, 8.0, 11.0 whatever) truly version specific? e.g. if the spreadsheet is created in Excel 2007 and saved as an earlier version, will this still work?
At the end of the stored proc, I'd like to run sp_configure again and change the settings back to the default (where adHoc is not allowed). Do I just change the 1 to 0 on those lines?
Thanks so much for the info on this, I've been hunting for two hours for this info.
Molly
August 15, 2010 at 11:31 pm
Hi Molly,
Search over at http://www.ozgrid.com/forum for ado (ActiveX Data Objects) by XLDennis (Dennis Wallentin)
I believe all of his code samples are written to access Jet 4 database, but you should be able to easily change that for SQL
Post back if you need help
Best,
Winston
August 27, 2010 at 7:21 am
I often use something along the lines of this to read in Excel data. The IMEX=1 trick will save you a lot of grief from SQL incorrectly "guessing" what type of data to read in from Excel.
/*
-- NOTES
--N.B. "IMEX=1" in connection string for OPENROWSET prevents this problem: If 1st row in XL file is blank, then ALL rows will have NULL.
*/
USE dev;
GO
SELECT *
FROM OpenRowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\myserver\SQLServerFiles\XL-Import-Test.xls',
'select * from ReportData'-- Where ReportData is a Named Range in XL
) -- will be called F1, F2, F3, ... if there are no headers.
--If it's just a sheet, try
SELECT *
FROM OpenRowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\myserver\SQLServerFiles\XL-Import-Test.xls',
'select * from [Sheet1$]'
)
Per BOL: "OPENROWSET can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled. When these options are not set, the default behavior does not allow for ad hoc access."
You can turn this on with the Surface Area Configuration Manager or with the sp_configure command, per ssagrawal's post above.
This should get you started,
Rich
August 29, 2010 at 12:11 pm
Hey, all.
Just back from holidays and catching up.
Winston, I've added the site to my resources list, I can just see surfing for additional tips.
And Rich, great code. I've copied it locally, and it sure came in handy.
Looks like we have a working solution, so thanks for all the help.
Cheers,
M
August 29, 2010 at 7:59 pm
You're welcome! Nice to know it helped, and good to hear back that I wasn't typing into the ether!
This is an incredibly useful site, full of knowledgeable articles, thoughtful debate, and tireless contributions from people who, frankly, should be glad they don't work for me, given the amount of non-company time they spend contributing to the success of blokes like me! 😉
Yours,
Rich
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply