April 12, 2012 at 8:13 am
I've got a simple Stored Procedure which produces about 20-30 columns of data and a few hundred rows, some of the columns contain data with commas in.
The target output of the data is CSV, as such these commas mess things up. So, wrapping these fields in " ........ " seems to solve the problem. (For example: '"'+REPLACE(FieldName,'"','''')+'"' as FieldName)
Run the SP, save as CSV, try importing back into SQL via Flat File import and all is fine.
Now... create a report of this SP, so one can schedule the report to run say weekly and dump a CSV to a network share and things change.
Firstly if you preview the report before deploying there are THREE speech marks at each end of the strings = e.g. """This text""" when you open the CSV in notepad.
Deploy the report and view an exported CSV in notepad and you have two speech marks ""This text""
Trying to import as Flat File again with the CSV from the deployed report doesn't work - you get an error about embedded text qualifiers.
Interestingly, if you open the 'preview' CSV in Excel it has just one speech mark around the text "this text", if you open the deployed CSV in Excel it puts two speech marks JUST AT THE END! - e.g. This text""
I know Excel can try and 'interpret' things so I've just looked at the notepad side of things for now.
Has anyone encountered this behaviour before, if so, what did you do to get around it?
My thoughts were:
1. Change the delimiter from comma to pipe (but that would be a global change and I'd prefer not to do that)
2. Don't do the wrapping in quotes in T-SQL but within the report.
I'm not in the office/in front of a PC with SQL so I can't play but I'm trying to research ideas to solve it for tomorrow, any thoughts?
April 16, 2012 at 12:31 pm
I'd say that doing the delimiters in the report is probably your best option as you can test it on the report by stripping out speech marks and adding them back in in the same formula before you think about changing the stored proc.
April 17, 2012 at 3:38 am
I looked into it some more and it seems SSRS puts "" around cases where it's required, I think the problem may have been trying to import without specifying " as a text qualifier when someone was using the data.
SSRS does all sorts of odd things when you try to do it manually, so it's trying to be too clever, on this occasion I've just let it be clever!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply