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\
I am in SQLCMD mode and I have the Query Options set to:
Results\Text\
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?
I have a SQL query that I need to export to my desktop using this command:
:OUT C:\Users\bedward1\Desktop\
I am in SQLCMD mode and I have the Query Options set to:
Results\Text\
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?
SQL
Last Comment
leflon
ASKER
I dont think I know how to use sqlcmd from the command line. I will need to look that up.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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?
Cheers
leflon
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?
Cheers
leflon
ASKER
Thanks leflon. No, I have not. I don't know how to access the command line.
Becky,
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.
leflon
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.
leflon
ASKER
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.
Becky,
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.
leflon
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.
leflon
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.