SQL Csv export has End of line character

It seems this is not possible in BCP utility but you may use another tool to reduce the output file size by removing the last two or three characters. This allows e.g. TRUNC: https://www.softpedia.com/get/System/System-Miscellaneous/Trunc.shtml

The same work can do FSUTIL DOS command. First retrieve the current file length by
FSUTIL file queryValidData YourFileName.ext

and then set the file size
FSUTIL file setValidData YourFileName.ext newSize

UPDATE:
FSUTIL requires administrative rights for setting the new file size... but even with admin rights it does not work for me.

Additional tool is SFK: http://stahlworks.com/dev/?tool=partcopy

What are you opening the output file with?

If I use Notepad, it does "appear" there is an extra line but in any other editor it doesn't show it.

If you don't have another editor, use FIND to count the lines (I used a file called q.txt):
find /c /v "" q.txt

Open up powershell and do a hex dump:
type q.txt | Format-Hex

count the lines in Powershell:
type q.txt | Measure-Object

Maybe some BCP versions do not add CRLF or whatever character is defined in -r parameter but I still see CRLF at the end of each line and this is correct behavior.

If the software does not process the CRLF in the last line properly then its removal seems to the only way.

>>at the end of each line and this is correct behavior.

At the end of each line is correct.  The question asked is about the last line of the file and an "extra" one.

No, I did not understand question this way. There is nothing extra, just the "EOL character on the last line of data".

The question is the software that opens the file to import it has issues with the "end of line" character on the last line.  The software that opens  is from a vendor of ours.    They want us to remove the character, I only see it when I open the file in excel as it is a CSV file. I have attached a screen shot.
Capture.PNG

OK, this is not extra EOL but something else… and such character does not generate BCP obviously. Could you open the CSV file in some editor which can display invisible characters? Possibilities are Notepad++ or some hexadecimal editor.

I opened in notepad ++ has SUB at the end.  I have attached the CSV file.

Is that data OK to post in an open forum?

I don't see anything "extra" ad the end.

That image still shows what might be considered "sensitive" data.  You might want to take a look and remove it if it does.

The SUB is an ascii 26:
http://www.asciitable.com/

I don't believe bcp by itself is creating it.  If you run the bcp command from the command line, is it there?

you are correct when run from command line straight to file nothing, when the below is run:   It appears.

exec master..xp_cmdshell 'copy \\eincftp\m$\Invoices\ENSINVOICEHEADER.csv + \\eincftp\m$\Invoices\ENSINVOICEBODY.csv  \\eincftp\m$\Invoices\INCINVOICE.csv'

Thank you very much this took care of it.   THANKS

LOL, we could tell it directly if you would mention you are using COPY command to generate the output...

I should have posted that with my question but I didn't sorry, again thanks to all!  great job.

In the future:  NEVER try to debug the entire process at once.

Break it down to the smallest components:
Test bcp command line first:  Removes stored procedure stuff.
Then test bcp inside the procedure.

Then you would have seen it wasn't in bcp so it HAD to be external to that.

Will Do thanks for all the help.   I jumped straight to bcp.