Error when CSV file contains empty data while SQLBulkCopy

[PGoRule]

I am getting the errors when my CSV file contains some fields as blank. At DB table these fields are allowed to keep null. But when WriteToServer of SqlBulkCopy executes it throws an error for the blank value that it cannot convert string to int or string to datetime.
How can we allow the blank values so that DB puts <NULL> for it?

The extract file is :
(int,int,int,datetime,int,int)
lot_type_fk,valid_on,status,modif_date,row_type,C001
3,20070401,5,,,997103


Sharing the best from my side...

--Prashant--

 

[AlexCuse]

Depending on whether or not the file contains headers, I think you could find something in the System.IO namespace that will allow you to either:

a.) check for file size > 0
or
b.) check for number of rows > 1

and only execute your bulk copy if the file passes this test.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[PGoRule]

Thanx Alex...
I have done that check before I read the CSV file.
But when the row contains empty field as:
3,20070401,5,,,997103
it throws an error that it cannot convert the string to respective column data type. In such a situation if the column is set to 'Accept Null'...the DB should replace <Null> for such columns having no data.
The above CSV can be imported by DTS very well to same table with empty data and DB replaces empty data to <Null>.
Then why not BulkLoad?


Sharing the best from my side...

--Prashant--

 

[phinoppix]

Hi, I haven't used the class but maybe you can try looking at the column(s) corresponding to the empty CSV column(s) actually contains DBNull value, not an empty string.

 

[PGoRule]

Thanx phinoppix,

While doing some RnD on this issue, got a solution...
When the CSV line is read, we split it to an Array of string on basis of Seperator.
So I just put a check, if any Token in that array contains null or empty value...replace it with DBNull.Value.
This solves the problem and puts <Null> in DB for that empty field.


Sharing the best from my side...

--Prashant--