irishm20
asked on
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?
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?
D B
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?
ASKER
Yes, they were. Do I need to make sure that my excel sheet has no formatting?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
That is "before" I define the actual transformation and do the column mappings.
ASKER
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.
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 :-(
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 :-(
ASKER
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.