How Do I Upload an Excel File to a Database

At that place are many means to import data from an Excel file to a SQL Server database using:

• SQL Server Integration Services (SSIS)
• the OPENROWSET and OPENDATASOURCE functions
• SQL Server linked servers
• the SQL Server Import and Export Wizard

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

To first the process of importing information from an Excel file to a SQL Server database using the SQL Server Import and Export Wizard the SQL Server Import and Consign Magician needs to be launched. There are several means 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 Sorcerer folio

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

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

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

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

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

As you can run across, at that place is no Microsoft Excel provider in the listing of the Data source drop downwards box. At that place are several reasons for this. The offset reason could exist that Microsoft Office isn't installed. But in that location is no demand to install Microsoft Office (Microsoft Excel) in order to meet the Microsoft Excel provider in the Data source drop downwards box list.

To see the Microsoft Excel provider in the list, install Microsoft Access Database Engine 2016 Redistributable. The latest version of Microsoft Access Database Engine tin can open the earlier version of Excel, so make certain that y'all accept the latest one.

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

• AccessDatabaseEngine.exe is 32-fleck version
• AccessDatabaseEngine_X64.exe is 64-bit version

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 downwards box.

This is because you lot may run a incorrect version of the SQL Server Import and Consign Wizard. For example, the AccessDatabaseEngine.exe is installed and the SQL Server Import and Export Sorcerer 64-bit version is launched. In this case, in lodge to see the Microsoft Excel provider in the listing of the Data source driblet down box, launch the SQL Server Import and Export Sorcerer 32-chip version and the Microsoft Excel provider volition appear in the list:

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

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

From the Excel version driblet downwardly box, cull the version of Excel that uses the source workbook. In our example, that is the Microsoft Excel 2016 version:

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

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

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

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

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

The following warning message may appear afterward clicking the Side by side button:

The operation could not be completed.

Additional information:

The 'Microsoft.ACE.OLEDB.xvi.0' provider is not registered on the local machine. (Organisation.Data)

Typically, this alarm 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 Consign Wizard is launched, simply you have installed the Microsoft Admission Database Engine 2016 Redistributable 64 bit version.

In that location are 2 solutions for resolving this problem:

• The first is to launch the 64-bit version of the SQL Server Import and Export Wizard via the Beginning menu
• The 2d resolution is to install the Microsoft Access Database Engine 2016 Redistributable 32 fleck version and go along using the SQL Server Import and Export Wizard via SSMS, but first, you need to uninstall the 64-flake version of Microsoft Access Database Engine 2016 Redistributable, otherwise the following warning bulletin volition appear when launching the installation package:

Note: The Microsoft Admission Database Engine 2016 Redistributable tin be installed in serenity mode.

Open up the Command Prompt window and run the post-obit:

For 32-bit version

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

For 64-chip version

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

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

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

Under the Destination driblet down box, cull a provider that tin connect to a SQL Server database.

One of providers that tin connect to SQL Server is:

• .Cyberspace Framework Data Provider for SqlServer
• Microsoft OLE DB Provider for SQL Server
• SQL Server Native Customer 11.0

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

From the Server name philharmonic box, select the SQL Server case:

In the Authentication department, determine how it will exist connected to SQL Server by using Windows or SQL Server Authentication fashion.

From the Database drib downward box, choice a database in which data from information source (Excel file) will be placed:

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:

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

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

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

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

If the Write a query to specify the information to transfer in the Choose a Destination page is chosen, and so the Provide a Source Query page will exist shown when the Next button is pressed:

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

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

The statement could not be parsed.

Additional information:

The Microsoft Access database engine could not find the object 'BusinessEntity'. Brand certain the object exists and that you spell its name and the path name correctly. If 'BusinessEntity' is non a local object, check your network connexion or contact the server administrator. (Microsoft Admission Database Engine)

Or this:

The argument could not be parsed.

Additional information:

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

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

On this folio, all worksheets for the Excel file (ImportData.xlsx) will be listed under the Source column. From the Source listing, choose from which worksheets you lot desire to import data to the SQL Server database past clicking the check box adjacent to the proper noun of the worksheets. The selected worksheets will appear in the Destination column:

The name of the tables in the SQL Server database past default volition be the names of the selected worksheets from the Source column, but these names tin can be inverse past clicking on the name in the Destination column:

Every bit it may be noticed, the icon in the Destination cavalcade for the BusinessEntity$ field is different from the Table1 and Table2 fields. This is because the table in the SQL Server database that is called every bit a destination already exists and for the Table1 and Table2 fields, new tables volition be created.

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

As well, make sure that columns in the destination tabular array accept data types that are compatible with the information types in the columns of the source data, otherwise the following error may appear:

Establish ane unknown cavalcade type conversion(s) You are only allowed to save the package

When the appropriate columns and the data types are set, click the Adjacent button, the Salvage and Run Packet page of the SQL Server Import and Export Wizard will appear:

Click the Next button if want to import information from an Excel file to a SQL Server database, but if want to salve the SSIS package for the afterwards use, bank check the Save SSIS Package push.

On the Consummate the Sorcerer page, choices that are made in the previous wizard pages are shown:

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

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

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

This error:

– Validating (Fault)

