How to Send a Query Result in CSV Format

How to send a query result in CSV format?

Adding @query_result_separator should do the trick.

EXEC sp_send_dbmail @profile_name='default',
@recipients='dev@null.com',
@subject=@SUB,
@body=@BODY,

@query= 'SELECT [MID],[HID],[MeC],[LC],[RowCDate]
FROM [JBC].[dbo].[Table1] WHERE RowCDate >= GETDATE()
',
@attach_query_result_as_file = 1,
@query_attachment_filename   = 'Results.csv',
@query_result_separator      = ','

Adding @query_result_no_padding = 1 might clean up the results a bit. All off the arguments can be found here

Export query result to .csv file in SQL Server 2008

1. Open SQL Server Management Studio
2. Go to Tools > Options > Query
   Results > SQL Server > Results To Text
3. On the far right, there is a drop
   down box called Output Format
4. Choose Comma Delimited and click OK

Here's a full screen version of that image, below

This will show your query results as comma-delimited text.

To save the results of a query to a file: Ctrl + Shift + F

Sending a query results in csv file - file name

You need to define the report name before calling the SP

declare @attachment varchar(200) = CONCAT('report_',convert(varchar(25),getdate() ,102),'.csv');

After this, you can replace:

,@query_attachment_filename = 'report.csv'

by something like:

,@query_attachment_filename = @attachment

The 102 will format the date as YYYY.MM.DD, for other formats see: docs

NOTE: Some format will contain illegal characters for filenames, like i.e. 101, which formats as mm/dd/yyyy. But the / is not valid within a filename under Windows.

How do you email a query result as a csv using sp_send_dbmail stored procedure with SQL?

The problem is that Excel doesn't understand the columns. To fix this you need to send instructions to Excel explaining the csv file.

The instruction needs to be the first part of the file, before the data so you have to create an alias for the first field in the query that contains the Excel instructions along with the actual name you want

The instruction is “sep=,”, to ensure that excel will understand comma field separator.

DECLARE @column1name varchar(50)

-- Create the column name with the instrucation in a variable
SET @Column1Name = '[sep=,' + CHAR(13) + CHAR(10) + 'Name]'
DECLARE @qry varchar(8000)

-- Create the query, concatenating the column name as an alias
SELECT @qry='set nocount on; SELECT TOP 10 c.CCustName ' + @column1name + 
         ' ,c.CID Id FROM MyDb.dbo.Customers c'

EXEC msdb.dbo.sp_send_dbmail
    @recipients='me@myself.com',
    @subject='CSV Extract', 
    @profile_name = 'Valid Profile',
    @body='See attachment',
    @query =@qry,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'CSV_Extract.csv',
    @query_result_separator = ',',
    @query_result_width = 32767,
    @query_result_no_padding = 1

These are the final results;

It's not perfect (Line two is annoying) but it is certainly good enough for my purposes

How can I output MySQL query results in CSV format?

From Save MySQL query results into a text or CSV file:

SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Note: That syntax may need to be reordered to

SELECT order_id,product_name,qty
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';

in more recent versions of MySQL.

Using this command, columns names will not be exported.

Also note that /var/lib/mysql-files/orders.csv will be on the server that is running MySQL. The user that the MySQL process is running under must have permissions to write to the directory chosen, or the command will fail.

If you want to write output to your local machine from a remote server (especially a hosted or virtualize machine such as Heroku or Amazon RDS), this solution is not suitable.

How to export my sql query result set to CSV format?

You can use the overloaded method for writeNext which accepts boolean as a parameter.

void writeNext(String[] nextLine, boolean applyQuotesToAll)

In this pass the boolean as false to ignore the quotes.

[EDIT]

You can also use the constructor to disable the quotes.

CSVWriter writer = new CSVWriter(outputfile, ',',
                                 CSVWriter.NO_QUOTE_CHARACTER,
                                 CSVWriter.DEFAULT_ESCAPE_CHARACTER,
                                 CSVWriter.DEFAULT_LINE_END);

http://opencsv.sourceforge.net/apidocs/com/opencsv/ICSVWriter.html#writeNext-java.lang.String:A-boolean-

---

Related Topics