Export to Excel - numbers are exported as text

Export to Excel - numbers are exported as text

Post by Nikola Mili » Wed, 22 May 2002 16:10:35



Hi,
I have problem with exporting data to Excel. Problem is that Excel see any
exported number type as text data. I've put Excel macros on Open event of
sheet to convert those text data to numbers, but it's not good solution. How
can I force Excel to see numbers correctly ?

-- itopDailyReport is linked server to Excel template
-- tblDailyReport is named range in Excel template with columns Name and
Code
INSERT INTO
OPENQUERY(itopDailyReport, '
        SELECT Name, Code
        FROM tblDailyReport
')
SELECT 'name' , 123

I'm using SS2000 Enterprise edition SP1 on Win2000 Advanced Server SP2.

Thanks in advance
Nikola Milic

 
 
 

Export to Excel - numbers are exported as text

Post by Nikola Mili » Wed, 22 May 2002 17:59:14


Hi,
I've found http://support.microsoft.com/default.aspx?scid=kb;EN-US;q281517
(KB article no. q281517). There is direction to solution. Jet db engine
search first 8 rows in Excel and try to guess the type of the source column.
If I put as first row (below headers) data with correct data type,
everything works fine. Problem is how to remove that first row with data
types? Again with macros, but I want to avoid macros.

Now, my question is reformulated: How can I put in Excel named range
information about data type without adding row with data?

Thanks in advance
Nikola Milic


Quote:> Hi,
> I have problem with exporting data to Excel. Problem is that Excel see any
> exported number type as text data. I've put Excel macros on Open event of
> sheet to convert those text data to numbers, but it's not good solution.
How
> can I force Excel to see numbers correctly ?

> -- itopDailyReport is linked server to Excel template
> -- tblDailyReport is named range in Excel template with columns Name and
> Code
> INSERT INTO
> OPENQUERY(itopDailyReport, '
>         SELECT Name, Code
>         FROM tblDailyReport
> ')
> SELECT 'name' , 123

> I'm using SS2000 Enterprise edition SP1 on Win2000 Advanced Server SP2.

> Thanks in advance
> Nikola Milic


 
 
 

Export to Excel - numbers are exported as text

Post by Allan Mitchel » Thu, 23 May 2002 04:19:20


Excel is one of those things that at times seems as though it was sent to test
us.  You can format the cells using OLE automation after the datapump.

I do a datapump to Excel.

I know the data starts at B1 as the first row is always the header

I drop an AX script on the page and have on an "On Success" constraint after
the pump

'**********************************************************************
' Formatting a range / or whole spreadsheet's cells
'************************************************************************
OPTION EXPLICIT

Function Main()

Dim excel_application
Dim excel_workbook
Dim excel_worksheet

Set excel_application = CREATEOBJECT("Excel.Application")

Set excel_workbook =
excel_application.Workbooks.Open(DTSGlobalVariables("gv_ExcelFileName").Value)

Set excel_worksheet = excel_workbook.Worksheets(1)

SELECT CASE DTSGlobalVariables("gv_YesNoRange").Value

CASE "True"

excel_worksheet.Range(DTSGlobalVariables("gv_Range").Value).Cells.NumberFormat
= DTSGlobalVariables("gv_Format").Value

CASE "False"

excel_worksheet.Cells.NumberFormat = DTSGlobalVariables("gv_Format").Value

END SELECT

'Save the changes

excel_workbook.Save

'Clear up the Excel objects

excel_workbook.Close
excel_application.Quit

Set excel_workbook = Nothing
Set excel_application = Nothing

Main = DTSTaskExecResult_Success
End Function

a number format of 0 gives you integer values
and obviously following on from there 0.0 is 1 decimal place , 0.00 is two etc
etc

--

Allan Mitchell
http://www.sqldts.com

 
 
 

Export to Excel - numbers are exported as text

Post by Douglas Laudenschlager [MS » Thu, 23 May 2002 09:08:28


Nikola,

In my experience, you will have the best results using Excel as a database
when you also use ADOX (or even DAO) to CREATE the Excel worksheet that you
plan to use.

There are obviously "hidden" settings for Excel cells which can enforce the
correct datatype, but which are not available through the Excel user
interface. (As you've discovered, formatting the cells in advance does not
solve the problem.)

-Doug

Using ADO and ADO.Net with Excel: Resources and Known Issues
April 30, 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
(pending)
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)
---------
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 (pending)
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.

 
 
 

Export to Excel - numbers are exported as text

Post by Nikola Mili » Thu, 23 May 2002 15:50:14


Thanks to both of you for replies,
I prefer to use macros on client side. DTS will add a lot of work on server,
which I want to avoid (and completely new export procedure, which I also
want to avoid :-)   ).

Thanks again
Nikola Milic



Quote:

> Nikola,

> In my experience, you will have the best results using Excel as a database
> when you also use ADOX (or even DAO) to CREATE the Excel worksheet that
you
> plan to use.

> There are obviously "hidden" settings for Excel cells which can enforce
the
> correct datatype, but which are not available through the Excel user
> interface. (As you've discovered, formatting the cells in advance does not
> solve the problem.)

> -Doug

> Using ADO and ADO.Net with Excel: Resources and Known Issues
> April 30, 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
> (pending)
> 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)
> ---------
> 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 (pending)
> 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.