none
Issue with importing data from Excel from Sharepoint online RRS feed

  • Question

  • 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

    lundi 19 octobre 2020 12:42

Toutes les réponses

  • Hi Alla2552,

    Thank you for posting here.

    >>Now that excel files were moved to **Sharepoint Online** location

    You can consider downloading Excel file from SharePoint and then use the existing code to read excel file from local drive.

    Here's a reference you can refer to.

    Download File From SharePoint 365

    The converted VB.NET code is

            Const username As String = "username@tenant.onmicrosoft.com"
            Const password As String = "password"
            Const url As String = "https://tenant.sharepoint.com/"
            Dim securedPassword = New SecureString()
    
            For Each c In password.ToCharArray()
                securedPassword.AppendChar(c)
            Next
            Dim credentials = New SharePointOnlineCredentials(username, securedPassword)
            DownloadFile(url, credentials, "/Shared Documents/Report.xslx")
    
    
        Private Sub DownloadFile(ByVal webUrl As String, ByVal credentials As ICredentials, ByVal fileRelativeUrl As String)
            Using client = New WebClient()
                client.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")
                client.Headers.Add("User-Agent: Other")
                client.Credentials = credentials
                client.DownloadFile(webUrl, fileRelativeUrl)
            End Using
        End Sub

    Hope it could be helpful.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    mardi 20 octobre 2020 05:42
    Modérateur
  • Thank you. Does it mean that there is no direct way to read the excel file and use bulk insert into sql?

    Alla Sanders

    mercredi 21 octobre 2020 17:57
  • Hi Alla2552,

    Thanks for your feedback.

    >>Does it mean that there is no direct way to read the excel file and use bulk insert into sql?

    Hope suggestions in the following references could be helpful.

    1. SSIS Excel/CSV from Sharepoint to SQL Server
    2. Easiest way to extract SharePoint list data to a separate SQL Server table?

    If you need any help about SSIS, you can post your question on SSIS forum.

    Best Regards,

    Xingyu Zhao 

     

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    jeudi 22 octobre 2020 02:23
    Modérateur
  • Thank you. It seems that those methods require manual processing, while I need the code in Visual basic that we run as scheduled task - without any user' input. Unless I misunderstood? Thanks, Alla

    Alla Sanders

    jeudi 22 octobre 2020 16:14
  • Hi Alla2552,

    You can try the following code which use SPList.Items property  to get an unfiltered collection of all items in the list.

            Dim strUrl As String = "some url"
            Using myWeb As SPWeb = New SPSite(strUrl).OpenWeb() ' Or SPContext.Current.Web
                Dim items As SPListItemCollection = myWeb.Lists("YourListName").Items
                Dim fieldValue1 As String = ""
                Dim fieldValuen As String = ""
                Dim con As SqlConnection = New SqlConnection("connection string")
                cmd.Connection = con
    
                Using cmd As SqlCommand = New SqlCommand()
                    con.Open()
    
                    For Each item As SPListItem In items
                        fieldValue1 = item("column 1 name in SPList").ToString()
                        fieldValuen = item("column n name in SPList").ToString()
                        Dim sqlCommand As String = "INSERT INTO TABLE_NAME(field1ColumnName, ..., fieldnColumnName) VALUES('" & fieldValue1 & "', ...,'" & fieldValue1 & "')"
                        cmd.CommandText = sqlCommand
                        cmd.ExecuteNonQuery()
                    Next
    
                    con.Close()
                End Using
            End Using

    Note: Since I don't have the test environment, the above code has not been tested.

    Besides, after consulting with SSIS experts, the following reference could be helpful.

    How to configure OData SSIS Connection for SharePoint Online

    Best Regards,

    Xingyu Zhao

    Note: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; Therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    vendredi 23 octobre 2020 02:46
    Modérateur
  • Thank you for the idea. The code you enclosed goes through a Sharepoint list, while I need to get to read and extract data from the excel file in the folder of SP. Is there a way to achieve it? Thanks, Alla

    Alla Sanders

    vendredi 23 octobre 2020 12:28
  • Hi Alla2552,

    Two methods of reading Excel files from SharePoint document are suggested in the following reference,

    Read excel file stored in SharePoint Document Library

    The first one is to read Excel data through Excel Services 2010 REST API.

    You can refer to the following code in vb.net.

    Public Class ExcelClient
        Implements IDisposable
        Dim _client As WebClient
        Public Sub New(ByVal webUri As Uri, ByVal credentials As ICredentials)
            webUri = webUri
            _client = New WebClient With {
                .Credentials = credentials
            }
        End Sub
    
        Public Function ReadTable(ByVal libraryName As String, ByVal fileName As String, ByVal tableName As String, ByVal formatType As String) As String
            Dim endpointUrl = String.Format("{0}/_vti_bin/ExcelRest.aspx/{1}/{2}/Model/Tables('{3}')?$format={4}", WebUri, libraryName, fileName, tableName, formatType)
            Return _client.DownloadString(endpointUrl)
        End Function
    
        Private Sub IDisposable_Dispose() Implements IDisposable.Dispose
            _client.Dispose()
            GC.SuppressFinalize(Me)
        End Sub
    End Class

    The second one use OpenXML SDK, and here's a document you may need.

    Parse and read a large spreadsheet document (Open XML SDK)

    After reading the data from Excel, you can insert the data into SQL Server.

    Hope them could be helpful.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.






    lundi 26 octobre 2020 01:46
    Modérateur
  • Sorry. I'm confused.

    So I'm currently using Microsoft.Office.Interop.Excel.Worksheet to get excel sheet name. Then you suggested to use SP List, so I installed Microsoft.SharePoint library

    Now you are suggesting to use REST API or Open XML SDK. Seems like a lot of different ways. Why is it not possible to use Sql bulk import like before we switched to Sharepoint? Or - OleDbCommand to open Excel?

    Is it really the only way to achieve it - to use so many different methods? What would be most efficient way? Thanks, Alla


    Alla Sanders

    lundi 26 octobre 2020 21:02
  • Hi Alla2552,

    >>What would be most efficient way

    I regret that I don't have the test environment, so in order to get more efficient responses,you can also consider posting your question on developer community. Thanks.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    mardi 27 octobre 2020 01:24
    Modérateur