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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
Steve HoggITCommented:
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)
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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.      
Steve HoggITCommented:
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..


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.