如何将 Excel 文件导入 SQL Server?

2021-12-09 00:00:00 csv excel import sql-server oledb

我在 Excel 文件中有数据 - 实际上是

  • 下一个窗口是选择数据源".选择 Excel:

    • 在数据源"下拉列表中，选择 Microsoft Excel(如果您安装了 Excel，此选项应自动出现).

    • 单击浏览"按钮选择要导入的 Excel 文件的路径.

    • 选择 Excel 文件的版本 (

    1. 在选择目标"屏幕上，选择目标数据库:

    • 选择服务器名称"、身份验证(通常是您的 sql 用户名和密码)并选择一个数据库作为目标.点击下一步.

    1. 在指定表复制或查询"窗口中:

    • 为简单起见，只需选择从一个或多个表或视图复制数据"，点击下一步.

    1. '选择源表'从您的 Excel 文件中选择工作表并为每个工作表指定一个目标表.如果您还没有表格，向导将非常友好地创建一个新表格，该表格与您电子表格中的所有列相匹配.点击下一步.

    2. 点击完成.

    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:

    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.

    解决方案

    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 Server Management Studio (SSMS).

    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, and select Tasks, Import Data. An import wizard will pop up (you can usually just click Next on the first screen).

    3. 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.

    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.

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

    • For simplicity just select 'Copy data from one or more tables or views', click Next.

    1. '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.

    2. Click Finish.

    相关文章