Saving a SSRS report as a CSV – extra line issues

Ok, so I have finally finished a report…Now, it is not all over – some adventure is just around the corner. The client wants to have the final product in a csv format and here comes the surprise: new and unexpected lines come up, out of nowhere:

the line with **Textbox** and the second line (row 3) should not be recorded. why, oh whyy?

Aaah, I have the anwer: apparently CSV sees commas, tabs and new lines as new lines (for new lines, not a surprise there but I don’t want it to go in a new line) and what we need to do to repair this, is to use the REPLACE string function. Now, I just can’t add comma or tab or new line – I will have to search the ASCII code for each of them and then add this in the t-sql query.

So, let’s see.

For each column I will have to add this REPLACE armour, somewhat like this:

SELECT REPLACE()

Wooow! I am just mind blown undefined So, apparently the ASCII code for TAB is….char(9). char(9)???????????????? this is this char(9)?? this is why I saw it in the queries and this is why I never knew, until now that is, what was the meaning for this char(9). Hmmmm…no wonder it was always used when that annoying issues when everything was messed up once the result of a query was pasted in Excel. All the lines were compromised. That waaas it. Ok, and now I remember there is another CHAR used, along with the 9…CHAR(13). Let’s see what CHAR(13) translates into.

I am buffled again: what is actually “line feed” and “carriage return”?

Ok, I feel like I am going into the rabbit hole, I find something and then I have to research that something and then that something leads to a different something and so on. undefined

I have arrived here:

I can just write **SELECT** ASCII (‘what do you want to see’) and it will give you the ASCII code for (**ATENTION**) just the w character – so, it only goes to the extreme left

Ok, but comma, new line, tab – these are not regular characters, well yes, we have a solution for this, also – use UNICODE. Instead of ASCII, just add UNICODE. Let’s go in SSMS and check this out by ourselves:

And we have a winner:

Now let’s write the actual REPLACE.

I could write it like this: SELECT REPLACE (column, CHAR(9)+CHAR(13)+CHAR(44). Off to the report, be back with the new CSV version – to see if this is repaired.

And again, something is not quite right:

Cristina, replace with what? 😦

Aaah:

and now it works: Aleluia, Evrika! correct line: REPLACE(column, CHAR(9) + CHAR(13) + CHAR(44), ”)

Ok, let’s proceed with the rest of the query and see if it works. By the way, I am using http://poorsql.com/ to re-arrange my query in a more readable fashion.