Unable to Import Excel files into SQL Server

Copper Contributor

Hello SQL Team, 

 

I'm new to SQL Server & I just created a database and need to import an Excel file.

 

The problem is that the 3 methods of importing the file has failed.

 

 

 

  1. If I use tasks>import file, the Wizard opens and after indicating the datasource, I get the following error message:

RDP2024_1-1718094956050.png

 

 

 

2.  If I go to Start and use the SQL Server 2022 Import and Export Data (64-bit) in my pc Apps, I get this error:

RDP2024_3-1718095357849.png

 

 

 

 

 

 

 

3.  If I try importing in the csv format and import as a flat file, I get the following error message:

RDP2024_2-1718095174082.png

 

 

can anyone help please? 

 

7 Replies

@RDP2024 , as the message say, the data provider ACE for Office files isn't registered, you have to install it first.

Download Microsoft Access Database Engine 2016 Redistributable from Official Microsoft Download Cent...

@olafhelper 

 

both are downloaded already

 

 restarted my laptop about 5 times.... nothing

@RDP2024 

For the final results screen, clicking the "error" result will bring up the actual error message.

I also noticed that the redistributable Olafhelper is not version 12.

 

From the following Q&A article, it would appear that a solution is to choose Excel 2016 in the drop-down.

https://learn.microsoft.com/en-us/answers/questions/1658052/download-link-for-microsoft-ace-oledb-12...

 

Screenshot 2024-06-11 141536.png

@SivertSolem 

 

Yes, I do know that clicking on the error (when importing the flat csv file) will show the error details, however clicking on the error details that does not resolve this issue unfortunately. If you need to see this full error message:

===================================

Error inserting data into table. (Microsoft.SqlServer.Import.Wizard)

------------------------------
Program Location:

at Microsoft.SqlServer.Import.Wizard.InsertData.ResultCheck(Result result)
at Microsoft.SqlServer.Import.Wizard.InsertData.DoWork()
at Microsoft.SqlServer.Management.TaskForms.SimpleWorkItem.Run()

===================================

Error inserting data into table. (Microsoft.SqlServer.Prose.Import)

------------------------------
Program Location:

at Microsoft.SqlServer.Prose.Import.BcpTextSynthesis.InsertIntoDB(String inputFilePath, String tableName, String schemaName, IReadOnlyList`1 columnInfo, SqlConnection connection, Int32 batchSize, SqlTransaction transaction, IList`1 allFinalTransformations, IList`1 allFinalTransformationColumns, IList`1 allFinalColNames)
at Microsoft.SqlServer.Prose.Import.BcpProcess.CreateTableAndInsertDataIntoDb(String connectionString, Int32 batchSize, String azureAccessToken)

===================================

Column 'Salary' does not allow DBNull.Value. (Microsoft.Data.SqlClient)

------------------------------
Program Location:

at Microsoft.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)
at Microsoft.Data.SqlClient.SqlBulkCopy.ReadWriteColumnValueAsync(Int32 col)
at Microsoft.Data.SqlClient.SqlBulkCopy.CopyColumnsAsync(Int32 col, TaskCompletionSource`1 source)
at Microsoft.Data.SqlClient.SqlBulkCopy.CopyRowsAsync(Int32 rowsSoFar, Int32 totalRows, CancellationToken cts, TaskCompletionSource`1 source)
at Microsoft.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
at Microsoft.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
at Microsoft.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
at Microsoft.SqlServer.Prose.Import.BcpTextSynthesis.InsertIntoDB(String inputFilePath, String tableName, String schemaName, IReadOnlyList`1 columnInfo, SqlConnection connection, Int32 batchSize, SqlTransaction transaction, IList`1 allFinalTransformations, IList`1 allFinalTransformationColumns, IList`1 allFinalColNames)

 

 

 

 

 

 

@SivertSolem 

 

I have M365, so shouldn't Access 2016 already be installed?

@SivertSolem 

 

I get the same error message if I  select Microsoft Excel 2016. I called M365 support & after troubleshooting, they were unable to resolve, but they did confirm that since I have M365, I shold not need to download the 2016 product. 

 

Very disappointing

 

RDP2024_0-1718127897267.png

 

 


I also noticed that the redistributable Olafhelper is not version 12.

@SivertSolem , indeed, because that version is out-of-support and therefore there is no public download available.