COMMA delimited TEXT FILE

[Ricky Cruz]

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!

[AMIT]

Re: COMMA delimited TEXT FILE

Hi ,

Suggested solution to your problems.

1) If there is a NULL column, the result of concatenating any value
with[color=blue]
> 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[/color]

Solution>> Use ISNULL T-SQL Function. In your case it will be
Select Filed1,Isnull(F ield2,''), Field3 From TableName. This Isnull
function will replace value of field if null to supplied argument that
is ''.

[color=blue]
> 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.[/color]
Solution>> For 2nd porblem, change date time field value to varchar.
Thus query will be Select Filed1,Isnull(F ield2,''), Cast (
DateTimeField3 as Varchar) From TableName.


Hope above hints help you.

Thanks, Amit


Ricky Cruz <spricks@bigfoo t.com> wrote in message news:<3ef90fb1$ 0$198$75868355@ news.frii.net>. ..[color=blue]
> 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![/color]