[dba-SQLServer]Apostrophes in exported spreadsheets

David,

If your sproc is not using the export wizard then you have to insert the
apostrophe where and when needed.  The export wizard knows about the
various datatypes of M$ products and can handle them appropriately.
Your sproc only knows what you tell it. <grin>

Charles Wortz
Software Development Division
Texas Education Agency
1701 N. Congress Ave
Austin, TX 78701-1494
512-463-9493
CWortz at tea.state.tx.us



-----Original Message-----
From: David Emerson [mailto:davide at dalyn.co.nz] 
Sent: Friday 2003 May 30 14:20
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Apostrophes in exported spreadsheets


Further testing has shown that it is only if SQL tables are exported
that 
the apostrophes appear, if I export direct from a sproc then they are
not 
included.

David

At 29/05/2003, you wrote:
>David,
>
>The apostrophe is an Excel, not an Access, specification for a text 
>field.  Thus Access doesn't need it, but Excel does, so Access puts it 
>in when you export text strings to Excel.  A string delimited by quotes

>also indicates a text field to Excel.
>
>Charles Wortz
>-----Original Message-----
>From: David Emerson [mailto:davide at dalyn.co.nz]
>Sent: Thursday 2003 May 29 14:25
>To: dba-sqlserver at databaseadvisors.com
>Subject: RE: [dba-SQLServer]Apostrophes in exported spreadsheets
>
>
>The thing is that it didn't happen when I had A97 linked to an A97 BE.

>Text cells just had the text in them without the apostrophes.
>
>Another unusual thing is that some text fields have apostrophes at the 
>beginning and end of the text, other text fields only have an 
>apostrophe at the beginning!
>
>David
>
>At 29/05/2003, you wrote:
> >David,
> >
> >That apostrophe tells Excel that what follows in the cell is to be 
> >treated as text, not numbers.  If you want the cell values to be 
> >treated as numbers, then you need to change the exporting field 
> >datatypes from varchar to numeric datatypes.
> >
> >Charles Wortz
> >-----Original Message-----
> >From: David Emerson [mailto:davide at dalyn.co.nz]
> >Sent: Wednesday 2003 May 28 21:17
> >To: dba-SQLServer at databaseadvisors.com
> >Subject: [dba-SQLServer]Apostrophes in exported spreadsheets
> >
> >When I use the following line in an ADP linked to SQL2K, any varchar 
> >fields have an apostrophe put at the front of the data -
> >
> >strTableName = "dbo.ttmpLoadAllocateExp"
> >
> >DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, 
> >strTableName, "C:\Sheet.xls
> >
> >Does any one know why, and how can it be avoided?
> >
> >
> >Regards
> >
> >David Emerson
> >DALYN Software Ltd
> >25b Cunliffe St, Johnsonville
> >Wellington, New Zealand
> >Ph/Fax (877) 456-1205