How can you read excel data from sharepoint online library and load it into sql server database? Using c#

Nitesh 1 Reputation point
2021-10-09T21:47:55.587+00:00

I want to read excel data from sharepoint library (online) and load the data in database using c#. I don't want to download file to my local.

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
10,249 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,623 questions
SharePoint Development
SharePoint Development
SharePoint: A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.Development: The process of researching, productizing, and refining new or existing technologies.
2,798 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

2 answers

Sort by: Most helpful
  1. David 151 Reputation points
    2021-10-11T04:01:24.817+00:00

    You may try Spire.XLS for .NET. It supports to load an Excel file from stream and export data from a specified worksheet to Datatable by using the following C# code.

    Workbook book = new Workbook();
    book.LoadFromStream(stream);  
    DataTable dt = book.Worksheets[0].ExportDataTable(); 
    
    0 comments No comments

  2. RaytheonXie_MSFT 33,641 Reputation points Microsoft Vendor
    2021-10-11T09:16:24.037+00:00

    Hi @Nitesh ,
    We can use following url get json data of excel file by graph api first

    https://graph.microsoft.com/beta/me/drive/items/{id}/workbook/tables('4')/Rows  
    https://graph.microsoft.com/beta/me/drive/root:/{item-path}:/workbook/tables('4')/Rows  
    

    Refer to the following link
    https://learn.microsoft.com/en-us/graph/api/resources/excel?view=graph-rest-beta

    Then we can use following code to insert data into the sqlserver

    string sql = "INSERT INTO table (name) values (@name)";  
    conn.Open();  
    SqlCommand cmd = new SqlCommand(sql, conn);  
    cmd.Parameters.Add("@name", SqlDbType.VarChar);  
    cmd.Parameters["@name"].Value = test;  
    cmd.ExecuteNonQuery();  
    

    If an Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.