May 13, 2021 at 4:32 pm
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.
May 13, 2021 at 8:16 pm
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
“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 13, 2021 at 8:17 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 13, 2021 at 8:24 pm
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
“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, 2021 at 8:47 pm
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply