Excel Problem

Excel Problem

Post by Scott Schlue » Thu, 28 Mar 2002 01:23:35



Hi all,

I'm using ADO to import the values contained in an Excel file to a SQL
Server 2000 database.  The whole thing works fine except for one problem I'm
running into.  The "ITEM_NO" column contains mixed data type values, some
numeric, some text.  When I import, I get all of the data in the
spreadsheet, but in the ITEM_NO column, I only get data up to the first
erroneous field after which I get null values.  According to one of MSDNs
knowledge base articles, mixed data types when reading from Excel can cause
problems, which it is.  Their solution is that I must store the column as
text and if needed, convert the numbers to numbers in the app.  They also
say that it has nothing to do with Excel formattion options.  Any ideas on
how to solve this problem?

Thanks,

Scott Schluer

 
 
 

Excel Problem

Post by Douglas Laudenschlager [M » Thu, 28 Mar 2002 07:17:52


Scott,

The Excel ISAM driver is brutal about the mixed data types issue.

One almost-undocumented solution is to add "IMEX=1" to the Jet Provider
connection string, which opens the connection to Excel in "import mode" and
enforces the Registry setting, "ImportMixedTypes = Text". If you're writing
your own ADO code, it's easy to add this in the "Extended Properties"
portion of the connection string. (If you're using SQL Server DTS, it will
be a little harder to get at the Jet connection string.)

Will you please test this and see whether it resolves your issue? Best
wishes,

-Doug

Using ADO and ADO.Net with Excel: Resources and Known Issues
March 21, 2002

To locate a specific Microsoft Knowledge Base article online, use
http://support.microsoft.com/default.aspx?scid=kb;en-us;qNNNNNN
where "NNNNNN" represents the 6-digit article number
preceded by the "Q"

Basic
-----
Q257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA
Q303814 HOWTO: Use ADOX with Excel Data from Visual Basic or VBA
Q278973 SAMPLE: ExcelADO Shows How to Read/Write Data in Excel Workbooks
Q195951 HOWTO: Query and Update Excel Data Using ADO From ASP

Transferring Data into Excel
----------------------------
Q247412 INFO: Methods for Transferring Data to Excel from Visual Basic
Q295646 HOWTO: Transfer Data from ADO Data Source to Excel with ADO
Q246335 HOWTO: Transfer Data from ADO Recordset to Excel with Automation
Q319951 HOW TO: Transfer Data to Excel Using SQL Server DTS (pending)
Q306125 HOW TO: Import Data from SQL Server into Microsoft Excel

Known Issues (most recent listed first)
------------
Q319998 BUG: Querying Open Excel Worksheet Using ADO Causes Memory Leak
(pending)
Q316809 BUG: No ADO Connection Error When Excel Workbook Is Not Found
Q314763 FIX: ADO Inserts Data into Wrong Excel Columns
Q300948 BUG: Incorrect TABLE_TYPE Is Returned for Excel Worksheets
Q294410 ACC2002: Nulls Replaced w/ Next Field's Data Exporting to Excel
Q293828 BUG: Excel File Size Grows When You Edit ADO Recordset
Q288343 BUG: Excel ODBC Driver Disregards FirstRowHasNames/HDR Setting
Q246167 PRB: Collating Sequence Error Opening XLS as ADO Recordset
Q211378 XL2000: 'Could Not Decrypt File' Error with Password-Protected File
Q194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset (mixed
data types)
Q189897 XL97: Data Truncated to 255 Characters with Excel ODBC Driver (Rows
To Scan)

Net (most recent listed first)
----
Q318373 HOW TO: Use GetOleDbSchemaTable with Excel in Visual Basic .Net
Q317719 HOW TO: Display Data in Excel Using ASP.NET WebForm (pending)
Q316934 HOW TO: Use ADO.Net to Retrieve and Modify Records in Excel
Q316831 PRB: Cannot Configure Data Connections to Excel Files (pending)
Q316756 PRB: Error Using ADO.Net OleDbDataAdapter to Modify Excel Workbook
(pending)
Q311731 HOW TO: Query and Display Excel Data Using ADO.NET, VB .NET
Q307021 HOW TO: Transfer XML Data to Microsoft Excel 2002 with VB .NET
Q306572 HOW TO: Query and Display Excel Data Using ASP.NET, ADO.NET
Q306022 HOW TO: Transfer Data to an Excel Workbook Using Visual Basic .NET
Q302094 HOWTO: Fill/Retrieve Excel Data Using Arrays From VB .Net

ASP/Web (most recent listed first)
-------
Q317719 HOW TO: Display Data in Excel Using ASP.NET WebForm (pending)
Q307603 HOW TO: Write Binary Files to the Browser Using ASP.NET & VB
Q306572 HOW TO: Query and Display Excel Data Using ASP.NET, ADO.NET
Q285891 HOWTO: Use VB or ASP to Create an XML Spreadsheet for Excel 2002
Q278976 HOWTO: Transform Excel XML Spreadsheet for Server-Side Use
Q276488 HOWTO: Use ADODB.Stream to Read Binary Files to the Browser
Q257757 INFO: Considerations for Server-Side Automation of Office (Excel on
IIS)
Q199841 HOWTO: Display ASP Results Using Excel in IE with MIME Types
Q195951 HOWTO: Query and Update Excel Data Using ADO From ASP

SQL Server/DTS (most recent listed first)
--------------
Q319951 HOW TO: Transfer Data to Excel Using SQL Server DTS (pending)
Q306397 INFO: Use Excel with SQL Server Linked Servers and Distributed
Queries
Q306125 HOW TO: Import Data from SQL Server into Microsoft Excel
Q281517 PRB: Transfer of Data from Jet 4.0 OLEDB Source Fails w/ Error (DTS
& Excel)
Q236605 PRB: DTS Wizard May not Detect Excel Column Type for Mixed Data
Q231880 BUG: Import Wizard Fails if Excel File Open During Import/Export
Q207446 BUG: Cannot Import Excel 97 Spreadsheet with 256 or More Columns

ADO within Excel (most recent listed first)
----------------
Q291199 XL2002: "Invalid Use of New Keyword" Error Using ADODB Library
Q263498 BUG: Run-Time Error 5 Using Add Method of QueryTables Collection
Q244761 XL2000: How to Use ADO to Return Data to a ListBox or ComboBox
Q228633 OFF2000: "Catastrophic Failure" Error Running Samples.xls Macro
Q225059 XL2000: "Invalid Use of New Keyword" Error Using ADODB Library
Q215154 XL2000: Excel does not support OLE DB Data Links

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

 
 
 

Excel Problem

Post by Scott Schlue » Thu, 28 Mar 2002 07:49:54


Hi Doug,

Thanks for the tip, but no dice.  I'll check out some of the KB articles you
posted.  Thanks again.

Scott



Quote:

> Scott,

> The Excel ISAM driver is brutal about the mixed data types issue.

> One almost-undocumented solution is to add "IMEX=1" to the Jet Provider
> connection string, which opens the connection to Excel in "import mode"
and
> enforces the Registry setting, "ImportMixedTypes = Text". If you're
writing
> your own ADO code, it's easy to add this in the "Extended Properties"
> portion of the connection string. (If you're using SQL Server DTS, it will
> be a little harder to get at the Jet connection string.)

> Will you please test this and see whether it resolves your issue? Best
> wishes,

> -Doug

> Using ADO and ADO.Net with Excel: Resources and Known Issues
> March 21, 2002

> To locate a specific Microsoft Knowledge Base article online, use
> http://support.microsoft.com/default.aspx?scid=kb;en-us;qNNNNNN
> where "NNNNNN" represents the 6-digit article number
> preceded by the "Q"

> Basic
> -----
> Q257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA
> Q303814 HOWTO: Use ADOX with Excel Data from Visual Basic or VBA
> Q278973 SAMPLE: ExcelADO Shows How to Read/Write Data in Excel Workbooks
> Q195951 HOWTO: Query and Update Excel Data Using ADO From ASP

> Transferring Data into Excel
> ----------------------------
> Q247412 INFO: Methods for Transferring Data to Excel from Visual Basic
> Q295646 HOWTO: Transfer Data from ADO Data Source to Excel with ADO
> Q246335 HOWTO: Transfer Data from ADO Recordset to Excel with Automation
> Q319951 HOW TO: Transfer Data to Excel Using SQL Server DTS (pending)
> Q306125 HOW TO: Import Data from SQL Server into Microsoft Excel

> Known Issues (most recent listed first)
> ------------
> Q319998 BUG: Querying Open Excel Worksheet Using ADO Causes Memory Leak
> (pending)
> Q316809 BUG: No ADO Connection Error When Excel Workbook Is Not Found
> Q314763 FIX: ADO Inserts Data into Wrong Excel Columns
> Q300948 BUG: Incorrect TABLE_TYPE Is Returned for Excel Worksheets
> Q294410 ACC2002: Nulls Replaced w/ Next Field's Data Exporting to Excel
> Q293828 BUG: Excel File Size Grows When You Edit ADO Recordset
> Q288343 BUG: Excel ODBC Driver Disregards FirstRowHasNames/HDR Setting
> Q246167 PRB: Collating Sequence Error Opening XLS as ADO Recordset
> Q211378 XL2000: 'Could Not Decrypt File' Error with Password-Protected
File
> Q194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset (mixed
> data types)
> Q189897 XL97: Data Truncated to 255 Characters with Excel ODBC Driver
(Rows
> To Scan)

> Net (most recent listed first)
> ----
> Q318373 HOW TO: Use GetOleDbSchemaTable with Excel in Visual Basic .Net
> Q317719 HOW TO: Display Data in Excel Using ASP.NET WebForm (pending)
> Q316934 HOW TO: Use ADO.Net to Retrieve and Modify Records in Excel
> Q316831 PRB: Cannot Configure Data Connections to Excel Files (pending)
> Q316756 PRB: Error Using ADO.Net OleDbDataAdapter to Modify Excel Workbook
> (pending)
> Q311731 HOW TO: Query and Display Excel Data Using ADO.NET, VB .NET
> Q307021 HOW TO: Transfer XML Data to Microsoft Excel 2002 with VB .NET
> Q306572 HOW TO: Query and Display Excel Data Using ASP.NET, ADO.NET
> Q306022 HOW TO: Transfer Data to an Excel Workbook Using Visual Basic .NET
> Q302094 HOWTO: Fill/Retrieve Excel Data Using Arrays From VB .Net

> ASP/Web (most recent listed first)
> -------
> Q317719 HOW TO: Display Data in Excel Using ASP.NET WebForm (pending)
> Q307603 HOW TO: Write Binary Files to the Browser Using ASP.NET & VB
> Q306572 HOW TO: Query and Display Excel Data Using ASP.NET, ADO.NET
> Q285891 HOWTO: Use VB or ASP to Create an XML Spreadsheet for Excel 2002
> Q278976 HOWTO: Transform Excel XML Spreadsheet for Server-Side Use
> Q276488 HOWTO: Use ADODB.Stream to Read Binary Files to the Browser
> Q257757 INFO: Considerations for Server-Side Automation of Office (Excel
on
> IIS)
> Q199841 HOWTO: Display ASP Results Using Excel in IE with MIME Types
> Q195951 HOWTO: Query and Update Excel Data Using ADO From ASP

> SQL Server/DTS (most recent listed first)
> --------------
> Q319951 HOW TO: Transfer Data to Excel Using SQL Server DTS (pending)
> Q306397 INFO: Use Excel with SQL Server Linked Servers and Distributed
> Queries
> Q306125 HOW TO: Import Data from SQL Server into Microsoft Excel
> Q281517 PRB: Transfer of Data from Jet 4.0 OLEDB Source Fails w/ Error
(DTS
> & Excel)
> Q236605 PRB: DTS Wizard May not Detect Excel Column Type for Mixed Data
> Q231880 BUG: Import Wizard Fails if Excel File Open During Import/Export
> Q207446 BUG: Cannot Import Excel 97 Spreadsheet with 256 or More Columns

> ADO within Excel (most recent listed first)
> ----------------
> Q291199 XL2002: "Invalid Use of New Keyword" Error Using ADODB Library
> Q263498 BUG: Run-Time Error 5 Using Add Method of QueryTables Collection
> Q244761 XL2000: How to Use ADO to Return Data to a ListBox or ComboBox
> Q228633 OFF2000: "Catastrophic Failure" Error Running Samples.xls Macro
> Q225059 XL2000: "Invalid Use of New Keyword" Error Using ADODB Library
> Q215154 XL2000: Excel does not support OLE DB Data Links

> This posting is provided "AS IS" with no warranties, and confers no
rights.

> Are you secure? For information about the Microsoft Strategic Technology
> Protection Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.

 
 
 

Excel Problem

Post by Scott Schlue » Thu, 28 Mar 2002 07:57:21


Doug,

Your tip was only half of the soltuion (although your link to KB article
Q194124 provided the other half)!  I also had to edit the
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel registry entry
and change TypeGuessRows to a higher value so the ISAM driver would scan
more than the first 8 rows for sample data (the first 8 were all numeric).
Thanks!



Quote:

> Scott,

> The Excel ISAM driver is brutal about the mixed data types issue.

> One almost-undocumented solution is to add "IMEX=1" to the Jet Provider
> connection string, which opens the connection to Excel in "import mode"
and
> enforces the Registry setting, "ImportMixedTypes = Text". If you're
writing
> your own ADO code, it's easy to add this in the "Extended Properties"
> portion of the connection string. (If you're using SQL Server DTS, it will
> be a little harder to get at the Jet connection string.)

> Will you please test this and see whether it resolves your issue? Best
> wishes,

> -Doug

> Using ADO and ADO.Net with Excel: Resources and Known Issues
> March 21, 2002

> To locate a specific Microsoft Knowledge Base article online, use
> http://support.microsoft.com/default.aspx?scid=kb;en-us;qNNNNNN
> where "NNNNNN" represents the 6-digit article number
> preceded by the "Q"

> Basic
> -----
> Q257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA
> Q303814 HOWTO: Use ADOX with Excel Data from Visual Basic or VBA
> Q278973 SAMPLE: ExcelADO Shows How to Read/Write Data in Excel Workbooks
> Q195951 HOWTO: Query and Update Excel Data Using ADO From ASP

> Transferring Data into Excel
> ----------------------------
> Q247412 INFO: Methods for Transferring Data to Excel from Visual Basic
> Q295646 HOWTO: Transfer Data from ADO Data Source to Excel with ADO
> Q246335 HOWTO: Transfer Data from ADO Recordset to Excel with Automation
> Q319951 HOW TO: Transfer Data to Excel Using SQL Server DTS (pending)
> Q306125 HOW TO: Import Data from SQL Server into Microsoft Excel

> Known Issues (most recent listed first)
> ------------
> Q319998 BUG: Querying Open Excel Worksheet Using ADO Causes Memory Leak
> (pending)
> Q316809 BUG: No ADO Connection Error When Excel Workbook Is Not Found
> Q314763 FIX: ADO Inserts Data into Wrong Excel Columns
> Q300948 BUG: Incorrect TABLE_TYPE Is Returned for Excel Worksheets
> Q294410 ACC2002: Nulls Replaced w/ Next Field's Data Exporting to Excel
> Q293828 BUG: Excel File Size Grows When You Edit ADO Recordset
> Q288343 BUG: Excel ODBC Driver Disregards FirstRowHasNames/HDR Setting
> Q246167 PRB: Collating Sequence Error Opening XLS as ADO Recordset
> Q211378 XL2000: 'Could Not Decrypt File' Error with Password-Protected
File
> Q194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset (mixed
> data types)
> Q189897 XL97: Data Truncated to 255 Characters with Excel ODBC Driver
(Rows
> To Scan)

> Net (most recent listed first)
> ----
> Q318373 HOW TO: Use GetOleDbSchemaTable with Excel in Visual Basic .Net
> Q317719 HOW TO: Display Data in Excel Using ASP.NET WebForm (pending)
> Q316934 HOW TO: Use ADO.Net to Retrieve and Modify Records in Excel
> Q316831 PRB: Cannot Configure Data Connections to Excel Files (pending)
> Q316756 PRB: Error Using ADO.Net OleDbDataAdapter to Modify Excel Workbook
> (pending)
> Q311731 HOW TO: Query and Display Excel Data Using ADO.NET, VB .NET
> Q307021 HOW TO: Transfer XML Data to Microsoft Excel 2002 with VB .NET
> Q306572 HOW TO: Query and Display Excel Data Using ASP.NET, ADO.NET
> Q306022 HOW TO: Transfer Data to an Excel Workbook Using Visual Basic .NET
> Q302094 HOWTO: Fill/Retrieve Excel Data Using Arrays From VB .Net

> ASP/Web (most recent listed first)
> -------
> Q317719 HOW TO: Display Data in Excel Using ASP.NET WebForm (pending)
> Q307603 HOW TO: Write Binary Files to the Browser Using ASP.NET & VB
> Q306572 HOW TO: Query and Display Excel Data Using ASP.NET, ADO.NET
> Q285891 HOWTO: Use VB or ASP to Create an XML Spreadsheet for Excel 2002
> Q278976 HOWTO: Transform Excel XML Spreadsheet for Server-Side Use
> Q276488 HOWTO: Use ADODB.Stream to Read Binary Files to the Browser
> Q257757 INFO: Considerations for Server-Side Automation of Office (Excel
on
> IIS)
> Q199841 HOWTO: Display ASP Results Using Excel in IE with MIME Types
> Q195951 HOWTO: Query and Update Excel Data Using ADO From ASP

> SQL Server/DTS (most recent listed first)
> --------------
> Q319951 HOW TO: Transfer Data to Excel Using SQL Server DTS (pending)
> Q306397 INFO: Use Excel with SQL Server Linked Servers and Distributed
> Queries
> Q306125 HOW TO: Import Data from SQL Server into Microsoft Excel
> Q281517 PRB: Transfer of Data from Jet 4.0 OLEDB Source Fails w/ Error
(DTS
> & Excel)
> Q236605 PRB: DTS Wizard May not Detect Excel Column Type for Mixed Data
> Q231880 BUG: Import Wizard Fails if Excel File Open During Import/Export
> Q207446 BUG: Cannot Import Excel 97 Spreadsheet with 256 or More Columns

> ADO within Excel (most recent listed first)
> ----------------
> Q291199 XL2002: "Invalid Use of New Keyword" Error Using ADODB Library
> Q263498 BUG: Run-Time Error 5 Using Add Method of QueryTables Collection
> Q244761 XL2000: How to Use ADO to Return Data to a ListBox or ComboBox
> Q228633 OFF2000: "Catastrophic Failure" Error Running Samples.xls Macro
> Q225059 XL2000: "Invalid Use of New Keyword" Error Using ADODB Library
> Q215154 XL2000: Excel does not support OLE DB Data Links

> This posting is provided "AS IS" with no warranties, and confers no
rights.

> Are you secure? For information about the Microsoft Strategic Technology
> Protection Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.

 
 
 

Excel Problem

Post by Douglas Laudenschlager [M » Fri, 29 Mar 2002 03:21:27


Scott,

I'm glad to hear the IMEX setting resolved your issue. I need to remember
always to mention the "gotcha" with numeric data and the TypeGuessRows
registry entry. Thanks,

-Doug

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

 
 
 

1. Excel to Excel problem

I am trying to use a package I created in SQL Server DTS Designer and saved
as a VB bas file and then customized the VB. I am moving data from one Excel
spreadsheet to another. I have turned the DTS code into a VB procedure and
am calling it as part of a larger utility app. Basically, the app breaks up
a national Excel report into smaller regional Excel reports. I am passing a
strRegion argument to my DTS VB procedure and calling it from a loop outside
of the DTS code.The loop goes through each region and each time calls my DTS
VB procedure with the variable for that region number. The result of the
loop is a HoldBin.xls which contains a sheet for each regional report. Then,
my app moves those sheets into respective .xls's for posting on an intranet.

I am using Unitialize after Execute on the package at the end of each loop,
and I am also destroying my DTS objects at the end of each loop. And, I am
successfully moving the data and creating the separate sheets in HoldBin.xls
. The problem is that right after the DTS code executes, my app moves to a
formatting loop to format the new sheets one by one and then move them to
their respective regional xls's. It is in the beginning of this code that I
get an error when I try to do a Workbooks.Open ("HoldBin.xls") . I have
gotten several different errors here. I've gotten "Automation error - Server
threw back an error", and "Open method failed for Workbooks object", and
"RTE ... (80010108) ... The object invoked has disconnected from its
clients." The result is HoldBin.xls is corrupted. I cannot open it  because
of a memory conflict message.

I think that the problem lies in the fact that DTS is not letting go of the
xls file. I have tried the ReleaseConnection method for each of my
connections at after the Execute and before the Unitialize in my loop, but
it does not help. I have stopped my code and noticed that Excel.exe is
running in Task Manager after the DTS code is finished. What's even more
puzzling is that my code "as is" worked for several weeks!!! Maybe one out
of ten jobs failed with one of these errors, but 9 times out of 10, it
worked perfectly. However, two weeks ago, that reversed for some reason.
Now, rarely it works, usually when I first boot the machine.

Any ideas? There must be something about the DTS VB code that I'm missing or
could tighten up.

Gerald

2. SQL Mail Help

3. problem with excel - problems obtaining data

4. select by-exp

5. VB300DLL and Excel - problem

6. pgsql/src/backend/storage/buffer (s_lock.c)

7. exporting Access Table to EXCEL problem in VB6.0

8. MYSQL date question

9. SQL importing from Excel problem

10. Excel Problems!!!!

11. Export to Excel - problem with NULL

12. Excel Problem

13. Dimension and Excel problem