A
Alla2552
Guest
I have 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?
Alla Sanders
Continue reading...
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?
Alla Sanders
Continue reading...