如何将数据从Excel文件导入SQL Server数据库

There are many ways to import data from an Excel file to a SQL Server database using:

有多种方法可以使用以下方法将数据从Excel文件导入SQL Server数据库:

In this article, steps for importing data from an Excel file to a SQL Server database will be explained using the SQL Server Import and Export Wizard including some of problems that can occur during the processes.

在本文中,将使用SQL Server导入和导出向导解释将数据从Excel文件导入SQL Server数据库的步骤,其中包括在处理过程中可能发生的一些问题。

To start the process of importing data from an Excel file to a SQL Server database using the SQL Server Import and Export Wizard the SQL Server Import and Export Wizard needs to be launched. There are several ways to do that, and this can be seen on the How to import/export data to SQL Server using the SQL Server Import and Export Wizard page

要开始使用SQL Server导入和导出向导将数据从Excel文件导入SQL Server数据库的过程,需要启动SQL Server导入和导出向导。 有几种方法可以执行此操作,这可以在如何使用“ SQL Server导入和导出向导”页面向SQL Server导入/导出数据中看到。

The first page that appears when the SQL Server Import and Export Wizard launched is Welcome page:

启动SQL Server导入和导出向导时出现的第一页是欢迎页面:

On this page, only a quick introduction of the SQL Server Import and Export Wizard is shown.

在此页面上,仅显示SQL Server导入和导出向导的快速介绍。

Click the Next button to continue. The next page in the SQL Server Import and Export Wizard is Choose a Data Source page:

单击下一步按钮继续。 SQL Server导入和导出向导的下一页是“选择数据源”页面:

In the Choose a Data Source page, in order to continue with importing data from Excel to SQL Server the data source provider and way of connecting with data source must be provided. In our case, the provider for connecting to the Excel file is the Microsoft Excel provider.

在“ 选择数据源”页面中,为了继续将数据从Excel导入SQL Server,必须提供数据源提供程序和与数据源的连接方式。 在我们的情况下,用于连接到Excel文件的提供程序是Microsoft Excel提供程序。

From the Data source drop down box, select the Microsoft Excel provider:

数据源下拉框中,选择Microsoft Excel提供程序:

As you can see, there is no Microsoft Excel provider in the list of the Data source drop down box. There are several reasons for this. The first reason could be that Microsoft Office isn’t installed. But there is no need to install Microsoft Office (Microsoft Excel) in order to see the Microsoft Excel provider in the Data source drop down box list.

如您所见,数据源下拉框列表中没有Microsoft Excel提供程序。 有几个原因。 第一个原因可能是未安装Microsoft Office 。 但是无需安装Microsoft Office(Microsoft Excel)即可在“数据源”下拉框列表中查看Microsoft Excel提供程序。

To see the Microsoft Excel provider in the list, install Microsoft Access Database Engine 2016 Redistributable. The latest version of Microsoft Access Database Engine can open the earlier version of Excel, so make sure that you have the latest one.

若要在列表中查看Microsoft Excel提供程序,请安装Microsoft Access数据库引擎2016 Redistributable 。 最新版本的Microsoft Access数据库引擎可以打开早期版本的Excel,因此请确保您拥有最新版本的Excel。

The Microsoft Access Database Engine 2016 Redistributable comes with two versions:

Microsoft Access数据库引擎2016 Redistributable具有两个版本:

  • AccessDatabaseEngine.exe is 32-bit version

    AccessDatabaseEngine.exe是32位版本
  • AccessDatabaseEngine_X64.exe is 64-bit version

    AccessDatabaseEngine_X64.exe是64位版本

Now, when Microsoft Access Database Engine 2016 Redistributable is installed, we should see the Microsoft Excel provider in the list, but unfortunately the Microsoft Excel provider does not show in the list of the Data source drop down box.

现在,在安装Microsoft Access Database Engine 2016可再发行组件后 ,我们应该在列表中看到Microsoft Excel提供程序,但是不幸的是,Microsoft Excel提供程序没有显示在“数据源”下拉框的列表中。

This is because you may run a wrong version of the SQL Server Import and Export Wizard. For example, the AccessDatabaseEngine.exe is installed and the SQL Server Import and Export Wizard 64-bit version is launched. In this case, in order to see the Microsoft Excel provider in the list of the Data source drop down box, launch the SQL Server Import and Export Wizard 32-bit version and the Microsoft Excel provider will appear in the list:

