SQL Server - can't import value 0 or 1 to bit column

Question

This seems like a duplicate question. But as I searched & viewed the related questions & answers, i still can't find the answer.

I am writing a loader program which imports data records from CSV file to a database table. Here is the demo.

The database table is something as below:

use Test;
create table BoolTest (Name varchar(20) not null, IsValid bit not null);
insert into BoolTest (Name, IsValid) values('xx', 1);
insert into BoolTest (Name, IsValid) values('yy', 0);

The loader program is like:

class Program
{
    static void Main(string[] args)
    {
        var csvFileContent = "Name,IsValid\r\naa,true\r\nbb,false";            
        var csvLines = csvFileContent.Split(new String[] { "\r\n" }, StringSplitOptions.None);
        var columnNames = csvLines[0].Split(',');

        var table = new DataTable();
        foreach (var columnName in columnNames)
        {
            table.Columns.Add(new DataColumn(columnName));
        }

        for (var n = 1; n < csvLines.Length; ++n)
        {
            var line = csvLines[n];
            var values = line.Split(',');
            table.Rows.Add(values);
        }

        var connectionString = "Data Source=localhost;Initial Catalog=Test;Integrated Security=True";
        using (var bulkCopy = new SqlBulkCopy(connectionString)) {
            bulkCopy.DestinationTableName = "BoolTest";

            bulkCopy.ColumnMappings.Add("Name", "Name");
            bulkCopy.ColumnMappings.Add("IsValid", "IsValid");

            bulkCopy.WriteToServer(table);
        }

        Console.WriteLine("Done");
        Console.Read();
    }
}

Everything is fine above.

But the problem is the CSV file is from 3rd party and the values for column 'IsValid' is 0 or 1 as below

Name,IsValid
aa,1
bb,0

When the loader program tries to load this CSV file, bulkCopy.WriteToServer(table) will throw out exception

The sql server I am using is Sql server 2014.

Any help will be appreciated. Thanks.

--- update ---

Thanks guys for the solutions. I will try to manually change 0/1 to false/true in loader program. But still want to know whether there is easy fix (like changing settings in SQL Server or SqlBulkCopy) for this issue.

I also tried sql in Sql Server Management Studio like

insert into BoolTest (Name, IsValid) values('aa', 1); -- this works as we expected

insert into BoolTest (Name, IsValid) values('aa', '1'); -- this also works to my surprise

Both works well. But not sure why SQL server rejects the bulk copy when the IsValid value is 0 or 1. And when we show the table data with 'select', the value for IsValid is actually 0 or 1.

@AbdulRasheed Yes, true/false is ok for the program. But the csv file is from another company and the value is 0/1. — Yang You, May 10 '16 at 05:14
@yyou Then you will need to tell them to send it with `true`/`false`, or you need to make your program replace the values as appropriate. — Rob, May 10 '16 at 05:22
Could you find a way to parse the String into an integer before insert? Am thinking the CSV reader is reading the values as strings instead of integers, e.g. "1" instead of 1 — Peter, May 10 '16 at 05:26
@Rob Thanks. Both are solutions for this issue. But what I am curious about is which part of procedures causes this issue - DataTable, SqlBulkCopy or SQL Server? — Yang You, May 10 '16 at 05:27
BulkCopy likely. This uses a special API that is fast - but bypasses a lot of logic SQL Server normally applies. Type conversions among them. — TomTom, May 10 '16 at 05:31

score 2 · Answer 1 · answered May 10 '16 at 05:31

2

It's because Boolean values get replaced by bit values on insert, to make our lives easier to fix this you just need to create a parsing method for converting bit to bool, simple enough. Should then work

answered May 10 '16 at 05:31

TechnicalTophat

1,655
1
15
37

score 1 · Answer 2 · answered May 10 '16 at 05:30

The following INSERT statement will work on a SQL level:

INSERT INTO BoolTest (Name, IsValid) VALUES ('xx', 1);
INSERT INTO BoolTest (Name, IsValid) VALUES ('yy', 0);

But when you are passing values through to your stored procedure or SQL statement, you cannot pass through a 0 or a 1 because it will go through as a 0 or a 1 (and accepted as that). It is going to need to be a true or a false that will be passed through.

I think this is where you split the line values:

var values = line.Split(',');

All that you need to do here is to test for a 0 and a 1, and then replace them with a false or a true respectively.

score 1 · Accepted Answer · answered May 10 '16 at 05:41

I think the error is caused by trying to push a String (cell value in the DataTable) into a BIT column, as bulk insert is not able to perform implicit conversions ("0" and "1" values into 0/1 BIT values).

One way is to manually define columns along with their actual type:

table.Columns.Add(new DataColumn("Name", typeof(String)));
table.Columns.Add(new DataColumn("IsValid", typeof(bool)));

and the cells construction to something like this:

for (var n = 1; n < csvLines.Length; ++n)
{
    var line = csvLines[n];
    var values = line.Split(',');

    var rowValues = new object[values.Length];
    rowValues[0] = values[0];
    // conversion to integer is required as Boolean will not accept the string
    rowValues[1] = Convert.ToBoolean(Convert.ToInt32(rowValues[1]));
}

SQL Server - can't import value 0 or 1 to bit column

3 Answers3