A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.
It’s been a very long time since I’ve last posted.
I have to write data from a table to a file.
Lets say the table looks like this:
Table name = Users
Field 1 = sName – varchar(50)
Field 2 = sTelephone – varchar(20)
The object is to get the user names and telephone numbers saved to a flatfile.
The T-sql code looks like this
Declare @sSQL as varchar(800)
Declare @sDBName as varchar(800)
Declare @sFilename as varchar(800)
set @sDBName = (Select [name] From master.dbo.sysdatabases Where dbid = (Select db_id()))--'DebtpackGSSC'
set @sFilename = 'c:\TestFile123.txt'
Set @sSQL = 'bcp "Select sName , sTelephone From '+ @sDBName + '..Users Order by sName" queryout ' + @sFilename + ' -c -k -S' + @@Servername +' -U<my user name> -P<my password>'
exec master..xp_cmdShell @sSQL
Now it all works fine - file is created and contains valid data, except that the data does not line up in the file created.
(Looks like it’s tab delimited, but it’s not, there are spaces between the values.)
How do I fill the names with spaces to make sure that all the telephone numbers line up? Is it perhaps a setting on the bcp side, or should my select-statement be changed?
(I know this is a very long description of the problem for such a seemingly simple question, sorry, I have trouble expressing myself)
There may be a BCP option to do this, but I think that if your sName and sTelephone were cast as CHAR (I'm assuming they are defined in your table as VARCHARs?).
SELECT CAST(sName AS CHAR(40))
Would pad the remainder with spaces.
Tuesday, February 13, 2007
On the bcp command line, -t is the field terminator flag. To get a tab-delimited field , it'd be -t\t.
That should be the default, though. Are you sure whatever you're using to review the file isn't converting tabs to spaces? That's an option in editors like TextPad or EditPad.
know that a tab-delimited file may not line up neatly depending on the length of the actual data, so the CAST AS CHAR suggested above may make more sense if visual alignment is important.
To Former-fiver, I'm using a program called Programmer's File Editor. It doesn't convert tabs to spaces, and uses ia fixed font.
I've Altered the table to char (it was varchar), and it solved my problem!!
Thank to everybody who replied.
Have a great valentines day!
This topic is archived. No further replies will be accepted.Other recent topics
Powered by FogBugz