这是因为您可能运行了错误版本SQL Server导入和导出向导。 例如,安装AccessDatabaseEngine.exe并启动SQL Server导入和导出向导64位版本。 在这种情况下,为了在数据源下拉列表的列表中看到Microsoft Excel提供程序,请启动SQL Server导入和导出向导32位版本,并且Microsoft Excel提供程序将出现在列表中:

Now, when all is installed, from the list, select the Microsoft Excel provider. On the Choose a Data Source page, additional options appear:

现在,在全部安装后,从列表中选择Microsoft Excel提供程序。 在“ 选择数据源”页面上,出现其他选项:

In the Excel file path box, type the location to the Excel file or use the Browse button to navigate to the location:

在“ Excel文件路径”框中,键入Excel文件的位置,或使用“ 浏览”按钮导航到该位置:

From the Excel version drop down box, choose the version of Excel that uses the source workbook. In our case, that is the Microsoft Excel 2016 version:

从“ Excel版本”下拉框中,选择使用源工作簿的Excel版本。 在我们的案例中,这是Microsoft Excel 2016版本:

The latest option on this page is the First row has column names check box:

此页面上的最新选项是“ 第一行具有列名”复选框:

By default, this option is checked. It treads the first rows of the data source as the column names:

默认情况下,此选项处于选中状态。 它将数据源的第一行作为列名称:

But if this option is enabled and data source does not contain column names, the SQL Server Import and Export Wizard will add the column names, starting with the name F1 for the first column name:

但是,如果启用此选项,并且数据源不包含列名,则SQL Server导入和导出向导将添加列名,第一个列名以名称F1开始:

If this option is disabled and data source contain the column names the SQL Server Import and Export Wizard treats these columns as the first row of data:

如果禁用此选项,并且数据源包含列名,则SQL Server导入和导出向导会将这些列视为数据的第一行:

Now, when everything is set on the Choose a Data Source page of the SQL Server Import and Export Wizard, click the Next button to continue.

现在,在“ SQL Server导入和导出向导”的“选择数据源”页面上完成所有设置后,单击“ 下一步”按钮继续。

The following warning message may appear after clicking the Next button:

单击下一步按钮后,可能会出现以下警告消息:

The operation could not be completed.

操作无法完成。

Additional information:

附加信息:

The ‘Microsoft.ACE.OLEDB.16.0’ provider is not registered on the local machine. (System.Data)

“ Microsoft.ACE.OLEDB.16.0”提供程序未在本地计算机上注册。 (系统数据)

Typically, this warning message appears when the SQL Server Import and Export Wizard is launched via SQL Server Management Studio (SSMS) which is a 32-bit application and the 32-bit version of the SQL Server Import and Export Wizard is launched, but you have installed the Microsoft Access Database Engine 2016 Redistributable 64 bit version.

通常,当SQL Server导入和导出向导通过启动出现此警告消息SQL Server Management Studio中(SSMS),这是一个32位应用程序和的32位版本的SQL Server导入和导出向导启动,但你已安装Microsoft Access数据库引擎2016可再发行的 64位版本。

There are two solutions for resolving this problem:

有两种解决此问题的方法:

Note: The Microsoft Access Database Engine 2016 Redistributable can be installed in quiet mode.

注意 :可以以安静模式安装Microsoft Access Database Engine 2016可再发行组件。

Open the Command Prompt window and run the following:

打开命令提示符窗口,然后运行以下命令

For 32-bit version

对于32位版本

C:\Users\<User_Name>\Downloads\AccessDatabaseEngine.exe /quiet

C:\ Users \ <用户名> \ Downloads \ AccessDatabaseEngine.exe / quiet

For 64-bit version

对于64位版本

C:\Users\<User_Name>\Downloads\AccessDatabaseEngine_X64.exe /quiet

C:\ Users \ <用户名> \ Downloads \ AccessDatabaseEngine_X64.exe / quiet

The next page on the SQL Server Import and Export Wizard is the Choose a Destination page:

SQL Server导入和导出向导的下一页是“ 选择目标”页:

On this page, determine the destination where data from the data source (Excel file) will be placed. In our case, the destination will be a SQL Server database.

