• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 480
  • Last Modified:

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.
0
optimacommunications
Asked:
optimacommunications
1 Solution
 
Saurabh Singh TeotiaCommented:
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...

Saurabh...
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
CELL.VALUE=CELL.VALUE
NEXT Cell
end Sub

Open in new window

0
 
PedroCGDCommented:
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
0
 
HoggZillaCommented:
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)
 
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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.      
0
 
HoggZillaCommented:
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
0
 
hiren_panchalCommented:
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..

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

 Hiren

 
   
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now