I have data in an Excel file - actually xlsx format since it is now 2020. My requirement is to get this data into SQL Server as follows:
ad hoc, the use-case being feeding tables with test data, or infrequent data loads of small amounts of data (say < 3k rows), and
In a repeatable, robust, and possibly automated way for a production system.
If possible, those posting answers should give a thought to the security and config overheads of their proposals.
I implore readers to allow this apparently-vague question to remain open as I hope to collect suggestions (hopefully with links to examples) for all of the options and thus create a collated list.
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:
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.
On the 'Specify Table Copy or Query' window:
'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.
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$]')