在此页面上,确定将放置数据源(Excel文件)中的数据的目的地。 在我们的例子中,目标将是SQL Server数据库。

Under the Destination drop down box, choose a provider that can connect to a SQL Server database.

在“ 目标”下拉框中,选择可以连接到SQL Server数据库的提供程序。

One of providers that can connect to SQL Server is:

可以连接到SQL Server的提供程序之一是:

In this case, the SQL Server Native Client 11.0 will be selected from the Destination list:

在这种情况下,将从“ 目标”列表中选择“ SQL Server Native Client 11.0

From the Server name combo box, select the SQL Server instance:

服务器名称组合框中,选择SQL Server实例:

In the Authentication section, determine how it will be connected to SQL Server by using Windows or SQL Server Authentication mode.

在“ 身份验证”部分中,确定如何使用Windows或SQL Server身份验证模式将其连接到SQL Server。

From the Database drop down box, pick a database in which data from data source (Excel file) will be placed:

在“ 数据库”下拉框中,选择一个数据库,其中将放置来自数据源(Excel文件)的数据:

Or, create a new database as a destination for data from data source.

或者,创建一个新数据库作为数据源中数据的目标。

To do that, click the New button and in the Create Database dialog, set the parameters for the new SQL Server destination database:

为此,请单击“ 新建”按钮,然后在“ 创建数据库”对话框中,为新SQL Server目标数据库设置参数:

When everything is set on the Choose a Destination page, click the Next button to proceed.

在“ 选择目标”页面上设置完所有内容后,单击“ 下一步”按钮继续。

On the Specify Table Copy or Query page, determine how data from the data source will be copied to the destination:

在“ 指定表副本或查询”页面上,确定如何将数据源中的数据复制到目标:

If the Copy data from one or more tables or views radio button is selected then all data from the chosen worksheets will be copied.

如果选中“从一个或多个表或视图复制数据”单选按钮,则将复制所选工作表中的所有数据。

If the Write a query to specify the data to transfer is chosen, then only data that are specified in a SQL query will be copied from an Excel worksheet to a destination SQL Server database.

如果选择了“ 编写查询以指定要传输的数据” ,则仅将SQL查询中指定的数据从Excel工作表复制到目标SQL Server数据库。

If the Write a query to specify the data to transfer in the Choose a Destination page is chosen, then the Provide a Source Query page will be shown when the Next button is pressed:

如果在“ 选择目标”页面中选择了“ 编写查询以指定要传输的数据 ,那么在按下“ 下一步”按钮时将显示“ 提供源查询”页面:

In the SQL statement text box, type the query that will select the data to copy form the Excel file to the SQL Server database or load a query using the Browse button.

在“ SQL语句”文本框中,键入查询,该查询将选择要从Excel文件复制到SQL Server数据库的数据,或使用“ 浏览”按钮加载查询。

To successfully query a worksheet the $ to the end of the sheet name and the brackets around sheet name, ([BusinessEntity$]) must be added, otherwise the following warning messages may appear:

为了成功地查询工作表中$到片材名称的末尾和周围表名称的括号,必须添加([$的BusinessEntity]),否则以下警告消息可能出现:

The statement could not be parsed.

该语句无法解析。

Additional information:

附加信息:

The Microsoft Access database engine could not find the object ‘BusinessEntity’. Make sure the object exists and that you spell its name and the path name correctly. If ‘BusinessEntity’ is not a local object, check your network connection or contact the server administrator. (Microsoft Access Database Engine)

Microsoft Access数据库引擎找不到对象“ BusinessEntity”。 确保对象存在,并且正确拼写了它的名称和路径名。 如果“ BusinessEntity”不是本地对象,请检查您的网络连接或与服务器管理员联系。 (Microsoft Access数据库引擎)

Or this:

或这个:

The statement could not be parsed.

该语句无法解析。

Additional information:

附加信息:

Syntax error in FROM clause. (Microsoft Access Database Engine)

FROM子句中的语法错误。 (Microsoft Access数据库引擎)

If the Copy data from one or more tables or views radio button is chosen, when the Next button is pressed, the Select Source Tables and Views page will be shown:

如果选择了“从一个或多个表或视图复制数据”单选按钮,则按下“ 下一步”按钮时,将显示“ 选择源表和视图”页面:

On this page, all worksheets for the Excel file (ImportData.xlsx) will be listed under the Source column. From the Source list, choose from which worksheets you want to import data to the SQL Server database by clicking the check box next to the name of the worksheets. The selected worksheets will appear in the Destination column:

在此页面上,Excel文件(ImportData.xlsx)的所有工作表将在“ 源”列下列出。 在“源”列表中,通过单击工作表名称旁边的复选框,选择要将数据从哪个工作表导入SQL Server数据库。 所选的工作表将出现在“ 目标”列中:

The name of the tables in the SQL Server database by default will be the names of the selected worksheets from the Source column, but these names can be changed by clicking on the name in the Destination column:

默认情况下,SQL Server数据库中表的名称将是“ 源”列中所选工作表的名称,但是可以通过单击“ 目标”列中的名称来更改这些名称:

As it may be noticed, the icon in the Destination column for the BusinessEntity$ field is different from the Table1 and Table2 fields. This is because the table in the SQL Server database that is chosen as a destination already exists and for the Table1 and Table2 fields, new tables will be created.

可能已经注意到, BusinessEntity $字段的“目标”列中的图标与Table1和Table2字段不同。 这是因为选择作为目标SQL Server数据库中的表已经存在,并且对于Table1和Table2字段,将创建新表。

When using an existing table, make sure that the destination table has the same number of columns that have data source, otherwise the columns from the data source that does not have an adequate destination column will be by default ignored (<ignore>) and the data from that columns will not be imported to a destination table:

使用现有表时,请确保目标表具有相同数目的具有数据源的列,否则,默认情况下将忽略数据源中没有足够目标列的列( <ignore> ),并且该列中的数据不会导入到目标表中:

Also, make sure that columns in the destination table have data types that are compatible with the data types in the columns of the source data, otherwise the following error may appear:

另外,请确保目标表中的列具有与源数据的列中的数据类型兼容的数据类型,否则可能会出现以下错误:

Found 1 unknown column type conversion(s) You are only allowed to save the package

找到1个未知的列类型转换您只允许保存该程序包

When the appropriate columns and the data types are set, click the Next button, the Save and Run Package page of the SQL Server Import and Export Wizard will appear:

设置了适当的列和数据类型后,单击“ 下一步”按钮,将出现“ SQL Server导入和导出向导的“ 保存并运行包”页面:

Click the Next button if want to import data from an Excel file to a SQL Server database, but if want to save the SSIS package for the later use, check the Save SSIS Package button.

如果要将数据从Excel文件导入到SQL Server数据库,请单击“ 下一步”按钮,但是如果要保存SSIS包以供以后使用,请选中“ 保存SSIS包”按钮。

On the Complete the Wizard page, choices that are made in the previous wizard pages are shown:

在“ 完成向导”页面上,显示在先前的向导页面中做出的选择:

Click the Next button to import data from an Excel file to a SQL Server database. The Performing Operation page shows the status of the importing process if it is finished successfully or with the errors:

单击“ 下一步”按钮将数据从Excel文件导入到SQL Server数据库。 “ 执行操作”页面显示导入过程的状态,如果导入过程成功完成或出现错误:

If the error occurs during the process of importing data from Excel file to the SQL Server database the Messages… word will appear in the Message column for the action that failed:

如果在将数据从Excel文件导入SQL Server数据库的过程中发生错误,则Messages字样将出现在Message列中,用于失败的操作:

When click on the Messages…, the View Report dialog will appear with detailed information about the error:

当单击消息...时,将显示“ 查看报告”对话框其中包含有关错误的详细信息:

This error:

这个错误:

– Validating (Error)

–验证(错误)

Messages

留言内容

  • Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column “numb”.
    (SQL Server Import and Export Wizard)
  • 错误0xc0202049:数据流任务1:无法插入只读列“ numb”。
    (SQL Server导入和导出向导)
  • Error 0xc0202045: Data Flow Task 1: Column metadata validation failed.
    (SQL Server Import and Export Wizard)
  • 错误0xc0202045:数据流任务1:列元数据验证失败。
    (SQL Server导入和导出向导)
  • Error 0xc004706b: Data Flow Task 1: “Destination – test1” failed validation and returned validation status “VS_ISBROKEN”.
    (SQL Server Import and Export Wizard)
  • 错误0xc004706b:数据流任务1:“目标– test1”验证失败,并返回验证状态“ VS_ISBROKEN”。
    (SQL Server导入和导出向导)
  • Error 0xc004700c: Data Flow Task 1: One or more component failed validation.
    (SQL Server Import and Export Wizard)
  • 错误0xc004700c:数据流任务1:一个或多个组件验证失败。
    (SQL Server导入和导出向导)
  • Error 0xc0024107: Data Flow Task 1: There were errors during task validation.
    (SQL Server Import and Export Wizard)
  • 错误0xc0024107:数据流任务1:任务验证期间发生错误。
    (SQL Server导入和导出向导)

Typically, it appears when the destination table has an IDENTITY column. To resolve this, turn back to the Select Source Tables and Views page, select the tables that have identity property and press the Edit Mappings button. From the Transfer Settings dialog, select the Enable identity insert check box:

通常,它在目标表具有IDENTITY列时显示。 要解决此问题,请返回“ 选择源表和视图”页面,选择具有标识属性的表,然后按“ 编辑映射”按钮。 在“ 传输设置”对话框中,选中“ 启用身份插入”复选框:

Also, another common problem that may appear when importing data from data source to the destination SQL Server tables is the FOREIGN KEY Constraints problem. In the error below two destination tables test1 and test2 are shown. The test2 table is referenced to the test1 table:

另外,将数据从数据源导入到目标SQL Server表中时可能出现的另一个常见问题是FOREIGN KEY Constraints问题。 在下面的错误中,显示了两个目标表test1和test2。 test2表引用到test1表:

– Copying to [dbo].[test1] (Error)

–复制到[dbo]。[test1](错误)

Messages

留言内容

  • Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “Destination 1 – test2” (79) failed with error code 0xC0209029 while processing input “Destination Input” (92). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
    (SQL Server Import and Export Wizard)
  • 错误0xc0047022:数据流任务1:SSIS错误代码DTS_E_PROCESSINPUTFAILED。 处理输入“目标输入”(92)时,组件“目标1 – test2”(79)的ProcessInput方法失败,错误代码为0xC0209029。 所标识的组件从ProcessInput方法返回了错误。 该错误特定于组件,但该错误是致命的,将导致数据流任务停止运行。 在此之前可能会发布错误消息,其中包含有关失败的更多信息。
    (SQL Server导入和导出向导)
  • Information 0x402090df: Data Flow Task 1: The final commit for the data insertion in “Destination – test1” has started.
    (SQL Server Import and Export Wizard)
  • 信息0x402090df:数据流任务1:“目标– test1”中数据插入的最后提交已开始。
    (SQL Server导入和导出向导)
  • Information 0x402090e0: Data Flow Task 1: The final commit for the data insertion in “Destination – test1” has ended.
    (SQL Server Import and Export Wizard)
  • 信息0x402090e0:数据流任务1:“目标– test1”中数据插入的最后提交已结束。
    (SQL Server导入和导出向导)

– Copying to [dbo].[test2] (Error)

–复制到[dbo]。[test2](错误)

Messages

留言内容

  • Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “The statement has been terminated.”.
    An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Cannot insert the value NULL into column ‘No_id’, table ‘ImportData.dbo.test2’; column does not allow nulls. INSERT fails.”.
    (SQL Server Import and Export Wizard)
  • 错误0xc0202009:数据流任务1:SSIS错误代码DTS_E_OLEDBERROR。 发生OLE DB错误。 错误代码:0x80004005。
    OLE DB记录可用。 来源:“ Microsoft SQL Server本机客户端11.0”结果:0x80004005说明:“该语句已终止。”。
    OLE DB记录可用。 来源:“ Microsoft SQL Server Native Client 11.0”结果:0x80004005说明:“无法将值NULL插入表'ImportData.dbo.test2'的列'No_id'中; 列不允许为空。 INSERT失败。”。
    (SQL Server导入和导出向导)
  • Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “Destination 1 – test2.Inputs[Destination Input]” failed because error code 0xC020907B occurred, and the error row disposition on “Destination 1 – test2.Inputs[Destination Input]” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
    (SQL Server Import and Export Wizard)
  • 错误0xc0209029:数据流任务1:SSIS错误代码DTS_E_INDUCEDTRANSFORMFAILUREONERROR。 “目标1 – test2.Inputs [目标输入]”失败,因为发生了错误代码0xC020907B,并且“目标1 – test2.Inputs [目标输入]”上的错误行配置表示发生错误。 指定组件的指定对象发生错误。 在此之前可能会发布错误消息,其中包含有关失败的更多信息。
    (SQL Server导入和导出向导)

