0

I try to migrate records from Access database to SharePoint list (about 2 millions records). I go to "External" tab, then "More.." and there is "Export to SharePoint list". It failed after about 15'600 records saying "Cannot update. Database or object is in readonly mode".

I also tried to "Import spreadsheet" from *.xlsx, but excel supports only about 1 mln rows, and gives errors if I select more than about 250'000 rows to import. It was very slow so I don't know if it really can import these 250'000. but it tried :)

Maybe You have any ideas how to insert these records - maybe I can use Azure, upload *.mdb or *.accdb there and then sync with SharePoint list?..

There is not much columns, less then 10, but about 2 million rows.

update: I understand that there is threshold etc., but I want to use this list with Search, not views or queries. I'm sure this would not be problem for search crawler

Update:

Thank You Slaven! Updated post with code I used:

  private static OleDbConnection GetConnection()
        {
            OleDbConnection conn = new OleDbConnection();
            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=C:\Serial Numbers.accdb";

            conn = new OleDbConnection(connectionString);
            conn.Open();
            return conn;
        }


        public static void Main()
        {
            // settings variables..

            string queryString = "SELECT * FROM SerialNums";

            using (OleDbConnection connection = GetConnection())
            {
                OleDbCommand command = new OleDbCommand(queryString, connection);
                OleDbDataReader reader = command.ExecuteReader();
                var position = 0;

                int count = reader.FieldCount;

                using (ClientContext ctx = CreateClientContext(siteUrl, username, password)) // function to authenticate..
                {

                    List list = ctx.Web.Lists.GetByTitle(listName);

                    int batchSize = 200;
                    int currentBatch = 0;

                    for (int i = 0; i < 2008556; i++, currentBatch++)
                    {
                        try
                        {
                            if (reader.HasRows)
                            {
                                reader.Read();
                            }
                            else {
                                Console.WriteLine("NO DATA TO READ, EXIT");
                                return;
                            }

                            position++;
                            Console.WriteLine(String.Format("row: {0}, old id value {1}", position, reader.GetValue(0)));

                            ListItem item = list.AddItem(new ListItemCreationInformation());
                            item["_OldID"] = reader.GetValue(0);
                            item["Title"] = reader.GetValue(1);               
                            item.Update();

                            if (currentBatch >= batchSize)
                            {
                                // Reset the batch
                                currentBatch = 0;
                                Console.WriteLine("Resetting Batch to zero. Execute query");
                                ctx.ExecuteQuery();
                            }
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.ToString());
                        }
                    }

                    Console.WriteLine("Execute query one more time");
                    ctx.ExecuteQuery();
                }
            }
        }

        private static ClientContext CreateClientContext(string siteUrl, string username, string password)
        {
            ClientContext context = new ClientContext(siteUrl);
            var securePassword = new SecureString();
            foreach (var chr in password) securePassword.AppendChar(chr);
            context.Credentials = new SharePointOnlineCredentials(username, securePassword);
            return context;
        }
1

You can write your own simple batch import script in CSOM.

using (ClientContext ctx = new ClientContext("https://some-site-url"))
{
    // use proper credentials

    List list = ctx.Web.Lists.GetByTitle("Your List");

    int batchSize = 200;
    int currentBatch = 0;
    for (int i = 0; i < 10000; i++)
    {
        ListItem item = list.AddItem(new ListItemCreationInformation());
        // replace the code to write the data from your Access DB
        item["Title"] = "Some value";
        item.Update();

        if (currentBatch >= batchSize)
        {
            // Reset the batch
            currentBatch = 0;
            ctx.ExecuteQuery();
        }
    }

    // Execute query one more time if the barch size is less then batchSize
    ctx.ExecuteQuery();
}

Source: https://sharepoint.stackexchange.com/a/236677/33924

1
  • Thank You Slaven! It works, uploaded 500'000 and still working. I'll update post
    – Gennady G
    Oct 8 '19 at 9:43

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.