asked on

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

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.

ASKER CERTIFIED SOLUTION

Saurabh Singh Teotia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PedroCGD

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.
Helped?
regards!
pedro
www.pedrocgd.blogspot.com
EE-Excel1.JPG
EE-Excel2.JPG

Steve Hogg

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)

optimacommunications

ASKER

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.

Steve Hogg

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.

SQL-TO-EXCEL.dtsx.txt
data-conversion.bmp
excel-create-table.bmp
fromsql.xls

hiren_panchal

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..

hiren_panchal

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

Hiren