Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1524
  • Last Modified:

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?  
0
irishm20
Asked:
irishm20
  • 4
  • 3
1 Solution
 
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now