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.
9,621 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,238 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,668 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. David 146 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
  2. RaytheonXie_MSFT 31,071 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.