How to import an Excel file into SQL Server?

What is the quickest way of getting my Excel file into a table in SQL Server?

  • How to enable bulk permission in SQL Server
  • Getting COUNT and SKIP TAKE in one operation with Linq to Entities
  • TSQL Date Conversion Failure - My Inability to understand result sets
  • How do I specify “close existing connections” in sql script
  • T-sql - determine if value is integer
  • Create a non-clustered index in Entity Framework Core
  • 2 Solutions collect form web for “How to import an Excel file into SQL Server?”

    You can also use OPENROWSET to import excel file in sql server.

    SELECT * INTO Your_Table FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                            'Excel 12.0;Database=C:\temp\MySpreadsheet.xlsx',
                            'SELECT * FROM [Data$]')
    

    There are many articles about writing code to import an excel file, but this is a manual/shortcut version:

    If you don’t need to import your Excel file programmatically using code you can do it very quickly using the menu in SQL Management Studio.

    The quickest way to get your Excel file into SQL is by using the import wizard:

    1. Open SSMS (Sql Server Management Studio) and connect to the database where you want to import your file into.
    2. Import Data: in SSMS in Object Explorer under ‘Databases’ right-click the destination database, select Tasks, Import Data. An import wizard will pop up (you can usually just click ‘Next’ on the first screen).

    enter image description here

    1. The next window is ‘Choose a Data Source‘, select Excel:

      • In the ‘Data Source’ dropdown list select Microsoft Excel (this option should appear automatically if you have excel installed).

      • Click the ‘Browse’ button to select the path to the Excel file you want to import.

      • Select the version of the excel file (97-2003 is usually fine for files with a .XLS extension, or use 2007 for newer files with a .XLSX extension)
      • Tick the ‘First Row has headers’ checkbox if your excel file contains headers.
      • Click next.

    enter image description here

    1. On the ‘Choose a Destination‘ screen, select destination database:
      • Select the ‘Server name’, Authentication (typically your sql username & password) and select a Database as destination. Click Next.

    enter image description here

    1. On the ‘Specify Table Copy or Query‘ window:

      • For simplicity just select ‘Copy data from one or more tables or views’, click Next.
    2. Select Source Tables:‘ choose the worksheet(s) from your Excel file and specify a destination table for each worksheet. If you don’t have a table yet the wizard will very kindly create a new table that matches all the columns from your spreadsheet. Click Next.

    enter image description here

    1. Click Finish.
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.