question

Nitesh-5533 avatar image
0 Votes"
Nitesh-5533 asked RaytheonXie-MSFT commented

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

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.

dotnet-csharpoffice-sharepoint-onlinesql-server-transact-sqlsharepoint-dev
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Nitesh-5533 ,
would you please provide us with an update on the status of your issue?

0 Votes 0 ·

Hi @Nitesh-5533 ,
I am checking to see how things are going there on this issue.

0 Votes 0 ·

Hi @Nitesh-5533 ,
If there's anything you'd like to know, don't hesitate to ask.

0 Votes 0 ·

Hi @Nitesh-5533 ,
I am checking to see if the problem has been resolved.

0 Votes 0 ·
David-9140 avatar image
0 Votes"
David-9140 Suspended answered

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(); 


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RaytheonXie-MSFT avatar image
0 Votes"
RaytheonXie-MSFT answered RaytheonXie-MSFT commented

Hi @Nitesh-5533 ,
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://docs.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.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@RaytheonXie-MSFT when i try to do " https://graph.microsoft.com/beta/me/drive/root:/{item-path}:/workbook/tables('4')/Rows" i get this

"value": []

but i run my query with worksheet range i able to see the excel data. i want my query to get all the data from excel so i do not want to use "Range".

0 Votes 0 ·

Hi @Nitesh-5533 ,
Please check your table id and permission. We might get null value if we don't have access to the table

0 Votes 0 ·