SBC and MetaData example throws exception

[rajeev-ranjan]

Code based on the example provided in documentation

using (SqlConnection connection = new SqlConnection(connectionString)) {
          SqlBulkCopy sbc = new SqlBulkCopy(connection);
               sbc.DestinationTableName = "CsvReaderDeaultValueTest";
                using (LumenWorks.Framework.IO.Csv.CsvReader reader = new LumenWorks.Framework.IO.Csv.CsvReader(new StreamReader(fileName), true)) {
                    reader.Columns.Add(new LumenWorks.Framework.IO.Csv.Column { Name = "Meta", Type = typeof(String) });
                    reader.UseColumnDefaults = true;
                   reader.Columns[reader.Columns.Count - 1].DefaultValue = Path.GetFileName(fileName);
                    string columnHeader = String.Empty;
                    string pattern = @"[\s\(\)\-\/\:]+";
                    Regex regex = new Regex(pattern);
                    foreach (var column in reader.Columns) {
                        columnHeader = regex.Replace(column.Name, String.Empty);
                        sbc.ColumnMappings.Add(columnHeader, columnHeader);
                    }
                    connection.Open();
                    sbc.BatchSize = 10000;
                    sbc.WriteToServer(reader); //write base rows to db
                }
            }

I get the error

'Meta' field header not found. Parameter name: name

I found that when we do sbc.WriteToServer(reader) it makes a call to LumenWorks.Framework.IO.Csv.CsvReader.IDataRecord.GetOrdinal(string name) and for the column that was added ("Meta" in this example) it throws an ArgumentException when trying to do _fieldHeaderIndexes.TryGetValue(name, out index)

How did you guys do the SBC using the CsvReader? I noticed that you had sbc.AddColumnMapping() method which is not native to SqlBulkCopy class. Did you guys write an extension method for this thing to work? Thanks.

[phatcher]

Have a look at the Readme - I've extended the example to show the implementation of AddColumnMapping

[phatcher]

@rajeev-ranjan Looking at your code again, it appears you are trying to infer the majority of the headers from the source data but add an explicit column for the filename.

For the SBC usage this is not really supported since it would only work if all the columns are string, so I specify them all and then add the explicity columns afterwards.

Also your regex on the column name doesn't affect the CSV column names only what your attempting to map to in SBC - which won't work, you would need to do this change against the original columns

Here's the code as I might expect it to be - not tested though...

using (SqlConnection connection = new SqlConnection(connectionString)) {
    SqlBulkCopy sbc = new SqlBulkCopy(connection);
    sbc.DestinationTableName = "CsvReaderDeaultValueTest";
    using (LumenWorks.Framework.IO.Csv.CsvReader reader = new LumenWorks.Framework.IO.Csv.CsvReader(new StreamReader(fileName), true)) {
        -- Force it to read the headers
        reader.GetFieldHeaders();

        -- Tidy up the column names - NB Still presumes that all columns are string
        string pattern = @"[\s\(\)\-\/\:]+";
        Regex regex = new Regex(pattern);
        foreach (var column in reader.Columns) {
            column.Name = regex.Replace(column.Name, String.Empty);
        }

        -- Now add the extra metdata column
        reader.Columns.Add(new LumenWorks.Framework.IO.Csv.Column { Name = "Meta", Type = typeof(String) });
        reader.UseColumnDefaults = true;
        reader.Columns["Meta"].DefaultValue = Path.GetFileName(fileName);

        -- Map SBC columns
        foreach (var column in reader.Columns) {
            sbc.ColumnMappings.Add(column.Name, column.Name);
        }

        -- Do the work
        connection.Open();
        sbc.BatchSize = 10000;
        sbc.WriteToServer(reader); //write base rows to db
    }
}

[joeg10]

Hi, I'm trying to add a meta data field to the csvreader. I can see the field "LoanTape" is added in the columns collection, but I receive ": ''LoanTape' field header not found. Parameter name: name'" when executing WriteToServer. Can you help me out?

using (CsvReader csv = new CsvReader(new StreamReader(new MemoryStream(csvData)), true)) { csv.GetFieldHeaders();

            csv.Columns.Add(new LumenWorks.Framework.IO.Csv.Column {   Name = "LoanTape", Type = typeof(int)  });            

            // Fix up the column defaults with the values we need
            csv.UseColumnDefaults = true;
            csv.Columns[32].DefaultValue = loanTapeId.ToString();
            //csv.Columns[csv.GetFieldIndex("LoanTapeId")].DefaultValue = loanTapeId.ToString();

            using (var sbc = new SqlBulkCopy(connectionString))
            {
                sbc.DestinationTableName = "dbo.Loan";
                sbc.BatchSize = 1000;

                foreach (var item in fields)
                {
                    switch (item.MappingName)
                    {
                        case "LOANID":
                            sbc.ColumnMappings.Add(item.Name, "LoanNumber");
                            break;
                        case "ORIGDATE":
                            sbc.ColumnMappings.Add(item.Name, "OriginationDate");
                            break;
                        case "YOUNGESTBORROWERDOB":
                            sbc.ColumnMappings.Add(item.Name, "YoungestDateOfBirth");
                            break;
                        case "COSTTOSERVICE":
                            sbc.ColumnMappings.Add(item.Name, "CostToService"); break;
                        case "PMTAMTSCHED-CURR":
                            sbc.ColumnMappings.Add(item.Name, "CurrentPaymentAmountSecheduled"); break;
                        case "CURRUPB": sbc.ColumnMappings.Add(item.Name, "CurrentPrincipal"); break;
                        case "CURRPRINCIPALLIMIT": sbc.ColumnMappings.Add(item.Name, "CurrentPrincipalLimit"); break;
                        case "MCA": sbc.ColumnMappings.Add(item.Name, "MaximumClaimAmount"); break;
                        case "SVCFEE": sbc.ColumnMappings.Add(item.Name, "ServiceFee"); break;
                        case "INTERESTRATE@CURR": sbc.ColumnMappings.Add(item.Name, "CurrentInterestRate"); break;
                        case "MIPRATE": sbc.ColumnMappings.Add(item.Name, "MortgageInsurancePremiumRate"); break;
                        case "GENDER": sbc.ColumnMappings.Add(item.Name, "Gender"); break;
                        case "LOANSTATUS": sbc.ColumnMappings.Add(item.Name, "LoanStatus"); break;
                        case "PMTPLAN-CURR": sbc.ColumnMappings.Add(item.Name, "CurentPaymentPlan"); break;
                        case "OPTIONAL1": sbc.ColumnMappings.Add(item.Name, "Optional1"); break;
                        case "OPTIONAL2": sbc.ColumnMappings.Add(item.Name, "Optional2"); break;
                        case "OPTIONAL3": sbc.ColumnMappings.Add(item.Name, "Optional3"); break;
                        case "OPTIONAL4": sbc.ColumnMappings.Add(item.Name, "Optional4"); break;
                        case "OPTIONAL5": sbc.ColumnMappings.Add(item.Name, "Optional5"); break;
                        case "ADJTYPE": sbc.ColumnMappings.Add(item.Name, "AdjustmentType"); break;
                        case "HMBSSECURITIZATIONDATE": sbc.ColumnMappings.Add(item.Name, "HMBSSecuritizationDate"); break;
                        case "SECURITIZEDBALANCE-CURRENT": sbc.ColumnMappings.Add(item.Name, "CurrentSecuritizedBalance"); break;
                        case "HMBSParticipationRate-Current": sbc.ColumnMappings.Add(item.Name, "CurrentHMBSParticipationRate"); break;
                        case "LOANMARGIN": sbc.ColumnMappings.Add(item.Name, "LoanMargin"); break;
                        case "RATECEILING": sbc.ColumnMappings.Add(item.Name, "RateCeiling"); break;
                        case "RATEFLOOR": sbc.ColumnMappings.Add(item.Name, "RateFloor"); break;
                        case "CURR-REMAININGYR1FUNDS": sbc.ColumnMappings.Add(item.Name, "CurrRemainingYr1Funds"); break;
                        case "HOMEPRICE-CURR": sbc.ColumnMappings.Add(item.Name, "HomePriceCurr"); break;
                        case "HOMEPRICEDATE-CURR": sbc.ColumnMappings.Add(item.Name, "HomePriceDateCurr"); break;
                        case "PROPERTYSTATE": sbc.ColumnMappings.Add(item.Name, "PropertyState"); break;
                        case "MONTH1RLV": sbc.ColumnMappings.Add(item.Name, "Month1Rlv"); break;
                        case "TIMEINSTATUS": sbc.ColumnMappings.Add(item.Name, "TimeInStatus"); break;
                        case "DEBENTURERATE": sbc.ColumnMappings.Add(item.Name, "DebentureRate"); break;
                        case "NEXTARMRESET": sbc.ColumnMappings.Add(item.Name, "NextArmReset"); break;
                        case "ARMINTERVAL": sbc.ColumnMappings.Add(item.Name, "ArmInterval"); break;

                        default:
                            break;
                    }
                }
                //csv.GetFieldHeaders();
                sbc.ColumnMappings.Add("LoanTape", "LoanTape");
                sbc.WriteToServer(csv);

[firthy]

Hi, I have the exact same issue. 'myfield' field header not found. Did you manage to resolve this? Also GetFieldIndex returns -1

======================== using (var reader = new LumenWorks.Framework.IO.Csv.CsvReader(new StreamReader(stream), true)) {

                    reader.Columns = new List<LumenWorks.Framework.IO.Csv.Column>
                    {

                        new LumenWorks.Framework.IO.Csv.Column { Name = "Order Number", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Order Type", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Account Ref", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Order Date", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Customer Name", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Status", Type = typeof(int) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Notes 1", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Notes 2", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Notes 3", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Delivery Address Name", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Delivery Address Line 1", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Delivery Address Line 2", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Delivery Address Line 3", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Delivery Address Line 4", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Delivery Address Line 5", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Carriage Net", Type = typeof(decimal) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Carriage Tax Code", Type = typeof(int) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Carriage Nominal Code", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Carriage Consignment", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Carriage Dept Code", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Carriage Courier", Type = typeof(int) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Global Tax Code", Type = typeof(int) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Global Nominal Code", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Global Dept Code", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Global Details", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Product Code", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "QuantityAllocated", Type = typeof(decimal) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "QuantityDelivered", Type = typeof(decimal) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "QuantityToDespatch", Type = typeof(decimal) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Quantity", Type = typeof(decimal) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Unit Price", Type = typeof(decimal) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Unit of Sale", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Description", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "DetailsText", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Comment 1", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Comment 2", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Tax Code", Type = typeof(int) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Nominal Code", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Dept Code", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Line Information", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Discount Amount", Type = typeof(decimal) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Discount Rate", Type = typeof(decimal) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Project Ref", Type = typeof(string) },
                        new LumenWorks.Framework.IO.Csv.Column { Name = "Net", Type = typeof(decimal) },

                        // NB Fake column so bulk import works
                        new LumenWorks.Framework.IO.Csv.Column { Name = "ImportGuid", Type = typeof(Guid), DefaultValue = Guid.NewGuid().ToString() }
                    };

                    // Fix up the column defaults with the values we need
                    reader.UseColumnDefaults = true;

                    //int fieldIndex = reader.GetFieldIndex("ImportGuid");
                    //reader.Columns[44].DefaultValue = Guid.NewGuid().ToString();

                    SqlConnection conn = new SqlConnection(connectionString);
                    conn.Open();

                    SqlTransaction transaction = conn.BeginTransaction();

                    try
                    {
                        SqlBulkCopy scb = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock, transaction);

                        scb.DestinationTableName = "SalesOrderImport";
                        scb.BulkCopyTimeout = 3600;
                        scb.BatchSize = 5000;

                        scb.ColumnMappings.Add("Order Number", "OrderNumber");
                        scb.ColumnMappings.Add("Order Type", "OrderType");
                        scb.ColumnMappings.Add("Account Ref", "AccountRef");
                        scb.ColumnMappings.Add("Order Date", "OrderDate");
                        scb.ColumnMappings.Add("Customer Name", "CustomerName");
                        scb.ColumnMappings.Add("Status", "Status");
                        scb.ColumnMappings.Add("Notes 1", "Notes1");
                        scb.ColumnMappings.Add("Notes 2", "Notes2");
                        scb.ColumnMappings.Add("Notes 3", "Notes3");
                        scb.ColumnMappings.Add("Delivery Address Name", "DeliveryAddressName");
                        scb.ColumnMappings.Add("Delivery Address Line 1", "DeliveryAddressLine1");
                        scb.ColumnMappings.Add("Delivery Address Line 2", "DeliveryAddressLine2");
                        scb.ColumnMappings.Add("Delivery Address Line 3", "DeliveryAddressLine3");
                        scb.ColumnMappings.Add("Delivery Address Line 4", "DeliveryAddressLine4");
                        scb.ColumnMappings.Add("Delivery Address Line 5", "DeliveryAddressLine5");
                        scb.ColumnMappings.Add("Carriage Net", "CarriageNet");
                        scb.ColumnMappings.Add("Carriage Tax Code", "CarriageTaxCode");
                        scb.ColumnMappings.Add("Carriage Nominal Code", "CarriageNominalCode");
                        scb.ColumnMappings.Add("Carriage Consignment", "CarriageConsignment");
                        scb.ColumnMappings.Add("Carriage Dept Code", "CarriageDeptCode");
                        scb.ColumnMappings.Add("Carriage Courier", "CarriageCourier");
                        scb.ColumnMappings.Add("Global Tax Code", "GlobalTaxCode");
                        scb.ColumnMappings.Add("Global Nominal Code", "GlobalNominalCode");
                        scb.ColumnMappings.Add("Global Dept Code", "GlobalDeptCode");
                        scb.ColumnMappings.Add("Global Details", "GlobalDetails");
                        scb.ColumnMappings.Add("Product Code", "ProductCode");
                        scb.ColumnMappings.Add("QuantityAllocated", "QuantityAllocated");
                        scb.ColumnMappings.Add("QuantityDelivered", "QuantityDelivered");
                        scb.ColumnMappings.Add("QuantityToDespatch", "QuantityToDespatch");
                        scb.ColumnMappings.Add("Quantity", "Quantity");
                        scb.ColumnMappings.Add("Unit Price", "UnitPrice");
                        scb.ColumnMappings.Add("Unit of Sale", "UnitOfSale");
                        scb.ColumnMappings.Add("Description", "Description");
                        scb.ColumnMappings.Add("DetailsText", "DetailsText");
                        scb.ColumnMappings.Add("Comment 1", "Comment1");
                        scb.ColumnMappings.Add("Comment 2", "Comment2");
                        scb.ColumnMappings.Add("Tax Code", "TaxCode");
                        scb.ColumnMappings.Add("Nominal Code", "NominalCode");
                        scb.ColumnMappings.Add("Dept Code", "DeptCode");
                        scb.ColumnMappings.Add("Line Information", "LineInformation");
                        scb.ColumnMappings.Add("Discount Amount", "DiscountAmount");
                        scb.ColumnMappings.Add("Discount Rate", "DiscountRate");
                        scb.ColumnMappings.Add("Project Ref", "ProjectRef");
                        scb.ColumnMappings.Add("Net", "Net");
                        scb.ColumnMappings.Add("ImportGuid", "ImportGuid");

                        scb.WriteToServer(reader);

                        transaction.Commit();
                    }
                            catch (Exception ex)
                    {
                        transaction.Rollback();
                        _logger.InsertLog(LogLevel.Error, "Sales Order Import Error", ex.Message);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }

========================

cheers, mark

[phatcher]

@joeg10 @firthy For issues like this it's much easier to fix if there's a full sample e.g. code, schema and an associated data file.

There is some new code which I've merged that might address this, so can you build locally and test please

[firthy]

Hi, Ive added a csv, the create table sql, and example code. cheers, mark lumen_bug.zip

[phatcher]

@firthy Thanks for that