Bulk Upload Excel Data From Stored in SharePoint Library to SharePoint List (CSOM)
Below Code helps in Excel data upload from excel file located in Shared Document library to list name Demo
Upload excel file whose data to copy in csv formatin Shared Documents library
**
**
//Copy Variables and change as desired
private static string SiteURL = "http://tritontest";<br>
private static string FilePath = @"C:\temp\";<br>
private static string StatusFileName = "UploadStatus.txt";<br>
private static string FileName = "Demo.csv";<br>
private static string ListName= "Demo";
Copy Below code in Main or Page Load Event
**
**
** **
//Write Status of upload in file
FileStream ostrm;
StreamWriter writer;
TextWriter oldOut = Console.Out;
System.IO.File.WriteAllText(FilePath + StatusFileName, string.Empty);
ostrm = new FileStream(FilePath + StatusFileName, FileMode.OpenOrCreate, FileAccess.Write);
writer = new StreamWriter(ostrm);
Console.SetOut(writer);
Console.WriteLine("Data Upload Begins");
//Delete exiting file
DeleteData(FilePath + FileName);
//Download new project file
DownloadDocument(FileName);
//Get Data
DataTable dtDemo = GetDataTabletFromCSVFile(FilePath + FileName);
if (dtDemo.Rows.Count != 0)
{
//Delete existing data fromDemo list
DeleteAllItems(ListName);
//Insert data into Demo list
InsertData(dtSuppliers, ListName);
} Console.WriteLine("Data Upload Ends");
Console.SetOut(oldOut);
writer.Close();
ostrm.Close();
Copy Following Functions
**
#region Delete File
public static void DeleteData(string strFileToDelete)
{
try
{
System.IO.File.Delete(strFileToDelete);
}
catch (System.IO.IOException e)
{
Console.WriteLine(e.Message);
}
}
#endregion
#region Download File
public static void DownloadDocument(string documentName)
{
ListItem item = GetDocumentFromSP(documentName);
if (item != null)
{
using (ClientContext clientContext = new ClientContext(SiteURL))
{
FileInformation fInfo = Microsoft.SharePoint.Client.File.OpenBinaryDirect(clientContext, item["FileRef"].ToString());
byte[] bytesarr = ReadFully(fInfo.Stream);
DownloadFileUsingFileStream(bytesarr, documentName);
}
}
}
private static void DownloadFileUsingFileStream(byte[] filecontent, string fileName)
{
FileStream fs = new FileStream(FilePath + fileName, FileMode.CreateNew);
using (fs)
{
BinaryWriter bw = new BinaryWriter(fs);
bw.Write(filecontent, 0, filecontent.Length);
bw.Close();
}
}
private static ListItem GetDocumentFromSP(string documentName)
{
//This method is discussed above i.e. Get List Item Collection from SharePoint
//Document List
ListItemCollection listItems = GetListItemCollectionFromSP("FileLeafRef", documentName, "Text", 1);
return (listItems != null && listItems.Count == 1) ? listItems[0] : null;
}
private static ListItemCollection GetListItemCollectionFromSP(string name, string value, string type, int rowLimit)
{
//Update siteURL and DocumentListName with as per your site
ListItemCollection listItems = null;
using (ClientContext clientContext = new ClientContext(SiteURL))
{
List documentsList = clientContext.Web.Lists.GetByTitle(DocumentLibrary);
CamlQuery camlQuery = new CamlQuery(); ;
camlQuery.ViewXml = @"\<View\>\<Query\>\<Where\>\<Eq\>\<FieldRef Name='" + name + @"'/\>\<Value Type='" + type + "'>" + value + @"\</Value\>\</Eq\>\</Where\>\<RowLimit\>" + rowLimit.ToString() + @"\</RowLimit\>\</Query\>\</View\>";
listItems = documentsList.GetItems(camlQuery);
clientContext.Load(documentsList);
clientContext.Load(listItems);
clientContext.ExecuteQuery();
}
return listItems;
}
public static byte[] ReadFully(Stream input)
{
byte[] buffer = new byte[16 * 1024];
using (MemoryStream ms = new MemoryStream())
{
int read;
while ((read = input.Read(buffer, 0, buffer.Length)) > 0)
{
ms.Write(buffer, 0, read);
}
return ms.ToArray();
}
}
#endregion
#region Read File
private static DataTable GetDataTabletFromCSVFile(string csvFile)
{
DataTable csvData = new DataTable();
try
{
using (TextFieldParser csvReader = new TextFieldParser(csvFile))
{
csvReader.SetDelimiters(new string[] { ";" });
csvReader.HasFieldsEnclosedInQuotes = true;
string[] colFields = csvReader.ReadFields();
foreach (string column in colFields)
{
DataColumn datecolumn = new DataColumn(column);
datecolumn.AllowDBNull = true;
csvData.Columns.Add(datecolumn);
}
while (!csvReader.EndOfData)
{
string[] fieldData = csvReader.ReadFields();
//Making empty value as null
for (int i = 0; i < fieldData.Length; i++)
{
if (fieldData[i] == "")
{
fieldData[i] = null;
}
}
csvData.Rows.Add(fieldData);
}
}
}
catch (Exception ex)
{
}
return csvData;
}
#endregion
#region Delete List Item
public static void DeleteAllItems(string strListName)
{
ClientContext clientContext = new ClientContext(SiteURL);
SP.List oList = clientContext.Web.Lists.GetByTitle(strListName);
ListItemCollection listItems = oList.GetItems(CamlQuery.CreateAllItemsQuery());
clientContext.Load(listItems,
eachItem => eachItem.Include(
item => item,
item => item["ID"]));
clientContext.ExecuteQuery();
var totalListItems = listItems.Count;
var counter = 0;
Console.WriteLine("Deletion in List:" + strListName + ". Started at :" + DateTime.Now.ToString());
if (totalListItems > 0)
{
for (counter = totalListItems - 1; counter > -1; counter--)
{
listItems[counter].DeleteObject();
clientContext.ExecuteQuery();
}
}
Console.WriteLine("Deletion in List:" + strListName + ". Completed at :" + DateTime.Now.ToString());
}
#endregion
#region Insert List Item
public static void InsertData(DataTable filedata, string strListName, string FinanceCompany)
{
DataTable dt = filedata;
int count = 0;
ClientContext clientContext = new ClientContext(SiteURL);
SP.List oList = clientContext.Web.Lists.GetByTitle(strListName);
ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
Console.WriteLine("Insertion in list:" + strListName + ". Begins at:" + DateTime.Now.ToString());
foreach (DataRow dr in dt.Select("Finance_Company = " + FinanceCompany))
{
ListItem oListItem = oList.AddItem(itemCreateInfo);
oListItem["Title"] = dr["Finance_Company"];
oListItem["Field1"] = dr["Field1"];
oListItem["Field2"] = dr["Field2"];
oListItem.Update();
clientContext.ExecuteQuery();
count++;
}
Console.WriteLine("Insertion in list:" + strListName + ". Completed at:" + DateTime.Now.ToString());
}
#endregion
**