Column display null while exporting from Excel Through DTS

Column display null while exporting from Excel Through DTS

Post by Niraj Kumar Sing » Wed, 26 Jun 2002 15:16:56



Dear Sir/Madam,
    I am trying to export data from Excel Sheet (Excel
2000) into SQL Server - 2000 database using DTS. One
column of execl sheet contain numeric and alpha numeric
value .Here only  observed strange behaviour of DTS
package. I have around 200 rows in my excel sheet . IF
first row of excel sheet contain Numeric value in a
particular column ( For Example 83) and Second row of that
particluar column contain AlphaNumeric or Alphabatic value
( For Example either '4X' OR 'AU' ) then DTS package
export only numeric value and ignore Alphanumeric value or
Alphabatic value but if it encounter any Numeric value
then it export numeric value.
Case - 1      
      COL1
ROW-1   83
ROW-2   4X
ROW-3   AU
ROW-4   83

After Exporting data through DTS the column wiil
contain '83' ONLY

CASE - 2
        COL1
ROW-1   4X
ROW-2   AU
ROW-3   83
ROW-4   AU

After Exporting data through DTS the column wiil
contain '4X' AND 'AU' ONLY .

I suspect it is DTS BUG.

Could u pls help me solve this problem .

Thanx & Warm Regards
NIRAJ

 
 
 

Column display null while exporting from Excel Through DTS

Post by Douglas Laudenschlager [M » Thu, 27 Jun 2002 02:07:19


Niraj,

The Excel driver determines the data type of a column based on the
"majority" datatype in the rows it scans as a sample -- this used to be 8,
now it appears (from the Registry) to be 25. Anyway, the driver returns
NULL for values that don't match the datatype.

One solution is to open your package in Disconnected Edit mode, locate the
"Extended Properties" entry for the Excel connection, and add "IMEX=1" to
force "import mode". For more information, please see:

Q194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q194124

Best wishes,

-Doug

Using ADO and ADO.Net with Excel: Resources and Known Issues
May 24, 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

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
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
Q316831 PRB: Cannot Configure Data Connections to Excel Files
Q316809 BUG: No ADO Connection Error When Excel Workbook Is Not Found
Q316756 PRB: Error Using ADO.Net OleDbDataAdapter to Modify Excel Workbook
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)

VB/VB.Net (most recent listed first)
---------
Q319180 HOWTO: Transform Dataset to Spreadsheet XML for Microsoft Excel
Q318373 HOW TO: Use GetOleDbSchemaTable with Excel in Visual Basic .Net
Q316934 HOW TO: Use ADO.Net to Retrieve and Modify Records in Excel
Q311731 HOW TO: Query and Display Excel Data Using ADO.NET, VB .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/ASP.Net/Web (most recent listed first)
---------------
Q317719 HOW TO: Export Data in DataGrid on an ASP. NET WebForm to Excel
Q311194 HOW TO: Use ASP.NET to Display Database Data in Excel
Q308247 HOW TO: Use ASP.NET to Query and Display Database Data in Excel
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
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)
--------------
Q321686 HOW TO: Import Data into SQL Server from Microsoft Excel
Q319951 HOW TO: Transfer Data to Excel Using SQL Server DTS
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

XML (most recent listed first)
---
Q319180 HOWTO: Transform Dataset to Spreadsheet XML for Microsoft Excel
Q307021 HOW TO: Transfer XML Data to Microsoft Excel 2002 with VB .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

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.

 
 
 

Column display null while exporting from Excel Through DTS

Post by Allan Mitchel » Thu, 27 Jun 2002 08:03:29


Also

Excel Inserts Null Values
http://www.sqldts.com/main.asp?nav=1,6,254,0

--

Allan Mitchell
http://www.sqldts.com

 
 
 

1. SQL 2000 DTS export to Excel problem with Nulls

I have just upgraded to SQL 2000 from 7. I am having
problems with null placements within my Excel file after
running a DTS Package. But if I run the same query in the
Query Analyzer window then import the results into Excel
there is no problem. So it just occurs when using DTS.

Expected results and results from QA:
1,,,2,3

Actual results from DTS import

1,2,3,,,

For some reason it doesn't recognize the nulls and moves
data to the left. In the Transform Data Task Properties>
Options tab I've checked Use Fast Load and Keep null
values. Any suggestions would be helpful?

2. Text file DTS showing history of changes

3. DTS Export: How to have nulls export as blanks on fixed width export

4. Case insensitive SQL search with LIKE?

5. DTS Export to Excel with Detailed Excel Formatting

6. Trainer wanted UK

7. DTS Error: it tries to insert null into column when no not null is set

8. DTS Error: it tries to insert null into column when no not null

9. Export Null values to Excel cause left shifted values in XLS

10. Export to Excel - problem with NULL

11. Null values exporting to Excel

12. column is null vs. column = coalesce(@var,null)