[dba-SQLServer]Apostrophes in exported spreadsheets

David Emerson davide at dalyn.co.nz
Fri May 30 15:32:38 CDT 2003


Sorry Charles.  The problem is that apostrophes are being added when we 
don't want them!  See the bottom of the message for the code I am using.

David

At 30/05/2003, you wrote:
>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
>_______________________________________________



More information about the dba-SQLServer mailing list