none
Programmatically read excel file from SharePoint online document library(o365) and save to sql server database.

    Question

  • i am uploading document to sharepoint online document library Programmatically by using below method

     using (var clientContext = objCommon.GetContext())
                        {
                            if (clientContext != null)
                            {
                                
                                System.IO.Stream fStream = fuDocuments.PostedFile.InputStream;
                                byte[] _byteArray = new byte[fStream.Length];
                                fStream.Read(_byteArray, 0, (int)fStream.Length);
                                fStream.Close();
                                SP.List documentLib = clientContext.Web.Lists.GetByTitle(DocumentLib);
                                SP.FileCreationInformation fcInfo = new SP.FileCreationInformation();
                                string DocPath = documentLib.RootFolder.Context.Url + DocumentLib + "/";
                                Session["DocPath"] = DocPath;
                            string _fileUrl = documentLib.RootFolder.Context.Url + DocumentLib + "/" + fuDocuments.PostedFile.FileName;
                                fcInfo.Url = _fileUrl;
                                docx = fcInfo.Url;
                                fcInfo.Content = _byteArray;
                                fcInfo.Overwrite = true;
                                SP.File file = documentLib.RootFolder.Files.Add(fcInfo);
                                file.ListItemAllFields["ProjectID"] = ProjectID;
                                file.ListItemAllFields.Update();
                                System.IO.File.ReadAllBytes(path);
                                clientContext.Load(file);
                                clientContext.ExecuteQuery();

                            }
                        }

    once after this upload i would like to read excel file from sharepoint online document library and save to sql server database

    can anyone suggest how to do that??

    Friday, January 27, 2017 11:24 AM

Answers

  • Hi,

    We can read the document file from document library in SharePoint Online, the following code snippet for your reference:

    using (var clientContext = objCommon.GetContext())
    {
    	if (clientContext != null)
    	{
    		 
    		var list = clientContext.Web.Lists.GetByTitle("DocumentLib");
    		var listItem = list.GetItemById(1);
    		clientContext.Load(list);
    		clientContext.Load(listItem, i => i.File);
    		clientContext.ExecuteQuery();
    
    		var fileRef = listItem.File.ServerRelativeUrl;
    		var fileInfo = Microsoft.SharePoint.Client.File.OpenBinaryDirect(clientContext, fileRef);
    		var fileName = Path.Combine("C:\\Users\\epmadmin\\Desktop\\YourFolderName", (string)listItem.File.Name);
    		using (var fileStream = System.IO.File.Create(fileName))
    		{
    			fileInfo.Stream.CopyTo(fileStream);
    		}               
    
    	}
    } 
    

    If you want to save the file into SQL server, please refer to the blog below:

    Store or save files/documents in SQL Server database using C#

    http://www.shabdar.org/sql-server/121-store-or-save-files-in-sql-server-database-using-c.html

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, January 30, 2017 3:06 AM
    Moderator