Exporting a report from Report Builder to CSV

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » Exporting a report from Report Builder to CSV...

Exporting a report from Report Builder to CSV

Rate Topic

Display Mode

Topic Options

Author

Message

agnetha_11

Posted Wednesday, January 23, 2013 12:22 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 14, 2013 3:14 PM
Points: 13, Visits: 73

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.

Post #1410749

Koen Verbeeck

Posted Thursday, January 24, 2013 12:28 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:30 AM
Points: 9,410, Visits: 6,495

Are you opening it with Excel?

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1410932

agnetha_11

Posted Thursday, January 24, 2013 6:01 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 14, 2013 3:14 PM
Points: 13, Visits: 73

yes, I save it as a csv and then open using Excel.

Post #1411098

GoVelo

Posted Thursday, January 24, 2013 8:28 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 1:44 PM
Points: 15, Visits: 339

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...

Post #1411167

Koen Verbeeck

Posted Thursday, January 24, 2013 8:45 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:30 AM
Points: 9,410, Visits: 6,495

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.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1411180

agnetha_11

Posted Thursday, January 24, 2013 10:45 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 14, 2013 3:14 PM
Points: 13, Visits: 73

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'

Post #1411263

Koen Verbeeck

Posted Thursday, January 24, 2013 1:04 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:30 AM
Points: 9,410, Visits: 6,495

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.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1411318

« Prev Topic | Next Topic »

Permissions