May 15, 2020 at 9:02 pm
In rsreportserver.config file, how can I set CSV export to have a field delimiter of none So that I can extract a fixed length file? I tried keeping it empty but it gives commas after each field value. Also if possible, please provide the script to get fixed length file and some of the fields can have commas in between values.
I would really appreciate any input as my client is fully determined to use SSRS and not SSIS.
May 16, 2020 at 3:39 pm
You cannot define an export that will be a fixed width from SSRS - in order for a fixed width file to be generated you need to know the length of every field and pad every field to that length.
This cannot be defined as a possible export because that information will not be known at execution.
If you must use SSRS to export fixed width files then you need to do that in SQL. Depending on the structure of the output - you may need to include additional columns for sorting the data appropriately, but then you will display the single formatted column.
Now - you need to add a custom rendering for a text file, see this article: https://www.asr-solutions.com/2018/04/12/ssrs-custom-rendering-for-output-text-files/
With that, the user can then export to a text file and the report data will be fixed width because each row is already formatted as fixed width.
Or...you can create a single column in the report as an expression and build each field, padding out to the required length for each defined field - but that would be much harder to manage and maintain than doing it in SQL.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 17, 2020 at 4:21 am
Thank you Jeffery for the response. It make sense that either i go ahead with sql approach at db level or expression approach at SSRS level. Also using the config change implementation I am getting a txt file but when ever there is commas in any of the field I get double quote at the beginning and end of the line having comma.
Also for summary report, I am using vbcrlf function along with concatenation in SSRS expression and it adds double quotes at the beginning and end of the entire result set.
Could you please guide me on what extra I should add in the above config change which can express above scenarios?
Thank you!
May 17, 2020 at 3:02 pm
For a fixed width file - you should never have a comma in the data. If you have a comma then it is most likely because you have a column that is a combination of multiple columns (last name, first name). These should be separated into distinct fields in the fixed width file.
You could try adding the Qualifier to the extension:
<Qualifier></Qualifier>
The default is " and specifying a blank value should eliminate it...but the only way to be sure is to test it.
I don't have any idea on what your summary report is doing...or how it relates to this issue.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply