1

I've written a method, BulkCopy, to upload my Excel file to SQL Server database table. I am trying to unit test this and it fails each time with "System.NotSupportedException : Specified method is not supported".

If someone could have a look it would be much appreciated.

Kind regards,

Emmett

    public static void BulkCopy(string inputFilePath, string tableName)
    {

        System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
        var stream = File.Open(inputFilePath, FileMode.Open, FileAccess.Read);
        using (var reader = ExcelReaderFactory.CreateReader(stream))
        {
            using (var bulkCopy = new SqlBulkCopy(ConnectionString))
            {

                bulkCopy.EnableStreaming = true;
                bulkCopy.DestinationTableName = tableName;
                reader.Read();
                var cols = Enumerable.Range(0, reader.FieldCount).Select(i => reader.GetValue(i)).ToArray();
                foreach (var col in cols)
                {
                    var column = cols.GetValue(0).ToString();

                    if (column.Trim() == "Column 1")
                    {
                        bulkCopy.ColumnMappings.Add(column, "Column 1");
                    }

                    if (column.Trim() == "Column 2")
                    {
                        bulkCopy.ColumnMappings.Add(column, "Column 2");
                    }

                    if (column.Trim() == "Column 3")
                    {
                        bulkCopy.ColumnMappings.Add(column, "Column 3");
                    }

                //continued for column mappings...

                }

                bulkCopy.WriteToServer(reader);
            }
            Console.WriteLine("Copy data to database done (DataReader).");
        }
    }
Barrassment
  • 75
  • 1
  • 7

2 Answers2

2

I tested your code ,the issue is showed in the following code ,you reader is incorrect.

bulkCopy.WriteToServer(reader);

Pass the datatable into bulk , try the below code

public static void BulkCopy(string inputFilePath, string tableName)
    {
        System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
        var stream = System.IO.File.Open(inputFilePath, FileMode.Open, FileAccess.Read);
        IExcelDataReader reader;

        if (inputFilePath.EndsWith(".xls"))
            reader = ExcelReaderFactory.CreateBinaryReader(stream);
        else if (inputFilePath.EndsWith(".xlsx"))
            reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
        else
            throw new Exception("The file to be processed is not an Excel file");
        var conf = new ExcelDataSetConfiguration
        {
            ConfigureDataTable = _ => new ExcelDataTableConfiguration
            {
                UseHeaderRow = true
            }
        };
        var dataSet = reader.AsDataSet(conf);

        // Now you can get data from each sheet by its index or its "name"
        var dataTable = dataSet.Tables[0];

        using (var bulkCopy = new SqlBulkCopy(ConnectionString))
            {
                bulkCopy.EnableStreaming = true;
                bulkCopy.DestinationTableName = tableName;
                reader.Read();
                var cols = Enumerable.Range(0, reader.FieldCount).Select(i => reader.GetValue(i)).ToArray();
                foreach (var col in cols)
                {
                    var column =col.ToString();

                    if (column.Trim() == "Column 1")
                    {
                        bulkCopy.ColumnMappings.Add(column, "Column1");
                    }

                    if (column.Trim() == "Column 2")
                    {
                        bulkCopy.ColumnMappings.Add(column, "Column2");
                    }

                    if (column.Trim() == "Column 3")
                    {
                        bulkCopy.ColumnMappings.Add(column, "Column3");
                    }

                    //continued for column mappings...

                }
                bulkCopy.WriteToServer(dataTable);
            }
            Console.WriteLine("Copy data to database done (DataReader).");           
    }

Pass the dataReader into bulk, change your foreach part as shown

               for (var i = 0; i<cols.Count();i++)
                {
                    if (cols[i].ToString().Trim() == "Column 1")
                    {
                        bulkCopy.ColumnMappings.Add(i, "Column1");
                    }

                    if (cols[i].ToString().Trim() == "Column 2")
                    {
                        bulkCopy.ColumnMappings.Add(i, "Column2");
                    }

                    if (cols[i].ToString().Trim() == "Column 3")
                    {
                        bulkCopy.ColumnMappings.Add(i, "Column3");
                    }

                    //continued for column mappings...

                }
Xueli Chen
  • 11,987
  • 3
  • 25
  • 36
  • Hi, thanks for that, I have tested it and it works perfectly. The only thing is that I was hoping to avoid the need for using a DataTable to save the overhead. I think it should work using ExcelDataReader, per the DataReaderBulkCopySample() method example here: https://csharp.hotexamples.com/site/file?hash=0x4ee593e397a9eeebaca2bd45be323f565e38af84ae2da9e7ce43db1cac965b00&fullName=Program.cs&project=gSerP1983/OpenXml.Excel.Data Only I keep getting this "Specified method is not supported" error still. I'm wondering if it's because I'm using asp.net core, but I'm likely wrong. – Barrassment Feb 18 '20 at 15:56
  • 1
    For using ExcelDataReader , use `public SqlBulkCopyColumnMapping Add(int sourceColumnIndex, string destinationColumn);` this method , please check my updated answer. – Xueli Chen Feb 19 '20 at 09:23
  • Thank you, this now works perfectly and is exactly what I was looking for. – Barrassment Feb 19 '20 at 13:37
  • I'm glad that you have resolved the issue , could you mark my reply as answer? This will help other people who faces the same or similar issue to find the answer quickly. – Xueli Chen Feb 20 '20 at 01:10
0

You need to change the way it's setting the column mappings. If you delete them it will work. In the example link you posted they are using GetName to get the columns. I tried that and it doesn't work for some reason. I get a GetOrdinal error. That's the same error that is coming from your code: ExcelDataReader.ExcelDataReader`2.GetOrdinal(String name).

Not sure how pretty it is, but it works.

public static void BulkCopy(string inputFilePath, string tableName)
    {

        System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
        var stream = File.Open(inputFilePath, FileMode.Open, FileAccess.Read);
        using (var reader = ExcelReaderFactory.CreateReader(stream))
        {
            using (var bulkCopy = new SqlBulkCopy(ConnectionString))
            {

                bulkCopy.EnableStreaming = true;
                bulkCopy.DestinationTableName = tableName;
                reader.Read();
                var cols = Enumerable.Range(0, reader.FieldCount).Select(i => reader.GetValue(i)).ToArray();
                foreach (var col in cols)
                {
                     if (cols[i].ToString() == "Column 1")
                     {
                          bulkCopy.ColumnMappings.Add(i, "Column 1");
                     }

                     if (cols[i].ToString() == "Column 2")
                     {
                          bulkCopy.ColumnMappings.Add(i, "Column 2");
                     }

                     if (cols[i].ToString() == "Column 3")
                     {
                          bulkCopy.ColumnMappings.Add(i, "Column 3");
                     }

                    //continued for column mappings...

                }

                bulkCopy.WriteToServer(reader);
            }
            Console.WriteLine("Copy data to database done (DataReader).");
        }
    }
nickfinity
  • 1,119
  • 2
  • 15
  • 29