Import an Excel spreadsheet into SQL

Question

How do I import an Excel spreadsheet into SQL STudio

Answer 1

You right-click the database and select Tasks from the context menu. In the Tasks menu, select Import Data.

As Yithzak says, you need to have the ACE OLEDB provider installed, see https://www.microsoft.com/en-us/download/details.aspx?id=54920 (It is out of support. I am not up to date what is the replacement.)

You also need to have the support for SSIS installed. This is an optional component for SSMS. Run the Visual Studio Installer (find it in the Start Menu) and select Modify for SSMS. Go the tab Individual compoents and add SQL Server Integration Services.

Answer 2

Hi @Ian Jones,

Your question is marked for SSIS.

SSIS has Excel Source Adapter for such tasks. That adapter is using Microsoft ACE OLEDB Provider behind the scenes.

Answer 3

Use SQL Server Management Studio and start the SQL Server Import and Export Wizard:

1. In SSMS, connect to the SQL Server Database Engine.
2. Expand Databases.
3. Right-click the target database.
4. Select Tasks > Import Data.
5. In the wizard, choose Microsoft Excel as the data source.
6. Enter the Excel file path or browse to the workbook.
7. Select the correct Excel version.
8. Set First row has column names if the sheet header row contains column names.
9. Continue through the wizard to import the worksheet data into SQL Server.

Important details:

• The wizard can’t open a password-protected Excel file.
• If Excel does not appear in the data source list, the issue is often 32-bit vs 64-bit wizard/provider visibility. The Excel and Access providers are typically 32-bit and may not appear in the 64-bit wizard. Running the 32-bit wizard can resolve that.
• If importing multiple worksheets, the import typically has to be run once for each sheet.
• For direct Excel import, the documented methods apply to SQL Server on-premises only. For Azure SQL Database, export the Excel sheet to CSV or text first, then import that file.

Alternative methods:

• Save the Excel sheet as CSV or tab-delimited text, then import it with:
  • Import Flat File Wizard
  • BULK INSERT
  • bcp
  • Azure Data Factory
• If building repeatable ETL, create an SSIS package with Excel Source and SQL Server Destination.

---

• Import data from Excel to SQL Server or Azure SQL Database
• Import data from Excel to SQL Server or Azure SQL Database
• Connect to an Excel Data Source (SQL Server Import and Export Wizard)
• Import data from Excel to SQL Server or Azure SQL Database