none
Where should I start? RRS feed

  • Question

  • I want a performant way to import large Excel files into SQL Server.

    The format of the files varies, and every column in the file must be mapped to a database column, or it must be rejected with an error message.

    There are also a bunch of numbered columns at the end of each row of the file.  Any number of these can be missing, but their numbers must be contiguous.

    We want to run this import from a web or desktop application, so we want to implement it as a class library.

    Is SSIS the right solution to this problem?  What do I need to learn to implement such a solution?  I've never used SSIS, but I am a C#/SQL developer.

    The reason we're interested in SSIS is because we believe it would be the most performant way to import data into the database from Excel, but we're not entirely sure.

    Tuesday, December 3, 2019 4:57 PM

Answers

  • Hi M.S.Gregory,

    Please pay attention to single quotes and make sure your Excel file is closed when executing.

    SELECT *
     FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
       'Excel 12.0 Xml; HDR=NO;
        Database=c:\...\dada.xlsx',
       'select * from [Balance Import$]');
    

    Best Regards,

    Lily

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, December 5, 2019 1:50 AM

All replies

  • Try these articles:

    Import Data from Excel

    Using SSIS packages to import MS Excel data into a database

    HTH


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Tuesday, December 3, 2019 7:02 PM
    Tuesday, December 3, 2019 6:59 PM
  • Thanks for the reply.  Do you know if I can use SSIS to import an Excel file when I don't know exactly how many columns the sheet has?  Can I inject a script to analyze the file and adapt the way it is imported?


    Tuesday, December 3, 2019 8:54 PM
  • Hi M.S. Gregory,

    You can query MS Excel file, without knowing upfront its structure, as a virtual DB table on the file system.

    First, you test it in SSMS, and after that it is callable from SSIS Execute SQL Task.

    Check it out in SSMS:

    -- INSERT INTO tableName -- uncomment this line when you are ready
    SELECT *
     FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
       'Excel 12.0 Xml; HDR=NO;
        Database=c:\...\dada.xlsx',
        [Sheet1$]);
    Tuesday, December 3, 2019 9:32 PM
  • Hi Yitzhak,

    That doesn't work.  It tells me:

    Msg 7302, Level 16, State 1, Line 8
    Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    Tuesday, December 3, 2019 9:59 PM
  • This should solve the error:

    How to solve Microsoft.ACE.OLEDB.12.0 error

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    Tuesday, December 3, 2019 11:04 PM
  • Hi M.S.Gregory,

    To solve this error, you could run the following T-SQL code:

    EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
    EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

    And it's convenient to import Excel files into SQL Server by using SSIS Import and Export Data Wizard.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, December 4, 2019 2:09 AM
  • That doesn't work.  It tells me:

    Msg 7302, Level 16, State 1, Line 8
    Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    First, let's check what ACE providers are installed on your machine.

    Please execute the following query and share its outcome:

    -- 0. Check what 64-bit OLEDB providers are installed
    -- Microsoft.ACE.OLEDB.12.0 and
    -- Microsoft.ACE.OLEDB.15.0 0r 16.0
    EXEC master.sys.sp_MSset_oledb_prop;
    Wednesday, December 4, 2019 4:35 AM
  • I want a performant way to import large Excel files into SQL Server.

    The format of the files varies, and every column in the file must be mapped to a database column, or it must be rejected with an error message.

    You can use Script task and write a .NET code to load data. 

    http://www.techbrothersit.com/2016/03/how-to-load-data-from-excel-files-when.html

    Else, you can also use query given by Yitzak


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Wednesday, December 4, 2019 5:21 AM
  • It gives me this:

    provider_name	   | allow_in_process
    SQLOLEDB           | 0
    SSISOLEDB          | 0
    SQLNCLI11          | 1
    ADsDSOObject       | 1
    MSOLEDBSQL         | 1
    Search.CollatorDSO | 0
    MSDASQL            | 1
    MSOLAP             | 1
    MSDAOSP            | 0





    • Edited by M.S. Gregory Wednesday, December 4, 2019 8:44 PM
    Wednesday, December 4, 2019 3:32 PM
  • I can't use the wizard.  This must function as part of a larger application.
    Wednesday, December 4, 2019 3:35 PM
  • Hi M.S.Gregory,

    It means that you don't have any version (12, 15, or 16) of Microsoft ACE OLEDB provider 64-bit installed.

    Here is a few links to download. You need just one of them:

    • Microsoft Access Database Engine 2010 Redistributable Download (Microsoft.ACE.OLEDB.12.0)
      http://www.microsoft.com/en-us/download/details.aspx?id=13255
    • Microsoft Access 2013 Runtime (Microsoft.ACE.OLEDB.15.0)
      https://www.microsoft.com/en-us/download/details.aspx?id=39358
    • Microsoft Access Database Engine 2016 Redistributable (Microsoft.ACE.OLEDB.16.0)
      https://www.microsoft.com/en-us/download/details.aspx?id=54920

    Just as a reference, here is what I have on my machine:


    Wednesday, December 4, 2019 4:31 PM
  • Thank you!  That was it (once I replaced 32-bit Office with 64-bit).
    Wednesday, December 4, 2019 6:49 PM
  • Thank you.  That was helpful.
    Wednesday, December 4, 2019 6:51 PM
  • SELECT *
     FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
       'Excel 12.0 Xml; HDR=NO;
        Database=c:\...\dada.xlsx',
        [Sheet1$]);

    It seems to only work when the sheet is named "Sheet1".  Do you know how I can make it work when the sheet (the only sheet in the workbook) is named "Balance Import"?  This is an established standard in our organization.

    I've tried replacing [Sheet1$] with [Balance Import], [Balance Import$], [BalanceImport], [Balance%20Import] and such but it gives this error:

    Msg 7314, Level 16, State 1, Line 1
    The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not contain the table "Balance Import$". The table either does not exist or the current user does not have permissions on that table.
    

    It doesn't seem to like the space in the name.




    • Edited by M.S. Gregory Wednesday, December 4, 2019 8:42 PM
    Wednesday, December 4, 2019 8:29 PM
  • I found an answer elsewhere.

    The name needs to be surrounded with single quotes in the brackets (i.e. ['Balance Import$'])

    Wednesday, December 4, 2019 9:07 PM
  • Hi M.S.Gregory,

    When the sheet name contains a space, the following formatting should work: 

    SELECT *
     FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
       'Excel 12.0 Xml; HDR=NO;
        Database=e:\Temp\Formula_2.xlsx',
        ['Balance Import$']);

    Please don't forget to click "Mark as Answer" the response(s) that resolved your issue. This can be beneficial to other community members reading this thread.

    • Edited by Yitzhak Khabinsky Wednesday, December 4, 2019 9:30 PM
    • Proposed as answer by philfactor Wednesday, December 4, 2019 11:10 PM
    Wednesday, December 4, 2019 9:24 PM
  • Hi M.S.Gregory,

    Please pay attention to single quotes and make sure your Excel file is closed when executing.

    SELECT *
     FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
       'Excel 12.0 Xml; HDR=NO;
        Database=c:\...\dada.xlsx',
       'select * from [Balance Import$]');
    

    Best Regards,

    Lily

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, December 5, 2019 1:50 AM