Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
gmorey
Starting Member
2 Posts |
Posted - 2013-10-02 : 09:56:11
|
I need to be able to export the data from a table to a text file using sqlcmd. I need to be able to use Ctrl-A as a column separator. Is this doable, and if so, how? I am assuming the this is a value used by the -s option.I need to use a batch file to run the sqlcmd utility. |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-10-02 : 10:52:36
|
I don't think that Ctrl-A will result in any character (or code) in the resulting file - so there would be no separator. Unless you mean you want to use these 6 characters as the separator: "Ctrl-A"?Be One with the OptimizerTG |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-02 : 10:58:18
|
Even that may not be possible directly using sqlcmd's features. I recall having tried to use a multi-character column separator and failing miserably because sqlcmd wanted a single 8-bit character as the separator. |
|
|
gmorey
Starting Member
2 Posts |
Posted - 2013-10-02 : 11:05:59
|
I actually need to be able to do Control + A as a column seperator (the control key plus A). Not the literal "Ctrl-A". |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-10-03 : 04:36:51
|
quote: Originally posted by gmorey I actually need to be able to do Control + A as a column seperator (the control key plus A). Not the literal "Ctrl-A".
what is the ASCCI code for that ? KH[spoiler]Time is always against us[/spoiler] |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-03 : 08:42:16
|
KH, the ASCII code is 1. i.e., CHAR(1). But I have not figured out how to provide other than a single printable character as the value for parameter -s in sqlcmd. Perhaps you could - if you were to compose dynamic SQL and use SQLCMD from SSMS or call xp_cmdshell. But from the windows command line or from a windows bat file, how one would do that is not clear to me. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-10-03 : 09:58:32
|
one kluge...Might be to bcp out a statement (or view) where each row is a single value that is a concatenation all the columns with char(1) embedded between them:col1+char(1)+col2+char(1)[+...]Of course you'll need to convert all non-character columns to varchar as well as coalesce the nulls to empty strings.Just curious, why do you need to use this particular separator?Be One with the OptimizerTG |
|
|
|
|
|