Solved

SQL Reporting Services 2005 Save as CSV Issues

Posted on 2006-11-03
4
498 Views
Last Modified: 2010-08-05
Hi there

I have a simple table in SQL 2005 Reporting Services, that I am saving out as a CSV file.  This is for importing into Sage.

I need the CSV file to firstly not have any headers, which I can't see a way of doing in SQL RS, as it takes the name from the properties of the table cell naem where the value is, but would be interested if anyone has any ideas.

Secondly, I need the CSV file to be comma seperated which it is, but I also need the saved CSV file to have quotes as the text qualifier.  Again there seems to be no option on this in RS.  So for this I have tried ="""" & field!fieldsname & """" which displays fine when rendered, but when saved as a CSV you get """Fieldname""","""fieldname2""" etc, so then instead I tried =chr(34) & field!fieldname & chr(34) and again I get in the rendered view "Fieldname1" but when saved to a CSV file I get """fieldname""","""fieldname2""".

It seems slightly mad that there is not somehow in RS to format the type of CSV file I want to save, can anyone help me?

Any help much appreciated

David
0
Comment
Question by:DavidGreenfield
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 18

Assisted Solution

by:chrismc
chrismc earned 500 total points
ID: 17874206
David,
The quotes issue, I'd embed that into the SQL query. SQL delimits with single quotes, so Select ' " ' + Field1 + ' " ' From ...
will work. (extra spaces between quotes are for clarity only, remove them from your real query).

I think CSV export was not a big feature for MS hence the lack of options.

Cheers
Chris
0
 

Author Comment

by:DavidGreenfield
ID: 17874530
Good idea, one I hadn't tried, but when exported as a CSV, RS does exactly the same thing and produces """Field1""","""Field2""" etc.

Also tries select char(34) + field1 + char34 as field1 from....

And that does the same.

Just also tried single quotes, and that works, I can put one single quote or two quotes in, but of course 2 single quotes does not equal a double quote.

I guess this just can't be done is RS, unless there are any other good ideas.  I guess only other alternative is to do it solely in SQL server.
0
 
LVL 18

Accepted Solution

by:
chrismc earned 500 total points
ID: 17948398
Have you considered using Integration Services. SSIS is geared up to do tasks like this. You can run it periodically as a job too.

Cheers
Chris
0
 

Author Comment

by:DavidGreenfield
ID: 17948537
Thanks for the reply I actually used bcp in the end, and that worked fine!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
How to increase the row limit in Jasper Server.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question