This SharePoint csom tutorial explains, how to read CSV file from a document library using the .Net client object model (csom) in SharePoint Online.
Here we are going to write our csom code in a windows application. So first create a windows application and then add the below ddls in the reference. You can add these two dlls from NuGet package manager.
- Microsoft.SharePoint.Client
- Microsoft.SharePoint.Client.Runtime
Here we have a csv file which has 3 columns as:
- SiteName
- SiteURL
- SiteDescription
And it has 3 rows of data and the CSV file looks like below:
And we have uploaded the CSV file to a SharePoint document library (MyDocLib) like below:
Read CSV file from SharePoint document library using CSOM
Below is the full code to read the CSV file from the SharePoint document library using csom.
private void btnSubmit_Click(object sender, EventArgs e)
{
using (ClientContext context = new ClientContext("https://onlysharepoint2013.sharepoint.com/sites/Bhawana/"))
{
string username = "********@onlysharepoint2013.onmicrosoft.com";
context.AuthenticationMode = ClientAuthenticationMode.Default;
var secureString = new SecureString();
foreach (char c in ""********")
{
secureString.AppendChar(c);
}
context.Credentials = new SharePointOnlineCredentials(username, secureString);
try
{
DataTable dt= ImportSiteList(context);
}
catch (Exception ex)
{
throw;
}
}
}
private DataTable ImportSiteList(ClientContext context)
{
try
{
List list = context.Web.Lists.GetByTitle("MyDocLib");
context.Load(list);
context.ExecuteQuery();
Folder folder = list.RootFolder;
FileCollection files = folder.Files;
context.Load(files);
context.ExecuteQuery();
DataTable dt = new DataTable("tblSiteLists");
foreach (Microsoft.SharePoint.Client.File f in files)
{
FileInformation fileInformation = Microsoft.SharePoint.Client.File.OpenBinaryDirect(context, (string)f.ServerRelativeUrl);
if((string)f.ServerRelativeUrl == "/sites/Bhawana/MyDocLib/Sites.csv")
{
using (System.IO.StreamReader sr = new System.IO.StreamReader(fileInformation.Stream))
{
String line = sr.ReadToEnd();
string dataformat = line.Replace("\r\n", ",");
string[] strArray = dataformat.Split(‘,’);
dt.Columns.Add("SiteName", typeof(string));
dt.Columns.Add("SiteURL", typeof(string));
dt.Columns.Add("SiteDescription", typeof(string));
int j = 0;
DataRow dr = dt.NewRow();
for (int i = 3; i < strArray.Length – 1; i++)
{
dr[j] = strArray[i];
j++;
if (j == 3)
{
j = 0;
dt.Rows.Add(dr);
dr = dt.NewRow();
}
}
}
}
}
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
The above code will return a data table which looks like below:
You may like following SharePoint csom tutorials:
- Content type Examples using CSOM in SharePoint Online/2013/2016
- Copy list items from one list to another in SharePoint programmatically using CSOM
- Create an index in SharePoint Online list using CSOM
- Activate SharePoint Server Publishing Feature programmatically using CSOM
- Create Update Delete a list using Client Object Model CSOM in SharePoint 2013
- How to get access token in SharePoint Online using CSOM and use in Postman or Google Rest client
This SharePoint tutorial, we learned how to read CSV file from the SharePoint document library programmatically using CSOM.
Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com