I had an existing code reading excel file from the shared drive and importing data into sql (windows app - visual basic). Now that excel files were moved to Sharepoint Online location - existing code that used OleDbConnection and SqlBulkCopy doesn't work. I wasn't able to connect to open excel file at all, even after setting up authorized access to Sharepoint online. After researching alternative methods - I was able to get the sheet names of the excel file on Sharepoint, using Microsoft.Office.Interop.Excel, but still stuck on bulk insert the data from each sheet into the sql, since it was using OleDbConnection to excel. The excel file is huge with multiple pages, so bulk insert is preferred... Please let me know how to achieve it?
Original code for bulk insert (getting an error on ExcelConnection1.Open() - "IErrorInfo.GetDescription failed with E_FAIL(0x80004005).") Please note that filNm - is actually url "https://Actual Sharepoint path/filename.xlsx"
Dim ExcelStringNm As String = "Provider=Microsoft.ACE.OLEDB.16.0;" & "Data Source=" & filNm & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1""" Dim ExcelConnection1 As New OleDb.OleDbConnection(ExcelStringNm) For Cnt = 1 To SheetCnt Select Case Cnt Case 1 : SheetName = SheetName1 Case 2 : SheetName = SheetName2 Case 3 : SheetName = SheetName3 Case 4 : SheetName = SheetName4 Case 5 : SheetName = SheetName5 End Select expr = "SELECT * FROM [" + SheetName + "]" + ";" Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection1) ExcelConnection1.Open() SQLconn.ConnectionString = ConnString SQLconn.Open() Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn) bulkCopy.DestinationTableName = TableNameSql Try objDR = objCmdSelect.ExecuteReader bulkCopy.WriteToServer(objDR) objDR.Close() SQLconn.Close() ExcelConnection1.Close() Catch ex As Exception ExcelConnection1.Close() SQLconn.Close() End Try End Using Next
I got sheet names using this code:
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet Dim xlApp As New Microsoft.Office.Interop.Excel.Application
xlApp.Workbooks.Open(filNm, 0, True) Dim strSheetName As New List(Of String) For Each xlWorkSheet In xlApp.Sheets strSheetName.Add(xlWorkSheet.Name) Next
How can I insert data from each sheet of xlWorksheet into sql?
User contributions licensed under CC BY-SA 3.0