Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
joshb
Yak Posting Veteran
52 Posts |
 Posted - 2002-07-22 : 11:47:55
|
| I've created a DTS package to export some data to an excel spreadsheet. Everything works fine except when the data is put in excel a single quote (') is placed in front of each value (eg '462 or 'A value). Does anyone know how to eliminate this?SQL Server 2000 / Excel 97Thanks |
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-07-22 : 12:21:27
|
| joshb,I am guessing that the datatype of the table you are querying is a text type datatype (i.e. char, varchar, nchar, nvarchar). Therefore SQL Server is assuming these are text fields for excel instead of number fields. By putting a single quote in front of the value, it tells excel that this is a literal text so that Excel can set the cell formatting as text instead of the general formatting. If your datatypes are of one of the text datatypes, try casting the value to a int or float datatype in the Select statement.Let me know if this does not solve the problem or if the datatypes are of the float or int type and it is still putting them in a single quote format. Also, if you can provide your query and some sample data, then we could test it on our servers.Jeremy |
 |
|
|
joshb
Yak Posting Veteran
52 Posts |
Posted - 2002-08-01 : 11:57:03
|
| Actually the data type of the field I am most concerned about is real, although I have tried CASTing it as various different types. I think it may have something to do with how DTS recognizes the named range (created myself, not through the wizard) I have set as the destination, it is showing all the fields as varchar(255) regardless of how I have them formatted in excel or what values are in them.I have also tried using an activeX script to format the values before they go into excel but no luck.Any ideas on how to eliminate the apostrophes?Josh |
|
|
|
joshb
Yak Posting Veteran
52 Posts |
Posted - 2002-08-02 : 11:32:16
|
| This is really stumping me, hasn't anyone run into this before.Any suggestions/ideas would be appreciated!Josh |
|
|
|
|
|
|
|
|
DTS excel transformation