Link to home
Start Free TrialLog in
Avatar of Becky Edwards
Becky EdwardsFlag for United States of America

asked on

Need settings to convert SQL Data File to Pipe delimited during export.

Using SQL Server Management Studio v17.4

I have a SQL query that I need to export to my desktop using this command:

:OUT C:\Users\bedward1\Desktop\Encount3_test_RunDt_20190415.txt

I am in SQLCMD mode and I have the Query Options set to:
                       Output Format:  Custom delimiter
                       Custom Delimiter:  |

However, my result when I open them in Notepad are full of blank spaces.  Every row in the file takes up multiple rows due to large white spaces.

Can someone direct me to whatever settings I do not have correct?
Avatar of leflon
Flag of Germany image

Hello Becky,

do you have to run the script in SQLCMD mode?

If I use sqlcmd from the command line, I would go for the parameters
-s "|" to set delimiter
-W to cut off trailing whitespaces

Haven't yet found how -W is set in SQLCMD mode.
Avatar of Becky Edwards


I dont think I know how to use sqlcmd from the command line.  I will need to look that up.
Avatar of leflon
Flag of Germany image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hello Becky,

thanks for the rating.

Just out of curiosity, have you been able to produce the correctly delimitied output using sqlcmd?
If so can you post the sqlcmd command?

Thanks leflon. No, I have not.  I don't know how to access the command line.

use Win+R (Windows Key + R) to open "Run" dialog. In there just enter cmd and press enter.
That should open up the command window, kind of text terminal. There you can try to enter the "sqlcmd -?" and again press enter.
That should run the sqlcmd executable and display the options for that command.

If you get that running, we can continue with the next steps. If you like.

It looks like I need to install SQL or SSMS on my laptop.  I have been accessing it through Citrix on my pc at work.  Since I do not have direct access to my keyboard at work, I will need to install SQL here at home on my laptop to test your instructions.

you run SSMS through Citrix? Running it on your laptop and connecting to your work PC?

If you can install SSMS on your local laptop, it should come with sqlcmd.
You can also install sqlcmd on it's own, well with some command line utilities. Check here.

And you can connect to the target DB from your laptop too?

I still haven't found the time to check if all this formatting can't be done in SSMS directly. Sorry for that.