How can I import an Excel file into SQL Server

Question

Having data in an Excel file, or since it is 2020, an XLSX file. I need to enter this data into SQL Server in the manner described below:

1. ad hoc, the use case being feeding tables with test data, or infrequent data loads of small amounts of data (say < 3k rows), and

2. In a repeatable, robust, and possibly automated way for a production system.

Can someone please help me with this? 

Answer 1

You can use OPENROWSET to import an 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$]')

I hope this helps you.