I have a table in Excel with about 4000 records which I need exported to an
Excel Spreadsheet. This has to be triggered via an ASP.NET app via the web.
I have been trying to use DTS but some wierd things happen.

1) All the numeric columns in the table are recorded as strings in Excel.
Thus we cannot do math functions on them.
2) The export always inserts a blank row before writing the data to the
worksheet.

Does anyone have some working code on moving data between SQL Server 2000
and Excel? VBScript or DTS would be fine. Thanks

We have a few articles about pitfalls in Excel using DTS but if you are in
ASP.Net and you simply want to move data to Excel then what makes you want
to do this using DTS as opposed to coding .Net?

Troubleshooting
(http://www.sqldts.com/default.aspx?222)


Also search on the site for Excel.



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Chris van den Heuvel" <vandench (AT) fireblade (DOT) ca> wrote

Allan:

The only thing I wanted to avoid was having to install Excel on the
webserver. If there is a way to do the export from .Net without referencing
the Excel object then I would love to know. That shoudl solve all the
problems.


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

All you should need is MDAC as you do need to use the Excel interface per se
do you?

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Chris van den Heuvel" <vandench (AT) fireblade (DOT) ca> wrote

I've been trying to use the ADO.NET OLEDBConnection and associated objects
to write to the excel file but to no avail. I have tried naming ranges, with
columns headers, ithout column headers...Nothing seems to work...That is why
I tried the DTS route...


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

USING ADO AND ADO.NET WITH EXCEL: Resources and Known Issues
June 2003

General
-------
Q326548 HOW TO: Use Jet OLE DB Provider 4.0 to Connect to ISAM Databases
http://support.microsoft.com/default...;EN-US;Q326548
Q257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA
http://support.microsoft.com/default...;EN-US;Q257819
Q303814 HOWTO: Use ADOX with Excel Data from Visual Basic or VBA
http://support.microsoft.com/default...;EN-US;Q303814
Q278973 SAMPLE: ExcelADO Shows How to Read/Write Data in Excel Workbooks
http://support.microsoft.com/default...;EN-US;Q278973
Q195951 HOWTO: Query and Update Excel Data Using ADO From ASP
http://support.microsoft.com/default...;EN-US;Q195951

Transferring Data into Excel
----------------------------
Q247412 INFO: Methods for Transferring Data to Excel from Visual Basic
http://support.microsoft.com/default...;EN-US;Q247412
Q295646 HOWTO: Transfer Data from ADO Data Source to Excel with ADO
http://support.microsoft.com/default...;EN-US;Q295646
Q246335 HOWTO: Transfer Data from ADO Recordset to Excel with Automation
http://support.microsoft.com/default...;EN-US;Q246335
Q319951 HOW TO: Transfer Data to Excel by Using SQL Server DTS
http://support.microsoft.com/default...;EN-US;Q319951
Q306125 HOW TO: Import Data from SQL Server into Microsoft Excel
http://support.microsoft.com/default...;EN-US;Q306125

Known Issues
------------
Q319998 BUG: Memory Leak When You Query Open Excel Worksheet with ADO
http://support.microsoft.com/default...;EN-US;Q319998
Q316809 BUG: No ADO Connection Error When Excel Workbook Is Not Found
http://support.microsoft.com/default...;EN-US;Q316809
Q314763 FIX: ADO Inserts Data into Wrong Columns in Excel
http://support.microsoft.com/default...;EN-US;Q314763
Q316475 PRB: "Operation Must Use an Updateable Query" Error Message
http://support.microsoft.com/default...;EN-US;Q316475
Q300948 BUG: Incorrect TABLE_TYPE Is Returned for Excel Worksheets
http://support.microsoft.com/default...;EN-US;Q300948
Q294410 ACC2002: Nulls Replaced w/ Next Field's Data Exporting to Excel
http://support.microsoft.com/default...;EN-US;Q294410
Q293828 BUG: Excel File Size Grows When You Edit ADO Recordset
http://support.microsoft.com/default...;EN-US;Q293828
Q288343 BUG: Excel ODBC Driver Disregards FirstRowHasNames/HDR Setting
http://support.microsoft.com/default...;EN-US;Q288343
Q246167 PRB: Collating Sequence Error Opening XLS as ADO Recordset
http://support.microsoft.com/default...;EN-US;Q246167
Q211378 XL2000: 'Could Not Decrypt File' Error with Password-Protected File
http://support.microsoft.com/default...;EN-US;Q211378
Q194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset (mixed
data types)
http://support.microsoft.com/default...;EN-US;Q194124
Q189897 XL97: Data Truncated to 255 Characters with Excel ODBC Driver (Rows
To Scan)
http://support.microsoft.com/default...;EN-US;Q189897

Known Issues - .Net-specific
----------------------------
Q316831 PRB: Cannot Configure Data Connection to Excel Files in VS .NET
http://support.microsoft.com/default...;EN-US;Q316831
Q316756 PRB: Error w/ ADO.NET OLEDbDataAdapter to Modify Excel Workbook
http://support.microsoft.com/default...;EN-US;Q316756

VB.Net
------
Q318373 HOW TO: Use GetOleDbSchemaTable with Excel in Visual Basic .NET
http://support.microsoft.com/default...;EN-US;Q318373
Q316934 HOW TO: Use ADO.NET to Retrieve and Modify Records in Excel
http://support.microsoft.com/default...;EN-US;Q316934
Q306022 HOW TO: Transfer Data to Excel Workbook Using Visual Basic .NET
http://support.microsoft.com/default...;EN-US;Q306022
Q302094 HOWTO: Fill/Retrieve Excel Data Using Arrays From VB .Net
http://support.microsoft.com/default...;EN-US;Q302094

Web/ASP
-------
Q319180 HOWTO: Transform Dataset to Spreadsheet XML for Microsoft Excel
http://support.microsoft.com/default...;EN-US;Q319180
Q276488 HOWTO: Use ADODB.Stream to Read Binary Files to the Browser
http://support.microsoft.com/default...;EN-US;Q276488
Q257757 INFO: Considerations for Server-Side Automation of Office
http://support.microsoft.com/default...;EN-US;Q257757
Q199841 HOWTO: Display ASP Results Using Excel in IE with MIME Types
http://support.microsoft.com/default...;EN-US;Q199841
Q195951 HOWTO: Query and Update Excel Data Using ADO From ASP
http://support.microsoft.com/default...;EN-US;Q195951

Web/ASP.Net - .Net-specific
---------------------------
Q317719 HOW TO: Export Data in DataGrid on an ASP. NET WebForm to Excel
http://support.microsoft.com/default...;EN-US;Q317719
Q311731 HOW TO: Query and Display Excel Data by Using ADO.NET, VB .NET
http://support.microsoft.com/default...;EN-US;Q311731
Q308247 HOW TO: Use ASP.NET to Query and Display Database Data in Excel
http://support.microsoft.com/default...;EN-US;Q308247
Q307603 HOW TO: Write Binary Files to the Browser Using ASP.NET & VB
http://support.microsoft.com/default...;EN-US;Q307603

SQL Server/DTS
--------------
Q321686 HOW TO: Import Data into SQL Server from Excel
http://support.microsoft.com/default...;EN-US;Q321686
Q319951 HOW TO: Transfer Data to Excel by Using SQL Server DTS
http://support.microsoft.com/default...;EN-US;Q319951
Q306397 HOWTO: Use Excel w/ SQL Linked Servers & Distributed Queries
http://support.microsoft.com/default...;EN-US;Q306397
Q306125 HOW TO: Import Data from SQL Server into Microsoft Excel
http://support.microsoft.com/default...;EN-US;Q306125
Q281517 PRB: Transfer of Data from Jet 4.0 OLEDB Source Fails w/ Error
http://support.microsoft.com/default...;EN-US;Q281517
Q257757 INFO: Considerations for Server-Side Automation of Office
http://support.microsoft.com/default...;EN-US;Q257757
Q236605 PRB: DTS Wizard May not Detect Excel Column Type for Mixed Data
http://support.microsoft.com/default...;EN-US;Q236605
Q231880 BUG: Import Wizard Fails if Excel File Open During Import/Export
http://support.microsoft.com/default...EN-US;Q231880t
Q207446 BUG: Cannot Import Excel 97 Spreadsheet with 256 or More Columns
http://support.microsoft.com/default...;EN-US;Q207446

XML
---
Q285891 HOWTO: Use VB or ASP to Create an XML Spreadsheet for Excel 2002
http://support.microsoft.com/default...;EN-US;Q285891
Q278976 HOWTO: Transform Excel XML Spreadsheet for Server-Side Use
http://support.microsoft.com/default...;EN-US;Q278976

XML - .Net-specific
-------------------
Q319180 HOWTO: Transform Dataset to Spreadsheet XML for Microsoft Excel
http://support.microsoft.com/default...;EN-US;Q319180
Q307021 HOW TO: Transfer XML Data to Microsoft Excel 2002 with VB .NET
http://support.microsoft.com/default...;EN-US;Q307021

----------------------------------------------------------------------------------------
This list is maintained and updated from time to time by DOUGLASL.
The main contributors of content are DOUGLASL (12 articles) and LORITU (10).
----------------------------------------------------------------------------------------
Disclaimer: This document is provided "AS IS" with no warranties, and
confers no rights.

"Chris van den Heuvel" <vandench (AT) fireblade (DOT) ca> wrote