November 11, 2010 at 5:43 am
Hello All
I am running a BCP command to create a csv file from a SQL table, but when I am outputting fields which are empty, the text file has a space inserted instead.
I need my file to look like this:
"test","","","data"
instead of:
"test"," "," ","data"
Could anyone point me in the right direction
Thanks
Bicky1980
November 11, 2010 at 1:32 pm
Are you sure the source table is NULL and not a space?
November 12, 2010 at 4:06 am
Thanks for the reply, its not actually null its an empty string...
November 12, 2010 at 9:44 am
Anybody able to help??
November 12, 2010 at 9:46 am
Please post your BCP command, as well as your form if you are using one.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
November 12, 2010 at 10:39 am
BCP Command as follows:
declare @bcpcommand nvarchar(1000)
SET @bcpcommand = 'bcp "select CHAR(34)+urn,title,firstname,lastname,company,ad1,ad2,ad3,ad4,ad5,postcode,
phone1,phone2,mobile,email,dateofbirth,source,sourceapp,cpsagent,cpsreference,cpspartner,reactiv_bdm,
reactiv_qmq_rdate,job_number_1,job_number_2,job_number_3,job_number_4,job_number_5,job_number_6,
job_number_7,job_number_8,job_number_9,job_number_10,job_number_11,job_number_12,job_number_13,
job_number_14,job_number_15,job_number_16,job_number_17,job_number_18,job_number_19,job_number_20+char(34)
from ASHLEY.DBO.outputtable " queryout "c:\outputs\reactive_med_'+replace(CONVERT(VARCHAR(10), GETDATE(), 104),'.','')+'.csv" -f c:\outputs\emailvision.fmt -T -k'
EXEC master..xp_cmdshell @bcpCommand
Also Format file as follows:
9.0
43
1 SQLCHAR 0 10 "\",\"" 1 URN Latin1_General_CI_AS
2 SQLCHAR 0 50 "\",\"" 2 TITLE Latin1_General_CI_AS
3 SQLCHAR 0 50 "\",\"" 3 FIRSTNAME Latin1_General_CI_AS
4 SQLCHAR 0 50 "\",\"" 4 LASTNAME Latin1_General_CI_AS
5 SQLCHAR 0 1 "\",\"" 5 COMPANY Latin1_General_CI_AS
6 SQLCHAR 0 50 "\",\"" 6 AD1 Latin1_General_CI_AS
7 SQLCHAR 0 50 "\",\"" 7 AD2 Latin1_General_CI_AS
8 SQLCHAR 0 50 "\",\"" 8 AD3 Latin1_General_CI_AS
9 SQLCHAR 0 50 "\",\"" 9 AD4 Latin1_General_CI_AS
10 SQLCHAR 0 50 "\",\"" 10 AD5 Latin1_General_CI_AS
11 SQLCHAR 0 50 "\",\"" 11 POSTCODE Latin1_General_CI_AS
12 SQLCHAR 0 50 "\",\"" 12 PHONE1 Latin1_General_CI_AS
13 SQLCHAR 0 50 "\",\"" 13 PHONE2 Latin1_General_CI_AS
14 SQLCHAR 0 50 "\",\"" 14 MOBILE Latin1_General_CI_AS
15 SQLCHAR 0 150 "\",\"" 15 EMAIL Latin1_General_CI_AS
16 SQLCHAR 0 50 "\",\"" 16 DATEOFBIRTH Latin1_General_CI_AS
17 SQLCHAR 0 3 "\",\"" 17 SOURCE Latin1_General_CI_AS
18 SQLCHAR 0 1 "\",\"" 18 SOURCEAPP Latin1_General_CI_AS
19 SQLCHAR 0 50 "\",\"" 19 CPSAGENT Latin1_General_CI_AS
20 SQLCHAR 0 10 "\",\"" 20 CPSREFERENCE Latin1_General_CI_AS
21 SQLCHAR 0 50 "\",\"" 21 CPSPARTNER Latin1_General_CI_AS
22 SQLCHAR 0 1 "\",\"" 22 BDM Latin1_General_CI_AS
23 SQLCHAR 0 1 "\",\"" 23 RDATE Latin1_General_CI_AS
24 SQLCHAR 0 1 "\",\"" 24 JOB_NUMBER_1 Latin1_General_CI_AS
25 SQLCHAR 0 1 "\",\"" 25 JOB_NUMBER_2 Latin1_General_CI_AS
26 SQLCHAR 0 1 "\",\"" 26 JOB_NUMBER_3 Latin1_General_CI_AS
27 SQLCHAR 0 1 "\",\"" 27 JOB_NUMBER_4 Latin1_General_CI_AS
28 SQLCHAR 0 1 "\",\"" 28 JOB_NUMBER_5 Latin1_General_CI_AS
29 SQLCHAR 0 1 "\",\"" 29 JOB_NUMBER_6 Latin1_General_CI_AS
30 SQLCHAR 0 1 "\",\"" 30 JOB_NUMBER_7 Latin1_General_CI_AS
31 SQLCHAR 0 1 "\",\"" 31 JOB_NUMBER_8 Latin1_General_CI_AS
32 SQLCHAR 0 1 "\",\"" 32 JOB_NUMBER_9 Latin1_General_CI_AS
33 SQLCHAR 0 1 "\",\"" 33 JOB_NUMBER_10 Latin1_General_CI_AS
34 SQLCHAR 0 1 "\",\"" 34 JOB_NUMBER_11 Latin1_General_CI_AS
35 SQLCHAR 0 1 "\",\"" 35 JOB_NUMBER_12 Latin1_General_CI_AS
36 SQLCHAR 0 1 "\",\"" 36 JOB_NUMBER_13 Latin1_General_CI_AS
37 SQLCHAR 0 1 "\",\"" 37 JOB_NUMBER_14 Latin1_General_CI_AS
38 SQLCHAR 0 1 "\",\"" 38 JOB_NUMBER_15 Latin1_General_CI_AS
39 SQLCHAR 0 1 "\",\"" 39 JOB_NUMBER_16 Latin1_General_CI_AS
40 SQLCHAR 0 1 "\",\"" 40 JOB_NUMBER_17 Latin1_General_CI_AS
41 SQLCHAR 0 1 "\",\"" 41 JOB_NUMBER_18 Latin1_General_CI_AS
42 SQLCHAR 0 1 "\",\"" 42 JOB_NUMBER_19 Latin1_General_CI_AS
43 SQLCHAR 0 1 "\r" 43 JOB_NUMBER_20 Latin1_General_CI_AS
\r
Thanks
Ash
November 12, 2010 at 12:19 pm
If possible fix the source table with an update statement that replaces the empty strings with NULL.
UPDATE MyTable
SET MyField = NULL
WHERE MyField = ''
You could use REPLACE in your Select statement
SELECT REPLACE(MyField,'',NULL)
Or a CASE statement
SELECT
CASE MyField WHEN '' THEN NULL ELSE MyField END
November 12, 2010 at 1:30 pm
Fantasic Chrissy321, works a treat, decided to update the source table prior to outputting via BCP!
Problem solved! 😀
November 12, 2010 at 2:05 pm
oh dear, 1 new problem now...This works fine, however on my last field which needed to have quote qualifiers not only has the 1st quote e.g. "hello. The Header records (which I have inserted as the first record) has the trailing quote. So I am guessing this is because the value isnt a null vale...
Any idea how to sort this?
November 12, 2010 at 2:19 pm
bicky1980 (11/12/2010)
oh dear, 1 new problem now...This works fine, however on my last field which needed to have quote qualifiers not only has the 1st quote e.g. "hello. The Header records (which I have inserted as the first record) has the trailing quote. So I am guessing this is because the value isnt a null vale...Any idea how to sort this?
Literally sort the data or sort it out, that is solve the problem.
Can you show some sample data illustrating the problem?
If you are text qualifying the fields which something contained within the fields then you should either change your quailifier or do the UPDATE/REPLACE/CASE thing to clean your data.
November 12, 2010 at 2:52 pm
Literally sort the data or sort it out, that is solve the problem.
What?
The data has been output like this:
"Hello","This","Is","What","I","Mean
I need this to look like this:
"Hello","This","Is","What","I","Mean"
The format file is as above, the bcp is as above.
After the previous issue, which sorted the space issue, I now have the last value in my SQL table as a null value. This I am guessing is why the last quote (specified in bcp as +char(34)) is not being output.
I hope this makes my problem clearer...
Thanks
Bicky
November 12, 2010 at 4:09 pm
When you concatenate 2 values and one is a null you will always get null so job_number_20+char(34)
will always be null if job_number_20 is null
Try this instead of job_number_20+char(34)
CASE job_number_20 WHEN = NULL THEN char(34) ELSE job_number_20+char(34) END
or
COALESCE(job_number_20+char(34),char(34))
November 14, 2010 at 7:56 am
Fantastic, used COALESCE(job_number_20+char(34),char(34)) and this has rectified the problem thanks for the help 😀
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply