Exporting to csv files

[elmo29]

I have a cursor that scrolls through records using a group by statement and for each individual group it exports to a csv file all the records for that group and then emails it to a sales manager to check and then email onto a customer.

Since it is for customer use - I really need the csv file to have field names (so the customers know what information they are looking at) - which bcp doesn't do. Also I am using the pound symbol £ -which bcp doesn't like.

So, does anyone know if this is possible to do.

- can I run DTS in a stored procedure AND pass in variables to it i.e. the customerid and it will export a csv file

- can I modify bcp to do this

- Or is there another way??

Any help appreciated

 

[tlbroadbent]

I Use xp_sendmail email the output to the sales manager in csv format with headers.

exec master..xp_sendmail
@recipients = 'Sales Manager',
@subject = 'Sales Report',
@message = 'Please review the attached report and forward to customer when approived.',
@query = 'Select * From SalesTbl Where GroupID=' + @id,
@attachments = 'C:\temp\salesrpt.csv',
@attach_results = 'True',
@width = 256,
@separator = ','

Parameters mean:

@attachments - send results in file of this name
@attach_results - attach query results as a file
@width - set maximum row width for result to avoid line wrap
@separator - define the column separator

This can all be done in a stored procedure where you process the cursor to get the group ID.

Let me know if it works for you. Terry

X-) "I don't have a solution, but I admire your problem."

 

[elmo29]

Well that does work, but it sends out an email for each csv file ... when sales people are making hundreds of requests per day they aren't going to be too happy to get that quantity of emails. I have solved the problem by using osql and pkzip.

I see you are using a file name in @attachments, what does that do? does it save the @query under a specified name???
I thought @attachements could only be used to attach an existing file??

Also I am still getting the problem that osql (as bcp) cannot display the £ character in a normal csv and text file, only when I export it in unicode format - which sales managers and customers, being what they are, don't know how to use ..... any ideas??

thanks for your help

 

[tlbroadbent]

@attachments can send an existing file or when used in conjunction with @attach_results = 'True' send the results of the query in a file by that name. The file is saved on disk by that name rather than a temporary name created at time of execution. See xp_sendmail in SQL Books Online.

Have you considered creating a front-end program that would display the information as a form or report? The manager could review the information on screen and approve it for mailing by clicking a button or check box. Your SQL procedure could run regularly and mail approved reports directly to the customer.

Alternatively, the front-end program could create the report with headings and proper formatting, perhaps as RTF or another format that would allow use of the £ character. It could then send the report in an auto generated email message. Terry

;-) The single biggest challenge to learning SQL programming is unlearning procedural programming. -Joe Celko

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[elmo29]

I am going to be doing a front end program, but that takes time which I haven't got at the moment. for now I just wanted to do something to keep them quiet and stop them moaning - which it has. They aren't bothered about the £ being wrong, but being the fussy programmer I am I can't see any reason for my £ sign not to show properly.

My computer is set up as english and it doesn't seem to be asking for much to use a £,it is a commonly used character - well in england anyway. I have even tried passing it as a char value but that really messes things up.

Thank you for your help anyway