Messages

• Error 0xc0202049: Data Flow Task ane: Failure inserting into the read-but column "numb".
  (SQL Server Import and Export Magician)
  
• Error 0xc0202045: Data Flow Job 1: Column metadata validation failed.
  (SQL Server Import and Consign Wizard)
  
• Error 0xc004706b: Data Flow Task 1: "Destination – test1" failed validation and returned validation condition "VS_ISBROKEN".
  (SQL Server Import and Consign Wizard)
  
• Error 0xc004700c: Information Flow Job ane: One or more component failed validation.
  (SQL Server Import and Export Wizard)
  
• Error 0xc0024107: Data Flow Task ane: There were errors during chore validation.
  (SQL Server Import and Consign Wizard)
  

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:

Too, another common problem that may announced when importing information 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 tabular array:

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

Messages

• Error 0xc0047022: Data Period Task 1: SSIS Error Lawmaking DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination 1 – test2" (79) failed with error lawmaking 0xC0209029 while processing input "Destination Input" (92). The identified component returned an fault from the ProcessInput method. The error is specific to the component, but the error is fatal and will crusade the Data Menstruum task to finish running. At that place may be error messages posted before this with more information about the failure.
  (SQL Server Import and Consign Wizard)
  
• Information 0x402090df: Data Catamenia Chore 1: The final commit for the data insertion in "Destination – test1" has started.
  (SQL Server Import and Export Sorcerer)
  
• Data 0x402090e0: Data Flow Task 1: The final commit for the data insertion in "Destination – test1" has ended.
  (SQL Server Import and Export Wizard)
  

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

Messages

• Error 0xc0202009: Data Period Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error lawmaking: 0x80004005.
  An OLE DB record is bachelor. Source: "Microsoft SQL Server Native Client eleven.0" Hresult: 0x80004005 Description: "The statement has been terminated.".
  An OLE DB record is bachelor. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Clarification: "Cannot insert the value NULL into column 'No_id', tabular array 'ImportData.dbo.test2'; column does non allow nulls. INSERT fails.".
  (SQL Server Import and Consign Wizard)
  
• Mistake 0xc0209029: Data Period Task one: SSIS Fault Lawmaking DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Destination ane – 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 mistake. An error occurred on the specified object of the specified component. There may exist mistake messages posted earlier this with more information about the failure.
  (SQL Server Import and Export Wizard)
  

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

ALTER Table test2 NOCHECK CONSTRAINT ALL

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

Change TABLE test2 WITH Cheque Bank check CONSTRAINT ALL

In some cases, the warning bulletin like from the image beneath may appear:

– Validating (Warning)

Messages

• Alarm 0x802092a7: Data Menstruation Task i: 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 lx.
  (SQL Server Import and Consign Wizard)
  
• Alert 0x802092a7: Data Flow Chore i: Truncation may occur due to inserting data from data menstruum column "AddressLine2" with a length of 255 to database column "AddressLine2" with a length of lx.
  (SQL Server Import and Export Magician)
  
• Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from information period column "City" with a length of 255 to database cavalcade "City" with a length of 30.
  (SQL Server Import and Consign Sorcerer)
  
• Warning 0x802092a7: Data Menstruation Task 1: Truncation may occur due to inserting data from data period column "AddressLine1" with a length of 255 to database column "AddressLine1" with a length of threescore.
  (SQL Server Import and Export Wizard)
  
• Warning 0x802092a7: Information Period Task i: Truncation may occur due to inserting data from data menstruum cavalcade "AddressLine2" with a length of 255 to database cavalcade "AddressLine2" with a length of 60.
  (SQL Server Import and Export Wizard)
  
• Warning 0x802092a7: Data Catamenia Task i: Truncation may occur due to inserting data from information menstruation column "City" with a length of 255 to database column "City" with a length of 30.
  (SQL Server Import and Export Magician)
  
• Alarm 0x80049304: Information Period Task one: Warning: Could not open global shared retentiveness to communicate with operation DLL; data catamenia performance counters are not available. To resolve, run this bundle as an ambassador, or on the arrangement's console.
  (SQL Server Import and Export Wizard)

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

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

References:

• Import data from Excel to SQL Server or Azure SQL Database
• How to import/export data to SQL Server using the SQL Server Import and Export Wizard
• How to query Excel data using SQL Server linked servers
• Import and Export Data with the SQL Server Import and Export Wizard

• Author
• Recent Posts

Marko aka "Zivko" is a senior software analyst from Nis, Serbia focusing on SQL Server and MySQL likewise as client technologies similar SSMS, Visual Studio, and VSCode. He has extensive experience with quality assurance, issue escalation/resolution, and product evangelism.

He is a prolific author of administrative content related to SQL Server including a number of "platinum" articles (top ane% in terms of popularity and engagement). His writing covers a range of topics on MySQL and SQL Server including remote/linked servers, import/export, LocalDB, SSMS, and more.

In his part-time, Zivko likes basketball, foosball (table-soccer), and rock music.

See more virtually Marko at LinkedIn

View all posts by Marko Zivkovic

kirbyfeativill.blogspot.com

Source: https://www.sqlshack.com/import-data-excel-file-sql-server-database/

Share this post