You can create one Console Application in which you can read list item from EXCEL file and import it into SharePoint list. You must have all attachments separated for your list items.
File Path of the Excel file should be: C:\Exports\<list title>.xlsx
File Path for the attachments should be: C:\Exports\<ID of the list item>(Folder with name ID of list item)\your attachment items
From below code you can create console application to import data into list. Just add required DDLs references to your application.
CODE:
Program.cs
class Program
{
public static string siteUrl = "<SharePoint site Url>";
public static string username = "<username>";
public static string password = "<password>";
public static string spListName = "<ListName>"; //In which you want to import data
public static string connString = string.Empty;
public static string filePath = string.Empty;
public static void Main(string[] args)
{
#region Import data
using (var clientContext = new ClientContext(siteUrl))
{
try
{
SecureString passWord = new SecureString();
foreach (char c in password.ToCharArray()) passWord.AppendChar(c);
Console.WriteLine("Connecting \"" + siteUrl + "\"");
clientContext.Credentials = new SharePointOnlineCredentials(username, passWord);
Web Oweb = clientContext.Web;
clientContext.Load(Oweb);
clientContext.ExecuteQuery();
List spList = clientContext.Web.Lists.GetByTitle(spListName);
Console.WriteLine("Getting List: " + spListName);
clientContext.Load(spList);
clientContext.ExecuteQuery();
filePath = "C:\\Export\\" + spListName + ".xlsx";
Program p = new Program();
p.readExcel_importListData(clientContext, spListName);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if (clientContext != null)
clientContext.Dispose();
}
}
#endregion
}
public void readExcel_importListData(ClientContext context, String listName)
{
try
{
string dirLocation = Directory.GetCurrentDirectory();
Console.WriteLine("Location: : " + filePath);
List oList = context.Web.Lists.GetByTitle(listName);
ListItemCreationInformation listCreationInformation = new ListItemCreationInformation();
if (filePath.EndsWith(".xls"))
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filePath + "'; Extended Properties='Excel 8.0;'";
}
else if (filePath.EndsWith(".xlsx"))
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filePath + "';Extended Properties='Excel 12.0;'";
}
using (OleDbConnection con = new OleDbConnection(connString))
{
using (OleDbCommand cmd = new OleDbCommand())
{
//Read Sheet
cmd.Connection = con;
con.Open();
DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
con.Close();
string sheetName = "<List Title>";
//Read the Header Row and value
cmd.CommandText = "select * from [" + sheetName + "$]";
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
System.Data.DataTable data = new DataTable();
da.SelectCommand = cmd;
da.Fill(data);
foreach (DataRow i in data.Rows)
{
ListItem oListItem = oList.AddItem(listCreationInformation);
if (listName != "Document Library")
{
foreach (DataColumn column in data.Columns)
{
if (column.ColumnName != "ID")
{
string columnValue = i[column.ColumnName].ToString();
if (columnValue != "" || columnValue != string.Empty)
oListItem[column.ColumnName] = columnValue;
else
oListItem[column.ColumnName] = null;
}
}
//Stops all event receivers while updating items in list
using (var eventReceiverManager = new EventReceiverManager(true))
{
Console.WriteLine("Importing " + i["NameforCertificateTicket"].ToString());
eventReceiverManager.StopEventReceiver();
oListItem.Update();
context.ExecuteQuery();
eventReceiverManager.StartEventReceiver();
}
#region upload attachments
string localfilePath = "C:\\Export\\" + i["ID"].ToString();
bool exists = System.IO.Directory.Exists(localfilePath);
if(exists)
{
string[] filePaths = Directory.GetFiles(localfilePath);
foreach(string s in filePaths)
{
using(FileStream fs = System.IO.File.OpenRead(s))
{
var attInfo = new AttachmentCreationInformation();
attInfo.FileName = fs.Name;
attInfo.ContentStream = new MemoryStream(System.IO.File.ReadAllBytes(s));
Attachment att = oListItem.AttachmentFiles.Add(attInfo);
Console.WriteLine("Uploading "+fs.Name);
context.Load(att);
context.ExecuteQuery();
}
}
}
#endregion
}
}
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine();
Console.WriteLine(spListName + " List data imported Successfully.");
Console.Read();
}
}
}
}
catch (SystemException ex)
{
Console.WriteLine(ex.Message);
Console.Read();
}
}
}
EventReceiverManager.cs
//Stops all event receivers while updating items in list
public class EventReceiverManager : SPEventReceiverBase, IDisposable
{
public EventReceiverManager(bool disableImmediately)
{
EventFiringEnabled = !disableImmediately;
}
public void StopEventReceiver()
{
EventFiringEnabled = false;
}
public void StartEventReceiver()
{
EventFiringEnabled = true;
}
public void Dispose()
{
EventFiringEnabled = true;
}
}
Hope this will help you!
