BCP query regarding null values

Question

Hi Guys,

I have discovered that when exporting a file to CSV from BCP, it is placing a null value in all the empty fields. Having read the documentation again, I can see that this is mentioned. Unfortunately my application wont recognise the null values. The documentation mentions using -k to maintain empty values during import, but it doesn't seem to work for export. Does anybody know of a way that I can set BCP to maintain the empty spaces as blank, and not null?

Answer 1

iGGt,

You might want to try using COALESCE or ISNULL in your query to export the data. You could have null values be converted to '' (empty string literal) so that the output would go from :

1, Hello World, NULL, NULL

To:

1, Hello World,,,


Hope this helps,
-h

Answer 2

cheers,

looking at it I think ISNULL may be the best bet. Could you tell me how it would fit into my statement please.

INSERT INTO table
SELECT
VARIOUS.FIELDS...., '"'+CONVERT(varchar(98),TEST.dbo.Place.notes)+'"',          ...........................VARIOUS.FIELDS
VARIOUS.JOINS
WHERE ......


The main thing I am trying to achieve is with the '"'+CONVERT(varchar(98),TEST.dbo.Place.notes)+'"' may or may not contain data, and so needs to insert something other than null if there is no data.

Answer 3

OK, I think Ive got it


ISNULL(fieldName,' '),

'"'+CONVERT(varchar(98),ISNULL(TEST.dbo.Place.notes,' '))+'"',

Answer 4

iGGt,

The example you have above this post should work. In this case you are replacing NULL values with a space ' ', depending on business logic this could also be 0, the word 'NULL', or any other values used to denote a blank or invalid entry.

Hope this helps,
-h

Answer 5

Cheers,

I was working on the basis that when I opened up the file in a Hex editor I could see that where there were blank fields in the table, SQL had inserted a null character (0x00), but after I opened/saved/closed the file Windows had replaced those null values with spaces (0x20).

Since the second file was accepted by my other application, I decided it made sense to replace all null values with spaces, as they would (to all extents and purposes) appear as blank fields.