Bulk insert - Boolean 1

[micang]

SQL 2005

Hi All,

I am importing a text file with the following code:

BULK INSERT  tblname
    FROM 'd:\data.txt' 
    WITH 
    ( 
        FIRSTROW = 2, 
        FIELDTERMINATOR = '|', 
        ROWTERMINATOR = '\n'
        )

It works fine, apart for 2 columns that are Boolean (bit) type.

The text file has the data for these 2 columns as 'True'\'False'.

If I change these 2 columns type to varchar (10), it imports no problem and for these columns it shows the actual words 'True'\'False'.

If however I have the type for these 2 columns as bit, it does not import and gives a:
"Bulk load data conversion error (type mismatch or invalid character for the specified codepage)" error message.

I have tried to look for info, but can't find if there is a certain procedure to import booleans as '1'/'0'?

Any info appreciated.

Thank you

Michael

 

[vbkris]

>>I have tried to look for info, but can't find if there is a certain procedure to import booleans as '1'/'0'?


hmm, not directly. you may have to do a workaround.

let the import happen in 2 EXTRA columns that are varchar.

updated your bit columns using the values in the varchar column and drop the extra columns...

Known is handfull, Unknown is worldfull

 

[micang]

Hi vbkris,

Thank you, that solution will do the trick.

Much appreciated.

Michael

 

[vbkris]

your welcome...

Known is handfull, Unknown is worldfull