SSRS - exporting to csv issue

  • I have an SSRS report and have a field called "Ticket".

    "Ticket" is a VARCHAR(20) field and in my clients DB, they have a "Ticket" value of "00000000007" for example. I created an expression as such for this field in my report: =CStr(Fields!Ticket.Value)

    I preview my report and it previews the value as I want. The huge issue is when I export it out to CSV, excel changes this to "7" and drops off the other numbers. Has anyone "tricked" this issue and forced it to keep it as a text field on exporting? This is maddening to say the least as I want it to keep the field as Text and not have Excel change it on me and force issues.

  • This is really an Excel issue - but there is a simple trick to get Excel to treat it as a text value.  Prepend a single-quote to field - for example: '00000000007

    But - that will mess up the actual report and other exports.  So - to fix that you need to prepend the data when exporting to Excel and that can be accomplished using something like this: =IIF(Globals!RenderFormat.Name = "EXCELOPENXML","'","") & Fields!Ticket.Value

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • You cannot "trick" it; CSV is unformatted data.  Excel is trying to be helpful because it THOUGHT it was a numeric value.

    I don't think there is any way to convince Excel not to do that except MAYBE if you can manipulate the data at the source and put an appostrophe at the start, but I am not even sure that'll help.

    If you open the CSV up in Notepad, you will see that it is in fact a bunch of 0's followed by a 7, and not JUST a 7 like Excel is telling you.

  • Mr. Brian Gale wrote:

    You cannot "trick" it; CSV is unformatted data.  Excel is trying to be helpful because it THOUGHT it was a numeric value.

    I don't think there is any way to convince Excel not to do that except MAYBE if you can manipulate the data at the source and put an appostrophe at the start, but I am not even sure that'll help.

    If you open the CSV up in Notepad, you will see that it is in fact a bunch of 0's followed by a 7, and not JUST a 7 like Excel is telling you.

    I assumed this was an export to Excel - and not CSV.  But the same thing applies to CSV - you can use the 'trick' I showed prepending a single-quote and when it opens in Excel it will be treated as text.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • when it opens in Excel it will be treated as text.

    with a single quote at the beginning of it.

    which is not the intention, I guess.

    The way around is to save the file as .txt. Then when opening in Excel it will go through Import Wizard, where you can define the type of the column as a text.

    or use Libre Office instead of Excel - it opens .csv files via the Wizard as well.

    _____________
    Code for TallyGenerator

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply