Exporting to CSV file

[Tim Hunter]

We are exporting some data from SQL to a CSV file which is then emailed. This whole process works great. However, the CSV file has ------------------ on Line 2 of the CSV file. Is there a way to remove Line 2 and all the --------------?

Thanks!

[#2]

Here is what I am talking about in Line 2, see below.

[#3]

@Tim Hunter, how do you export the data to a CSV file?

The result looks like an export from SSMS grid/Query Window, right? Use better the Import/Export wizard for the export.

[#4]

@olafhelper 

We are using the Database Mail feature with a Stored Procedure to automatically email the data in a CSV file. So it is not just exporting the CSV file. The CSV file is sent in an email directly from SQL. The stored procedure works perfectly and emails with no issue. The only problem I have is that Line 2.

Here is our Stored Procedure

Select @recordCount = ISNULL(count(*),0) from invoiceheader 
where ivh_billto = 'PILKNO' 
and ivh_printdate >= dateadd(day,-7, getdate())

IF (@recordCount > 0)
begin

DECLARE @FileName varchar(35)
SELECT @FileName = 'Wynne-' + convert(nvarchar(MAX), dateadd(day,-7, getdate()), 10) + ' thru ' + convert(nvarchar(MAX), dateadd(day,-1, getdate()), 10) + '.csv'
EXEC msdb..sp_send_dbmail
 @profile_name = 'Pilot',
 @recipients = 'xxxx@xxx.com;xxx@xxx.com',
 --@from_address = 'xxx@xxx.com',
 --@reply_to = 'xxx@xxx.com',
 @subject = 'Freight Invoices',
 --@body = 'Test CSV output',
 @query = 'EXEC [dbo].[wynne_pilotinvoice]',
 @execute_query_database = 'TMWSUITE',
 @attach_query_result_as_file = 1,
 @query_result_separator ='	',
 @query_result_header =1,
 @query_result_width=32767,
 @query_attachment_filename = @FileName
end
else
 begin

      EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Pilot',
       @recipients = 'xxx@xxx.com;xxxx@xxx.com', 
            @BODY = 'No Invoices Found', 
            @subject = 'Pilot SQL Job'

End
End;

[#5]

@olafhelper 

Here is the store procedure that builds the CSV file.

USE [TMWSUITE]
GO
/****** Object:  StoredProcedure [dbo].[wynne_pilotinvoice]    Script Date: 10/28/2020 8:31:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[wynne_pilotinvoice]
AS
BEGIN



select 
i.ivh_invoicenumber as [Carrier Invoice #]
,convert(nvarchar(MAX),i.ivh_billdate , 1) as [Date of Invoice]
,i.ivh_ref_number as [Pilot Company Order#]
,c.cmp_name as [BILL To: Business Entity]
,c.cmp_address1 as [BILL TO: Business Entity Address]
,ct.cty_name as [Terminal City]
,c1.cmp_address1 as [Terminal Address]
,f.fgt_supplier as [Lifted Supplier]
,( select top 1 ref_number 
	from referencenumber 
	where ref_type = 'BL#'
	and i.ord_hdrnumber = ord_hdrnumber) as [BOL#]
,i.ivh_totalvolume as [Lifted Quanity]
,convert(nvarchar(MAX),i.ivh_shipdate , 1) as [Lift Date]
,RIGHT(c2.cmp_name,3) as [Pilot Retail Location Number]
,c2.cmp_name as [Pilot Retail Location Name]
,c2.cmp_address1 as [Pilot Location Address]
,i.ivh_quantity as [Delivered Quanity]
,convert(nvarchar(MAX),i.ivh_deliverydate , 1) as [Delivery Date]
, i.ivh_splitbill_flag as [Split Load]
,f.fgt_description as [Lifted Product]
,i.ivh_rate as [Carrier Base Freight Rate]
,(	select SUM(ivd_charge) 
	from invoicedetail	
	where i.ivh_hdrnumber = ivh_hdrnumber 
	and cht_itemcode = 'FSCP') as [Fuel Surcharge]
,(	select SUM(ivd_charge) 
	from invoicedetail	
	where i.ivh_hdrnumber = ivh_hdrnumber 
	and cht_itemcode NOT IN ( 'FSCP','LHV')) as [Accesorial Charges Total]
,i.ivh_totalcharge as [Total Invoice]
from invoiceheader i
LEFT JOIN company c on i.ivh_billto = c.cmp_id
LEFT JOIN company c1 on i.ivh_shipper = c1.cmp_id 
LEFT JOIN company c2 on i.ivh_consignee = c2.cmp_id
LEFT JOIN city ct on i.ivh_origincity = ct.cty_code
LEFT JOIN city ct1 on i.ivh_destcity = ct1.cty_code
Left JOIN legheader l on i.ord_hdrnumber = l.ord_hdrnumber
LEFT JOIN freightdetail f on l.stp_number_rstart = f.stp_number
where i.ivh_billto = 'PILKNO' 
and i.ivh_printdate >= dateadd(day,-7, getdate()) 
order by i.ivh_billdate, i.ivh_invoicenumber

end

[#6]

Hi @Tim Hunter --

One method for creating the csv with a result header with no lines is to generate the column names in the query section of sp_send_dbmail.  So for instance:

 @query = N'
			SET NOCOUNT ON
			SELECT ''RegionID'' AS RegionID, ''RegionDescription'' AS RegionDescription
			UNION ALL
			SELECT 
				CAST(RegionID AS varchar(10)),RegionDescription
			FROM
				TestDatabase01..mailtest'

Also, set the @query_result_header = 0.  The contents of the CSV should appear similar to this:

One recommendation I might make is that since you are using an existing stored procedure, considering sending the results of the stored procedure to a temp table, the writing a SELECT statement similar to the one shown above to produce and send the CSV.  Take care.