Issue with importing data from Excel from Sharepoint online

  • Thread starter Thread starter Alla2552
  • Start date Start date
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...
 
Back
Top