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

Groups > comp.databases.ms-sqlserver > #1152

Re: MSSMSE - Export results to CSV

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

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

csiph-web