osql and 'real' csv format

I want to use osql to generate the query report in comma separated format
(csv), but even though I specified output file as report.csv, the data in the
file is not comma separated, instead, it's just like what we see when we do
query with SQL QUERY ANALYZER with "results in text" menu seletect. for
example, suppose the query is select column1, column2 from table1, and I
expect the data in .csv file as
r11,r12
r21,r22
r31,r32
instead, I get:
header1 header2
-- --
r11 r12
r21 r22
r31 r32
my statement is:
"c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -o
c:\mydb\report.csv
what am i missing here and how to get report in 'real' csv format?
Appreciate your help and time on this, THANKS!
You may try something like
[script]
bcp "SELECT au_fname, au_lname FROM pubs..Authors ORDER BY au_lname"
queryout c:\Authors.txt -t , -r \n -c -SYourServerName -T
[/script]
And take a look at this article "Showdown-bcp vs. DTS"
http://www.windowsitpro.com/Articles...rticleID=19760
Cristian Lefter, SQL Server MVP
"gongzuo" <gongzuo@.discussions.microsoft.com> wrote in message
news:A7B5CE27-E2C1-4BCE-9B24-7EBF0588C921@.microsoft.com...
>I want to use osql to generate the query report in comma separated format
> (csv), but even though I specified output file as report.csv, the data in
> the
> file is not comma separated, instead, it's just like what we see when we
> do
> query with SQL QUERY ANALYZER with "results in text" menu seletect. for
> example, suppose the query is select column1, column2 from table1, and I
> expect the data in .csv file as
> r11,r12
> r21,r22
> r31,r32
> instead, I get:
> header1 header2
> -- --
> r11 r12
> r21 r22
> r31 r32
> my statement is:
> "c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -o
> c:\mydb\report.csv
> what am i missing here and how to get report in 'real' csv format?
> Appreciate your help and time on this, THANKS!
|||Try this
"c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -s"," -o
c:\mydb\report.csv
"gongzuo" wrote:

> I want to use osql to generate the query report in comma separated format
> (csv), but even though I specified output file as report.csv, the data in the
> file is not comma separated, instead, it's just like what we see when we do
> query with SQL QUERY ANALYZER with "results in text" menu seletect. for
> example, suppose the query is select column1, column2 from table1, and I
> expect the data in .csv file as
> r11,r12
> r21,r22
> r31,r32
> instead, I get:
> header1 header2
> -- --
> r11 r12
> r21 r22
> r31 r32
> my statement is:
> "c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -o
> c:\mydb\report.csv
> what am i missing here and how to get report in 'real' csv format?
> Appreciate your help and time on this, THANKS!
|||Try this
"c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -s"," -o
c:\mydb\report.csv
"gongzuo" wrote:

> I want to use osql to generate the query report in comma separated format
> (csv), but even though I specified output file as report.csv, the data in the
> file is not comma separated, instead, it's just like what we see when we do
> query with SQL QUERY ANALYZER with "results in text" menu seletect. for
> example, suppose the query is select column1, column2 from table1, and I
> expect the data in .csv file as
> r11,r12
> r21,r22
> r31,r32
> instead, I get:
> header1 header2
> -- --
> r11 r12
> r21 r22
> r31 r32
> my statement is:
> "c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -o
> c:\mydb\report.csv
> what am i missing here and how to get report in 'real' csv format?
> Appreciate your help and time on this, THANKS!
|||it works, Thanks!
one more question:
how to generate a 'real' .xls format report using the same method?
or what's the col_separator character for an .xls format?
[i changed the generated .csv to .xls and the data are not align with
the columns in .xls file; I changed the '.csv' to '.xls' in the statement
only to generated the same in wrong format.
"msdnbuddy" wrote:
[vbcol=seagreen]
> Try this
> "c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -s"," -o
> c:\mydb\report.csv
> "gongzuo" wrote:
|||it works, thanks!
one more: what's the colomn-separator character for an .xls format?
i changed the report generated by the above statement from 'report.csv'
to 'report.xls' and the data are not align with the columns in the file;
I changed the '.csv' to '.xls' in the statement and only get the similar
wrong .xls format]
"msdnbuddy" wrote:
[vbcol=seagreen]
> Try this
> "c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -s"," -o
> c:\mydb\report.csv
> "gongzuo" wrote:
|||The problem that was causing the columns to not align was that the column
width of the output file was (by default) 80 cols. This can be corrected
with the -w switch
"c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -s"," -w
500 -o
c:\mydb\report.csv
Depending on how many columns are returned in your select statement, I would
suggest that you increase or decrease the width. I did a "select * from
orders" on the northwind db and widht of 500 resulted in a well formatted
..xls file. Give it a try.
Cheers.
"gongzuo" wrote:
[vbcol=seagreen]
> it works, thanks!
> one more: what's the colomn-separator character for an .xls format?
> i changed the report generated by the above statement from 'report.csv'
> to 'report.xls' and the data are not align with the columns in the file;
> I changed the '.csv' to '.xls' in the statement and only get the similar
> wrong .xls format]
> "msdnbuddy" wrote:
|||Thanks !
another problem :
using -s","
I couldn't get the whole date_time , for example, for the date_time is
2005-04-20 14:38:36.000, I only get 38:36:0
I did "select BirthDate from employees" on the nothwind db and get 00:00:0
for all the 9 records.
appreciate your help and time!
"msdnbuddy" wrote:
[vbcol=seagreen]
> The problem that was causing the columns to not align was that the column
> width of the output file was (by default) 80 cols. This can be corrected
> with the -w switch
> "c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -s"," -w
> 500 -o
> c:\mydb\report.csv
> Depending on how many columns are returned in your select statement, I would
> suggest that you increase or decrease the width. I did a "select * from
> orders" on the northwind db and widht of 500 resulted in a well formatted
> .xls file. Give it a try.
> Cheers.
> "gongzuo" wrote:
|||I too am having this date problem. Also, can osql use tabs as delimiters? -s
"\t" doesn't work.
"gongzuo" wrote:
[vbcol=seagreen]
> Thanks !
> another problem :
> using -s","
> I couldn't get the whole date_time , for example, for the date_time is
> 2005-04-20 14:38:36.000, I only get 38:36:0
> I did "select BirthDate from employees" on the nothwind db and get 00:00:0
> for all the 9 records.
> appreciate your help and time!
> "msdnbuddy" wrote: