Upload large list (2 mln records) to SharePoint Online, from Access *.mdb

Question

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;
        }

Answer 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