The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

T-SQL: how to fill fields with spaces

Hi Everybody
It’s been a very long time since I’ve last posted.

Scenario:
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    

Problem:
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)
Seraph Send private email
Tuesday, February 13, 2007
 
 
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?).

For example:
SELECT CAST(sName AS CHAR(40))

Would pad the remainder with spaces.
EPiddy
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.
a former big-fiver Send private email
Tuesday, February 13, 2007
 
 
...and are you using a fixed or proportional font when you open the file? proportional fonts will NOT provide neat visual alignment.
a former big-fiver Send private email
Tuesday, February 13, 2007
 
 
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!!
Yeeeha!

Thank to everybody who replied. 
Have a great valentines day!
Seraph Send private email
Wednesday, February 14, 2007
 
 
O boy, what a waste of space.

You need lining up only for export, don't you?
Then why not

SELECT
  cast(name as char(40)),
  ...

and not touch the internals of the table?
Wojtek Send private email
Monday, February 19, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz