Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

DTS Export to Excel

0 views
Skip to first unread message

Anand Vadakkath

unread,
Dec 7, 2001, 2:24:19 PM12/7/01
to
I have successfully implemented a DTS export from SQLServer 7 to Excel using
DTS inside a Visual Basic activeX component. I have the following problem:

The DTS export inserts a appostrophe (') before every character or text data
type in the Excel file. This does not actually affect the spreadsheet
unless you try and perform operations on it using another dataextraction or
manipulation routine.

Any pointers would be appreciated.


David SheafferMS

unread,
Dec 12, 2001, 6:24:17 PM12/12/01
to
Use the DTS Export Wizard and use SQL Server as the source and Excel 8.0
as the destination. Then use copy table. You will see that DTS will not
import a quote around text columns in the excel spreadsheet. Save this
package and then use the knowledge base article below.
INF: ScriptPkg Tool Generates DTS Object Model Code
[sqlserver]ID: Q239454

Then compare how scriptpkg.exe scripted out the dts package and compare
with the properties etc you used in your VB Active x component.

dsh...@onlinemicrosoft.com
This posting is provided 'AS IS' with no warranties, and confers no rights.

Anand Vadakkath

unread,
Jan 9, 2002, 7:55:24 PM1/9/02
to

But it does put appostrophes before any text. You have to place your
cursor on the cell to see the appostrophe.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Darren Green

unread,
Jan 12, 2002, 7:08:52 AM1/12/02
to
In article <Oess9FXmBHA.2168@tkmsftngp05>, Anand Vadakkath
<aav...@hotmail.com> writes

>
>But it does put appostrophes before any text. You have to place your
>cursor on the cell to see the appostrophe.
>

I believe this is a limitation of the Jet OLE-DB provider. Never found a
way round it myself.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com


harry sinclair

unread,
Jan 12, 2002, 2:50:51 PM1/12/02
to
I've only seen the last 2 items in this thread so apologies if I've got hold
of the wrong end of the stick. The apostrophe you see in the formula bar
when you activate a cell is just formatting information that Excel uses and
is not part of the cell contents. It means that the cell is left justified.
If you centre the cell you'll see ^ before the text. Right justify shows ".
Regards Harry

"Darren Green" <darren...@reply-to-newsgroup-only.uk.com> wrote in
message news:0NrJlrDU...@sqldts.com...

David SheafferMS

unread,
Jan 15, 2002, 5:50:05 PM1/15/02
to
Good discussion. Anand, let me know if you want me to do any further
research on the topic.
0 new messages