0

Is there any possible way to upload bulk attachments into sharepoint list item while importing data from excel to sharepoint which will create custom list.

Our idea is to create custom list with attachments (supporting documents) together while exporting the data from excel to sharepoint. Is it possible ?

We have 1000+ list item where in each list item has separate supporting documents. I want to upload all of these in one shot like how we export the excel data to list item.

The supporting evidence should be uploaded as attachment in the respective line item.

0

You can try this using power-shell to upload bulk attachments. follow the steps given the below link http://www.c-sharpcorner.com/UploadFile/3d571f/adding-bulk-attachments-to-sharepoint-list-item-with-powersh/

0

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.