October 30, 2012 at 5:13 pm
I am using a BCP statement to output file to text so it can be uploaded to another application. However the extra line at the end of the file is causing issues.
I am using -T -c -S which is what someone else has told me to use. I'm guessing its the -c which needs to be changed, but I do not know what to change it to.
What parameters can I use to eliminate the extra line at the end of the file.
Any help would be greatly appreciated.
Thanks!
October 31, 2012 at 11:40 am
Can you please provide repro code including CREATE TABLE, INSERT statements to populate table and complete bcp command being used?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 31, 2012 at 12:04 pm
if you are selecting a row that is full of nulls, it would appear as an "extra" line in your file when BCP is finished.
here's a working example, assuming you have xp_cmdshell opened up.
on my sandbox, there's 58 tables, but the null makes 59 rows.
if i change the Order by, i can get teh "blank" row to show up at the top instead of the bottom.
EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT name As TbLName FROM SandBox.sys.tables UNION SELECT NULL FROM SandBox.sys.tables ORDER BY name DESC" queryout C:\Data\Objects.txt -t"|" -c -T '
Lowell
October 31, 2012 at 1:04 pm
That is one possibility if the query or table has only a single column and the file is defined as delimited. While NULL may amount to no value going to the file it will depend on the number of columns in the query or table and the defined delimiter, if any. With multiple columns going to the file, even with all columns being NULL, if the file is fixed-width the line will not be empty per se, it will be the proper width with all blanks. If there are multiple columns and a delimiter is defined then the delimiters will still be printed to the file.
Need more info...
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 31, 2012 at 2:02 pm
I'm an inserting all the values into a table with a single column. I am inserting header records into the table, and am doing a union with the actual data values. I include a blank row, between the header and the data which is showing up correctly in the file.
I then bcp out the entire table.
insert into test(fields)
select 'Submit=True'
union all
select 'Comments='
union all
Select ' '
UNION ALL
SELECT 'job_seeker_id,last_name,first_name,Date,Week_Start_Date,Cost_Center_Code'
UNION ALL
SELECT DISTINCT job_seeker_id + ', ' + last_name + ', ' + first_name + ', ' + CONVERT (varchar(10), date,101) + ', ' + CONVERT (varchar(10), week_start_date,101) + ', ' + Cost_Center_Code
FROM #upload_table
WHERE date IS NOT NULL
bcp qa.dbo.test out C:\Users\User\Desktop\Test\September_2012.txt -T -c -S USER
October 31, 2012 at 3:27 pm
You may be running into an ordering issue with bcp. Order is not guaranteed without an ORDER BY. See if something along these lines will work for you:
CREATE TABLE test(test_id INT IDENTITY(1,1), fields VARCHAR(8000));
insert into test(fields)
select 'Submit=True'
insert into test(fields)
select 'Comments='
insert into test(fields)
Select ' '
insert into test(fields)
SELECT 'job_seeker_id,last_name,first_name,Date,Week_Start_Date,Cost_Center_Code'
insert into test(fields)
SELECT DISTINCT job_seeker_id + ', ' + last_name + ', ' + first_name + ', ' + CONVERT (varchar(10), date,101) + ', ' + CONVERT (varchar(10), week_start_date,101) + ', ' + Cost_Center_Code
FROM #upload_table
WHERE date IS NOT NULL
bcp "select fields from qa.dbo.test order by test_id" queryout C:\Users\User\Desktop\Test\September_2012.txt -T -c -S USER
I bolded all code where I made a change to your original script.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 31, 2012 at 3:41 pm
Upon looking some more, ordering may not be your issue if the blank line in between the header and rest of the file is showing properly. I am thinking the issue is that you're not guarding against NULL in your SELECT statement when concatenating the string. If any of your columns are NULL, the entire string will be NULL and will be output as a blank line to the file:
SELECT DISTINCT job_seeker_id + ', ' + last_name + ', ' + first_name + ', ' + CONVERT (varchar(10), date,101) + ', ' + CONVERT (varchar(10), week_start_date,101) + ', ' + Cost_Center_Code
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 1, 2012 at 2:39 pm
So I tried just doing a select top 1 to see what the behavior is, and it still adds the extra line. The only data I have in the table is "comments", no spaces or special characters.
It returns one row of data and the the cursor is on the row below that. So it doesn't look like it's a data issue.
Is it expected that a new row would be added to the end of the data being retrieved?
Here's the BCP statement and the insert statement.
insert into test(fields)
select 'comments'
bcp "select top 1 fields from qa1.dbo.test" queryout C:Users\user\Desktop \Test\September_2012.txt -T -c -S user
Please let me know your thoughts.
Thanks!
November 1, 2012 at 3:08 pm
I cannot recreate the issue on 2008R2. I'll try it on 2012 later, but to make sure, what build # are you on?
bcp -v
should tell you
And when you say "below that", do you mean on the next line after the data? or are there actually 2 line breaks after your data?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 1, 2012 at 3:12 pm
It is on the next line after the data, there is only one return after the data.
So i'm getting:
Comments
(cursor here)
I am on version: 11.0.2100.60
I appreciate the help!
November 1, 2012 at 3:15 pm
outsider32 (11/1/2012)
It is on the next line after the data, there is only one return after the data.So i'm getting:
Comments
(cursor here)
I am on version: 11.0.2100.60
I appreciate the help!
does the comments field contain a CrLf?
i bet that's the issue right there...
do a quick SELECT CHARINDEX(CHAR(13),Comments), CHARINDEX(CHAR(10),Comments), '>' + Comments + '<'
to help visualize it.
Lowell
November 1, 2012 at 3:27 pm
outsider32 (11/1/2012)
It is on the next line after the data, there is only one return after the data.So i'm getting:
Comments
(cursor here)
I am on version: 11.0.2100.60
I appreciate the help!
The written word is so much fun 🙂
To my knowledge that's the intended behavior. By default bcp always appends a line break to every outputted data line per the default 'row delimiter'.
If you were seeing this (2 line breaks after the last line) then I would be worried:
Comments from last line in the file
(extra blank line)
(cursor here)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 14, 2016 at 11:54 am
I'm getting the same issue. I'm looping through a folder of files using #dirTree sql server 2012. Then for each file reading the data into a temp table that has one column and one row called blob. I can select it and see the data. then using bcp to select blob from the #table and reload the file. When it does that it adds a carrage return line feed to the end of the file. Each time i do it it adds another carrage return to the end. Is there a way to either stop this or somehow remove it. I havent been able to at this point.
here's my code
SET @sql2= 'SELECT @FileContents = BulkColumn
FROM OPENROWSET(BULK '''
+ @fullfilepath + ''' ,SINGLE_BLOB) x;'
EXEC Sp_executesql
@sql2,
N'@FileContents varchar(max) out',
@fullfilepathout out
--do replace code here
SET @filecontents = @fullfilepathout
PRINT @filecontents
-------------------------------------------------------------------------------------------------------------
--BEGIN - write sql variable to file
-------------------------------------------------------------------------------------------------------------
--firstly we create a global temp table with a unique name
SELECT @myspecialtemptable = '##temp'
+ CONVERT(VARCHAR(12), CONVERT(INT, Rand() * 1000000))
--then we create it using dynamic SQL, & insert a single row
--in it with the MAX Varchar stocked with the string we want
SELECT @command = 'create table [' + @myspecialtemptable
+ '] (MyID int identity(1,1), Bulkcol varchar(MAX))
insert into [' + @myspecialtemptable
+ '](BulkCol) select @filecontents'
EXECUTE Sp_executesql
@command,
N'@filecontents varchar(MAX)',
@filecontents
--then we execute the BCP to save the file
SELECT @command = 'bcp "select bulkcol from ['
+ @myspecialtemptable + ']' + '" queryout ' + '"'
+ @fullfilepath + '" '
--+ CASE WHEN @Unicode=0 THEN '-c' ELSE '-w' END
+ ' -c -T -S servername'
EXECUTE MASTER..Xp_cmdshell
@command
March 14, 2016 at 12:20 pm
i've uploaded the example output. Notice thextra carrage return line feed at the end.
March 14, 2016 at 12:23 pm
I'm going to agree with Orlando's lat post that this is the expected behavior.
{somedata} + {row delimiter}, which in this case is CrLf, puts the cursor on the next line.
Lowell
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply