Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1139 > unrolled thread

MSSMSE - Export results to CSV

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  MSSMSE - Export results to CSV "Cathy" <Cathy@Nospam.com> - 2012-06-21 19:24 +0100
    Re: MSSMSE - Export results to CSV Erland Sommarskog <esquel@sommarskog.se> - 2012-06-21 23:40 +0200
      Re: MSSMSE - Export results to CSV "Cathy" <Cathy@Nospam.com> - 2012-06-22 00:00 +0100
        Re: MSSMSE - Export results to CSV Erland Sommarskog <esquel@sommarskog.se> - 2012-06-22 11:33 +0200
          Re: MSSMSE - Export results to CSV "Cathy" <Cathy@Nospam.com> - 2012-06-22 18:48 +0100
            Re: MSSMSE - Export results to CSV Erland Sommarskog <esquel@sommarskog.se> - 2012-06-22 20:38 +0200
              Re: MSSMSE - Export results to CSV "Cathy" <Cathy@Nospam.com> - 2012-06-22 22:45 +0100
                Re: MSSMSE - Export results to CSV Erland Sommarskog <esquel@sommarskog.se> - 2012-06-23 11:28 +0200
                  Re: MSSMSE - Export results to CSV "Cathy" <Cathy@Nospam.com> - 2012-06-23 23:49 +0100

#1139 — MSSMSE - Export results to CSV

I regularly run a complex script manually in MSSMSE. The results shown in 
Grid I then right click on the top left corner of the grid and select "Save 
results as" and then save it as a CSV file.

The above works just as I want it to and the saved CSV file is just as I 
would expect to see it.

But I have to do this for a large number of queries on all databases on the 
server which is very time consuming.

I have tried saving the script and running it with osql
(sorry should have mentioned earlier this is still an old SQL2000 server)
But unfortunately the resultant CSV file seems to have a lot of blank spaces 
and wraps lines and just is not in the same CSV format as that exported in 
first step.

I am therefore wondering if there is not a way in which I could add 
something into the SELECT statement to save reults to CSV file and hope the 
results come out in the same format,

I have also tried altering settings to save results to file instead of GRID 
but this also comes out in a different format.

If anybody has tried to do this before and come accross a solution for this 
I will really appreciate it.

Regards
C
 

[toc] | [next] | [standalone]

#1145

Cathy (Cathy@Nospam.com) writes:
> I have tried saving the script and running it with osql
> (sorry should have mentioned earlier this is still an old SQL2000 server)
> But unfortunately the resultant CSV file seems to have a lot of blank
> spaces and wraps lines and just is not in the same CSV format as that
> exported in first step. 
> 
> I am therefore wondering if there is not a way in which I could add 
> something into the SELECT statement to save reults to CSV file and hope
> the results come out in the same format, 
 
You can use -h option to supress headers, -s to set the column separator and 
-w to control the width of the output. However, there will still be a lot of 
space. 

However, a better option for you may be BCP, at least as long as you 
don't want the header lines.

bcp "yourquerygoeshere" queryout outputfile.txt -c -t, -CRAW -T -S server

-T is for trusted connection. Use -U and -P if you use SQL Server 
authentication.

Beware that if there is comma in the data, bcp will not quote the value.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1146

I was just looking at BCP, struggled a bit, got most of it working in the 
end, but still not quite the same format.

I was in fact hoping there was a way I could specify outfile as can be done 
in MySQL but I dont seem to get this option in MSSMSE

WIll keep looking, there must be a way

C 

[toc] | [prev] | [next] | [standalone]

#1150

Cathy (Cathy@Nospam.com) writes:
> I was just looking at BCP, struggled a bit, got most of it working in the 
> end, but still not quite the same format.

So what was wrong with the format?
 
> I was in fact hoping there was a way I could specify outfile as can be
> done in MySQL but I dont seem to get this option in MSSMSE 
 
I don't know about MySQL, but SSMS is just a client tool for interactive
queries. 

There is also the Export/Import wizard which you find the program 
group for SQL Server.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1151

> So what was wrong with the format?

Well as you said
">Beware that if there is comma in the data, bcp will not quote the value."

Try to open the file exported via MSSMSE and the once created by BCP after 
doing this and you will note the difference

This is the bcp command I am using., try the same with

