January 23, 2013 at 12:22 pm
Hi,
I have a column that I am exporting from report builder to csv that is 16 digit long (1234567812345678). I can se it in this format in Report builder but when I export this column in csv format the column data changes to a scientific format.
How can I set it that when the file opens it is displayed it as it is.
I would appreciate your help.
Thank you.
January 24, 2013 at 12:28 am
Are you opening it with Excel?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 24, 2013 at 6:01 am
yes, I save it as a csv and then open using Excel.
January 24, 2013 at 8:28 am
That's just Excel making some assumptions about the data type. If you were to open the file in notepad, you'd see your data in the format you are expecting. You can fix it in Excel by highlighting the offending column, right click, Format cells... and choosing a better format, such as Number with 0 decimal places.
Edited to add, just re-read your question and realized you were asking how to automatically have it formatted correctly in Excel. I don't have an answer for that one...
January 24, 2013 at 8:45 am
agnetha_11 (1/24/2013)
yes, I save it as a csv and then open using Excel.
GoVelo is right, it's just Excel butchering the format. Open it with a decent text editor, such as the free notepad++, and you'll see the actual data.
Unfortunately, since you can't store any format information in a csv file, you cannot configure Excel to open it in another way.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 24, 2013 at 10:45 am
Some users want to filter or edit the spreadsheet. When I change the format from scientific notation to text or numeric some how the last digit in all the field in the column change to '0'
January 24, 2013 at 1:04 pm
agnetha_11 (1/24/2013)
Some users want to filter or edit the spreadsheet. When I change the format from scientific notation to text or numeric some how the last digit in all the field in the column change to '0'
Sounds like there is some rounding going on.
Another option would be to not just open the file with Excel, but to use the import data functionality of Excel. In the wizard you can set the datatype of the columns, which might help avoiding the scientific notation.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply