locked
excel 2007 import into sql server express 2008 r2 RRS feed

  • Question

  • Hello. I am trying to get info from an excel 2007 spreadsheet into a table in sql server express 2008 r2. The import method from sql server does not fulfull the import and fails part way thru. I have checked the info for mapping, names, etc and after about 6 tries I give up on the import. I am not fluent in sql. I then tried using a t-sql statement that shows below (trimmed down the insert into). When I run it I get the error below. The spreadsheet and sql server are both on my machine. Is there something different to use?

    Thanks...John

    Error is: 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.

    Insert into tblRequest
    ([RequestTypeID],
    [EDMSCategoryID],
    [RequestStatusID],
    [ApprovalStatusID],
    [Requester],
    [TentativeCompletionDate],
    [DevelopmentCompletionDate],
    [ReleaseDate],
    [RequestModifiedBy])

    select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=C:\MY DOCUMENTS\tblChangeRequest.xls;HDR=YES',
    'SELECT * FROM [tblChangeRequest$]')

     

     

    Friday, July 29, 2011 10:44 PM

Answers

All replies