Exporting to Excel from SQL Server 2005, Preceding Apostrophes in Output?

SQL Server 2005 SSIS... Exporting to Microsoft Excel Workbook/Worksheet

I am having trouble exporting information from SQL Server 2005 to Excel.  Some of the columns being output are preceded by an apostrophe.  Example:

Excel File:
perId  Amount  TranCd
1         '6000     '380
2         '8000     '390
3         '4000     '380

Any ideas on why this would be happening?  
irishm20Asked:
Who is Participating?
 
dbbishopCommented:
The columns should be formatted as numeric in Excel before you do you column mappings. I am not certain exactly how SSIS works, but that is the way DTS is. If I am exporting a numeric datatype to Excel and want it to retain numeric formatting, I have to format the column in Excel as numeric, not General or Text.
0
 
dbbishopCommented:
These are numeric fields that have been converted to char/varchar during the export. Were amount and TranCd defined as currency and numeric columns in Excel before you built the export function?
0
 
irishm20Author Commented:
Yes, they were.  Do I need to make sure that my excel sheet has no formatting?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dbbishopCommented:
That is "before" I define the actual transformation and do the column mappings.
0
 
irishm20Author Commented:
I've tried formatting the Excel Sheet first and then re-linking the file and then re-mapped, still apostrophes.  This "package" worked fine in MS Server 2000 and I  had no problems; now that we have to convert it to SQL Server 2005 we're having difficulties.  I have had to re-write the entire thing.  So still not sure what to do at this point.
0
 
dbbishopCommented:
I am not certain then at this point. Like I said, I'm still on 2000. I've heard LOTS of complaints about SSIS not being as friendly as DTS and having problems. Maybe this is one of them. Hopefully someone else with more experience with SSIS can pick this up. I tried!

I've got some pretty complex DTS packages (active-x scripts, looping, execute package, dynamic properties, etc.) so I actually dread the day we convert :-(
0
 
irishm20Author Commented:
Well it looks as if when I formatted the Excel worksheet it put the apostrophes in front of the column titles therefore when SQL Server looked at it and output the data, it followed the same format as the column heading.  So thanks for the solution!  One way or another it was right.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.