Stored Procedure and Excel

  • 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,....

    😀

  • 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

  • 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$]')

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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