bcp "SELECT CategoryID, CategoryName, Description FROM 
[Northwind].[dbo].[Categories]" queryout outputfile.csv -c -t, -CRAW -T -S 
PCNAME\SQLEXPRESS

Results when opened up in Excel is very different and not acceptable.

I could use the wizard, but the idea is to get a script that I can run 
against many databases, to export the results individually whilst running. 
Saving file data manually is almost easier and quicker than using the 
wizard, unless I am missing a trick.

***RESULT of BCP***
1,Beverages,Soft drinks, coffees, teas, beers, and ales
2,Condiments,Sweet and savory sauces, relishes, spreads, and seasonings
3,Confections,Desserts, candies, and sweet breads
4,Dairy Products,Cheeses
5,Grains/Cereals,Breads, crackers, pasta, and cereal
6,Meat/Poultry,Prepared meats
7,Produce,Dried fruit and bean curd
8,Seafood,Seaweed and fish

*** RESULT OF MSSMSE***
CategoryID,CategoryName,Description
1,Beverages,"Soft drinks, coffees, teas, beers, and ales"
2,Condiments,"Sweet and savory sauces, relishes, spreads, and seasonings"
3,Confections,"Desserts, candies, and sweet breads"
4,Dairy Products,Cheeses
5,Grains/Cereals,"Breads, crackers, pasta, and cereal"
6,Meat/Poultry,Prepared meats
7,Produce,Dried fruit and bean curd
8,Seafood,Seaweed and fish

[toc] | [prev] | [next] | [standalone]

#1152

Cathy (Cathy@Nospam.com) writes:
> I could use the wizard, but the idea is to get a script that I can run 
> against many databases, to export the results individually whilst running. 
> Saving file data manually is almost easier and quicker than using the 
> wizard, unless I am missing a trick.
> 
> ***RESULT of BCP***
> 1,Beverages,Soft drinks, coffees, teas, beers, and ales
> 2,Condiments,Sweet and savory sauces, relishes, spreads, and seasonings
> 3,Confections,Desserts, candies, and sweet breads
> 4,Dairy Products,Cheeses
> 5,Grains/Cereals,Breads, crackers, pasta, and cereal
> 6,Meat/Poultry,Prepared meats
> 7,Produce,Dried fruit and bean curd
> 8,Seafood,Seaweed and fish
 
OK. This can be arranged with a format file:

9.0
3
1 SQLCHAR 0 0 ","      1 Description  ""
2 SQLCHAR 0 0 ",\""    2 CategoryName Latin1_General_CI_AS
3 SQLCHAR 0 0 "\"\r\n" 3 Description  Latin1_General_CI_AS

Then replace "-c -t," in the command with "-f format.fmt".

An alternative is to run the query as

SELECT CategoryID, CategoryName, quotename(Description, '"') 
FROM  [Northwind].[dbo].[Categories]

Actually, this is better, because this will also handle double quotes
in the data.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1153

This is brilliant
> SELECT CategoryID, CategoryName, quotename(Description, '"')
> FROM  [Northwind].[dbo].[Categories]

Only problem I have now is that bcp reads the quote as the end of the 
command. Is there a way to escape this?

bcp "SELECT CategoryID, CategoryName, quotename(Description, '"')FROM 
[Northwind].[dbo].[Categories]" queryout outputfile.csv

Thanks so much for your help to so far

C 

[toc] | [prev] | [next] | [standalone]

#1154

Cathy (Cathy@Nospam.com) writes:
> This is brilliant
>> SELECT CategoryID, CategoryName, quotename(Description, '"')
>> FROM  [Northwind].[dbo].[Categories]
> 
> Only problem I have now is that bcp reads the quote as the end of the 
> command. Is there a way to escape this?
> 
> bcp "SELECT CategoryID, CategoryName, quotename(Description, '"')FROM 
> [Northwind].[dbo].[Categories]" queryout outputfile.csv
 
Well, not BCP but the command-line parser. I should have thought about 
that, sorry. I think that you can use \ to escape the quote:

bcp "SELECT CategoryID, CategoryName, quotename(Description, '\"')FROM 
[Northwind].[dbo].[Categories]" queryout outputfile.csv

But it may be simpler to use

   quotename(Description, char(34))

instead, and evading the problem that way.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1155

Thank you so much for you help. I did try \" but that did not work. Was not 
aware of char(34)

Worked a treat.

Regards
C 

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web