November 4, 2003 at 8:32 pm
I've been asked to supply data to a 3rd party. Requirement given was for a ".csv" format file, with headings in the first row, and all data to be enclosed in quotes.
(Note - that brings up another problem. Enclose a string of digits in quotes and Excel will assume that the field is numeric. There seems to be no way of telling Excel that it is actually a text field.)
After much heartache, tearing out of hair, etc, I've managed to get the file into the format required - BUT WITH ONE PROBLEM - there is "trailer" record which I can't get rid of. The trailer record is a "tab" followed by lots of spaces. Does anyone know how to get rid of it?
There has been a fair bit of interest expressed in the problems with attachments produced by xp_sendmail - mainly the "white space" that you can't eliminate.
My "work around", while not being very elegant (that's an understatement!) may spark a few ideas with others who are having similar problems.
The approach I've used is to generate the report as a single TEXT field.
Any help/comments would be appreciated.
Thanks
Michael
.. and here's the code excerpts ..
CREATE PROCEDURE sp_xxx_test3 AS
declare@quotechar(1),
@commachar(1),
@datanvarchar(1000)
declare@time_stamp_achar(20)
select@quote=char(34),@comma=','
... set time_stamp
delete from yyyKTemp.dbo.mike_xxx_test
-- Write the column headings
insert into yyyKTemp.dbo.mike_xxx_test
select
@quote + 'Customer' +@quote +@comma
+@quote + 'Order_Number' +@quote +@comma
+@quote + 'Reference_Number' +@quote +@comma
+@quote + 'Shipping_Line_Booking_Number' +@quote +@comma
+@quote + 'Depot' +@quote +@comma
+@quote + 'Shipping_Line' +@quote +@comma
+@quote + 'Vessel' +@quote +@comma
etc, etc
-- Cursor to get the actual data
declarec1 cursor for
select
char(10) +
@quote + 'yyy' +@quote +@comma
+@quote + rtrim(cust_po) +@quote +@comma
+@quote + ord_key +@quote +@comma
+@quote + rtrim(booking) +@quote +@comma
+@quote +@quote +@comma
+@quote +@quote +@comma
+@quote + rtrim(vessel_name) +@quote +@comma
+@quote + rtrim(text) +@quote +@comma
+@quote + rtrim(port_name)+' '+replace(convert(char(11),dt_cutoff,106),' ','-') +@quote +@comma
etc, etc
-- Get the data and write it to the text file (append to the end)
open c1
fetch c1 into @data
while @@fetch_status = 0
begin
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(rest) from yyyKTemp.dbo.mike_xxx_test
updatetext yyyKTemp.dbo.mike_xxx_test.rest @ptrval null 0
fetch c1 into @data
end
close c1
deallocate c1
select rest from yyyKTemp.dbo.mike_xxx_test
GO
And here's the code in "Agent" that calls it up
declare @dlen int,
@filename nvarchar(50),
@stamp char(19)
set@dlen = (select datalength(rest) from yyyKTemp.dbo.mike_xxx_test)
set@stamp = convert(char(19),getdate(),120)
set@filename = 'ShipmentSchedule_'
+substring(@stamp,1,4)+substring(@stamp,6,2)+substring(@stamp,9,2)
+substring(@stamp,12,2)+substring(@stamp,15,2)+substring(@stamp,18,2)
+'.csv'
exec master.dbo.xp_sendmail
@recipients='<recipient deleted>',
@copy_recipients='<various names deleted>',
@message='Shipping schedule attached',
@query='exec rep_yyyk1.dbo.sp_xxx_test3',
@attachments=@filename,
@ansi_attachment=True,
@attach_results=TRUE,
@separator='',
@subject='ShipmentSchedule',
@width=@dlen,
@no_header=TRUE
Note .. @separator='' is to stop a preceding space from being entered!!
November 7, 2003 at 8:00 am
This was removed by the editor as SPAM
November 7, 2003 at 5:27 pm
I was fighting the trailing blank problem just the other night, so I appreciate your example. I have no idea on the trailing line.
There's also always DTS as an option, it's not much harder and a bit easier to control output formats.
November 9, 2003 at 1:30 am
DTS would have probably been a bit more awkward for the requirements - csv with quotes around headings as well as data - plus having to have a timestamp as part of the name.
As it has turned out, the trailing blanks haven't presented a problem to the 3rd party - but I'd still like to know how to get of them!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply