468,291 Members | 1,608 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,291 developers. It's quick & easy.

COMMA delimited TEXT FILE

Hi,

On SQLServer 2000, I have a table with a following structure:

MYTABLE
col1 char,
col2 date,
col3 number

My Objective:
------------
Externally (from a command line), to select all columns and write the
output into a file delimited by a comma.

My method:
---------
1. Probably will use OSQL or BCP to do this.
2. Use the following syntax:
select RTRIM(col1) +','+ RTRIM(col2) +','+ RTRIM(col3)
from MYTABLE;

My 3 Problems:
-------------
1) If there is a NULL column, the result of concatenating any value with
NULL, is NULL. How can I work around this? I still want to record this
column as null. Something like say from the example above, if col2 is
null, would result to: APPLE,,5

2) The time format when querying the database is: 2003-06-24 15:10:20.
However, on the file, the data becomes: 24 JUN 2003 3:10PM. How can I
preserve the YYYY-MM-DD HH:MM:SS format? Notice that I also lost the
SS.

3) Which utility is better? BCP or OSQL?
For OSQL, it has a "-s" flag which gives me the option of putting a
column separator. But the result is:
"APPLE ,14 JUN 2003 , 5"
I don't need the extra space.
While for BCP, there is no column separator flag.

You will notice from my inquiry above that my background in SQLServer is
not very good.

Thanks in Advance!!

Regards
Ricky

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #1
1 13227
Hi ,

Suggested solution to your problems.

1) If there is a NULL column, the result of concatenating any value
with
NULL, is NULL. How can I work around this? I still want to record this
column as null. Something like say from the example above, if col2 is
null, would result to: APPLE,,5
Solution>> Use ISNULL T-SQL Function. In your case it will be
Select Filed1,Isnull(Field2,''), Field3 From TableName. This Isnull
function will replace value of field if null to supplied argument that
is ''.

2) The time format when querying the database is: 2003-06-24 15:10:20.
However, on the file, the data becomes: 24 JUN 2003 3:10PM. How can I
preserve the YYYY-MM-DD HH:MM:SS format? Notice that I also lost the
SS. Solution>> For 2nd porblem, change date time field value to varchar.
Thus query will be Select Filed1,Isnull(Field2,''), Cast (
DateTimeField3 as Varchar) From TableName.
Hope above hints help you.

Thanks, Amit
Ricky Cruz <sp*****@bigfoot.com> wrote in message news:<3e*********************@news.frii.net>... Hi,

On SQLServer 2000, I have a table with a following structure:

MYTABLE
col1 char,
col2 date,
col3 number

My Objective:
------------
Externally (from a command line), to select all columns and write the
output into a file delimited by a comma.

My method:
---------
1. Probably will use OSQL or BCP to do this.
2. Use the following syntax:
select RTRIM(col1) +','+ RTRIM(col2) +','+ RTRIM(col3)
from MYTABLE;

My 3 Problems:
-------------
1) If there is a NULL column, the result of concatenating any value with
NULL, is NULL. How can I work around this? I still want to record this
column as null. Something like say from the example above, if col2 is
null, would result to: APPLE,,5

2) The time format when querying the database is: 2003-06-24 15:10:20.
However, on the file, the data becomes: 24 JUN 2003 3:10PM. How can I
preserve the YYYY-MM-DD HH:MM:SS format? Notice that I also lost the
SS.

3) Which utility is better? BCP or OSQL?
For OSQL, it has a "-s" flag which gives me the option of putting a
column separator. But the result is:
"APPLE ,14 JUN 2003 , 5"
I don't need the extra space.
While for BCP, there is no column separator flag.

You will notice from my inquiry above that my background in SQLServer is
not very good.

Thanks in Advance!!

Regards
Ricky

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Elmo Watson | last post: by
1 post views Thread by John B. Lorenz | last post: by
5 posts views Thread by Yama | last post: by
9 posts views Thread by Bernie Yaeger | last post: by
4 posts views Thread by JustSomeGuy | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by Teichintx | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.