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.
Who is Participating?
Saurabh Singh TeotiaConnect With a Mentor Commented:
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
end Sub

Open in new window

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.
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)
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

optimacommunicationsAuthor Commented:
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.      
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.

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

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


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.