Unable to get numbers in excel spreadsheet from database although the data conversion exports numeric data type

Posted on 2008-11-03
Last Modified: 2013-11-10
When using Excel to receive records from SQL through a data conversion, the numbers in the excel spreadsheet are imported as text.  As a result, I am unable to do formulae such as sum().  The only way is to convert the number from text in the excel spreadsheet but this adds another level of complexity to the client.
Question by:optimacommunications
    LVL 59

    Accepted Solution

    Lets say...your numbers get pasted in column-A range-2 till the last row of Column-A...then you can simply use...this macro to convert them back to number...

    Sub convert()
    dim rng as range,cell as range
    set rng=Range("A2:A"&CELLS(65536,"a").END(XLUP).ROW)
    for each cell in rng
    NEXT Cell
    end Sub

    Open in new window

    LVL 22

    Expert Comment

    Are you sure you dont have any cell empty and they are all numbers, correct?
    If true, then try to go to advanced editor and force the change type of each column. If there is any row without the format that your are forcing, then it will generate an error.
    See images attached.
    LVL 17

    Expert Comment

    When you write your Source SQL Statement, to retrieve the records, use the CAST function on EVERY column to select the appropriate datatype. Do not put the Excel destination object in the Data Flow window until you have verified all column datatypes.
    Data types. The Excel driver recognizes only a limited set of data types. For example, all numeric columns are interpreted as doubles (DT_R8), and all string columns (other than memo columns) are interpreted as 255-character Unicode strings (DT_WSTR). Integration Services maps the Excel data types as follows:
    Numeric  double-precision float (DT_R8)
    Currency  currency (DT_CY)
    Boolean  Boolean (DT_BOOL)
    Date/time  date (DT_DATE)
    String  Unicode string, length 255 (DT_WSTR)
    Memo  Unicode text stream (DT_NTEXT)

    Author Comment

    None of the proposed solutions work.  If I use a new file or recreate the sheet, SSIS seems to do an implicit conversion.  I am trying to copy an excel spreadsheet to an output sheet (because I have some conditional formatting) and then put the data into the output sheet.  It seems very straightforward but it just is not working.

    I have poured over this problem for days!  Any suggestions.      
    LVL 17

    Expert Comment

    I have created the SSIS package which accomplishes this task. It takes a table in SQL Server, ToExcel, and writes it to an Excel file in the c:\temp directory. I tested it and it works, writes the correct datatypes. Attached is the file - I had to rename the file with a .txt on the end in order to upload. When you save the file, just remove the .txt and it will be a .dtsx again.


    Expert Comment

    Hi HoggZilla,

     Sorry but none of the solution has worked for me. Do you know any other way out. I have tried with 8 byte Unsigned Integer as well. But still produces same Excel file.
    I have tried below mentioned things:
    1. to put complete New File (with Formatted Cell as Numeric)
    2.Using Data Conversion
    3.Using Excel Destination --  Create table "Excel Destination" ---

    It is really tough issue...  Thanks in advance..


    Expert Comment

    Hi ,
       I got solution:
       Append a DUMMY ROW in excel file with the required data type ( Numeric).. It works..



    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now