To resolve this problem, disable constraint for referenced table (test2) by executing the following code in SSMS:

要解决此问题,请通过在SSMS中执行以下代码来禁用对引用表(test2)的约束:

ALTER TABLE test2 NOCHECK CONSTRAINT ALL

After importing data from data source to the destination SQL Server database, execute the following code in SSMS to enable constraint for the test2 table:

将数据从数据源导入到目标SQL Server数据库后,在SSMS中执行以下代码以启用对test2表的约束:

ALTER TABLE test2 WITH CHECK CHECK CONSTRAINT ALL

In some cases, the warning message like from the image below may appear:

在某些情况下,可能会出现如下图所示的警告消息:

– Validating (Warning)

–验证(警告)

Messages

留言内容

  • Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column “AddressLine1” with a length of 255 to database column “AddressLine1” with a length of 60.
    (SQL Server Import and Export Wizard)
  • 警告0x802092a7:数据流任务1:由于将数据从长度为255的数据流列“ AddressLine1”插入长度为60的数据库列“ AddressLine1”,可能会发生截断。
    (SQL Server导入和导出向导)
  • Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column “AddressLine2” with a length of 255 to database column “AddressLine2” with a length of 60.
    (SQL Server Import and Export Wizard)
  • 警告0x802092a7:数据流任务1:由于将数据从长度为255的数据流列“ AddressLine2”插入到长度为60的数据库列“ AddressLine2”,可能会发生截断。
    (SQL Server导入和导出向导)
  • Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column “City” with a length of 255 to database column “City” with a length of 30.
    (SQL Server Import and Export Wizard)
  • 警告0x802092a7:数据流任务1:由于将长度为255的数据流列“ City”中的数据插入长度为30的数据库列“ City”中,因此可能会发生截断。
    (SQL Server导入和导出向导)
  • Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column “AddressLine1” with a length of 255 to database column “AddressLine1” with a length of 60.
    (SQL Server Import and Export Wizard)
  • 警告0x802092a7:数据流任务1:由于将数据从长度为255的数据流列“ AddressLine1”插入长度为60的数据库列“ AddressLine1”,可能会发生截断。
    (SQL Server导入和导出向导)
  • Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column “AddressLine2” with a length of 255 to database column “AddressLine2” with a length of 60.
    (SQL Server Import and Export Wizard)
  • 警告0x802092a7:数据流任务1:由于将数据从长度为255的数据流列“ AddressLine2”插入到长度为60的数据库列“ AddressLine2”,可能会发生截断。
    (SQL Server导入和导出向导)
  • Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column “City” with a length of 255 to database column “City” with a length of 30.
    (SQL Server Import and Export Wizard)
  • 警告0x802092a7:数据流任务1:由于将长度为255的数据流列“ City”中的数据插入长度为30的数据库列“ City”中,因此可能会发生截断。
    (SQL Server导入和导出向导)
  • Warning 0x80049304: Data Flow Task 1: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system’s console.
    (SQL Server Import and Export Wizard)
  • 警告0x80049304:数据流任务1:警告:无法打开全局共享内存以与性能DLL进行通信;无法执行此操作。 数据流性能计数器不可用。 要解决此问题,请以管理员身份或在系统控制台上运行此程序包。
    (SQL Server导入和导出向导)

To resolve this, go to the SQL Server destination table and increase the column size for the columns that are listed in the warning message.

若要解决此问题,请转到SQL Server目标表,并增加警告消息中列出的列的列大小。

To verify that the data from the Excel file are imported to the SQL Server database, go to SSMS, find the database in which data are imported and list all data from the tables:

若要验证将Excel文件中的数据导入到SQL Server数据库,请转到SSMS,找到要在其中导入数据的数据库,并列出表中的所有数据:

参考文献: ( References: )

翻译自: https://www.sqlshack.com/import-data-excel-file-sql-server-database/

  • 1
    点赞
  • 0
    评论
  • 12
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2021 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值