Forum Discussion

Tim Hunter's avatar
Tim Hunter
Steel Contributor
Oct 27, 2020

Exporting to CSV file

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!

Business Apps
Integration Services
Reporting Services
  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Oct 28, 2020

    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.

     

    • Tim Hunter's avatar
      Tim Hunter
      Steel Contributor
      Oct 28, 2020

      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;

       

      • Tim Hunter's avatar
        Tim Hunter
        Steel Contributor
        Oct 28, 2020

        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

Resources