Thursday, October 20, 2011

Numeric gets converted to Text in Excel from SSIS

This is with reference to a question asked in MSDN forum where a User was trying to fill an Excel sheet with one the Numeric Column and field was changing to Text. 

OR

SSIS Excel Data Source: Text was truncated or one or more characters had no match in the target code page

 

This happens as Excel identifies data type for a column based on sampling of top few rows(8 by default) and in that sample it doeskin get correct data type.

 

 Problem:

Lets see the problem with an example I have a simple table with following structure
CREATE TABLE Emp
( ID INT IDENTITY (1,1)
, [Name] VARCHAR (10)
, Salary NUMERIC (10,2)
);


Here ID and Salary are Integer and Numeric receptively and if we try to export them to Excel using SSIS, Excel will store the correct values but would convert them into Text as below( see the Green mark over cell)


Unforunatly there is no way that we can control or change  the format/ datatype of a column of Excel sheet form SSIS.Even if we change the datatype using Advance Editor in Excel Destination , it wont reflect the change.

Solution:
So is there is solution to this, well not excatly a solution but a workaround Yes.
When Excel decide about format of cells in a column it takes sample from first few cells (other than first row-header) and keeps the same format for rest of cells down below. We can use this property of Excel and create one dummy row with the format as we are excepting for rest of the cells.

Now during actual writing of data Excel will pick the format from Dummy Row and keep it for rest of them. You can even hide Dummy Row after putting some values in it.

OR

We can increase the sampling Rows for Excel and it can guess Correct Data Type based on given Range of Rows. Sample Range for Rows can be increased from Registry Key TypeGuessRows
 

Path in Registry

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel 

For 64 bit machines it may be under
 HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel 

You can set any values to this editable field
a) Default: 8
b) 0 will take all the rows in the Sheet -This may slow down validation if no. of rows are large.
c) Recommend is to give a value where you can be sure of having correct data type rows.




Cheers!!

15 comments:

Sugee said...

Thanks for you solution...

Anonymous said...

How to hide a row while reading the data

Anonymous said...

Muy buena Solución! Gracias la estaba buscando... me ayudo de mucho!
Saludos Walter.

Anonymous said...

Good trick, thanks!

Anonymous said...

Excelente!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Nikolay Vetrov said...

Solution:1 Thanks, but how to delete second row using ssis ?

NITU said...

Try...
Free Excel Add-in Convert Excel cell Numbers to Words with Prefix and Suffix features.
Download Link - http://www.xltool.in

Srinivasa Rao Reddi said...
This comment has been removed by the author.
Domain KING said...

Thank you for such a wonderful Information !!
Here is a list of Top LINUX INTERVIEW QUESTIONS

Veritas Cluster Interview Questions

SAMBA Server Interview Questions

Linux FTP vsftpd Interview Questions

SSH Interview Questions

Apache Interview Questions

Nagios Interview questions

IPTABLES Interview Questions

Ldap Server Interview Questions

LVM Interview questions

Sendmail Server Interview Questions

YUM Interview Questions

NFS Interview Questions

Read More at :- Linux Troubleshooting

Anonymous said...

Hai,

Is it any way to save currency datatype in excel sheet

Anil Dubey said...

thankyou

Ganesh Ponna said...

Hello There,


A spot on observation on what probably is “the” underlying details of the #topic.Too many people don’t even think about wherever there will be actual demand and more importantly what happens if this demand comes later (or maybe a lot later) than they expect



I want to ask for the newbie of linux, which platform of linux should I start with?
But nice Article Mate! Great Information! Keep up the good work!


Many Thanks,
Ganesh


preethi Sharma said...

Hi There,

I learnt so much in such little time about Data Solutions : MSBI, Data Science. Even a toddler could become smart reading of your amazing articles.

I have been using linux for quite a few years now but have never really gotten involved beyond the basic home user level. I would like to get involved in linux now. how do i go about this? i have no programming skills or knowledge... yet.

I am so grateful for your blog. Really looking forward to read more.

MuchasGracias,
Preethi.

Kevin Lee said...

Hi Rahul,

Numeric gets converted to Text in Excel from SSIS being contrived to exist for many projects simply so it can be run will be the first to hit the wall, but those projects where the functions to make existing transactions cheaper in real world applications will find the elusive real-world demand.
The /boot/ directory contains static files required to boot the system, such as the Linux kernel, boot loader configuration files. These files are essential for the system to boot properly.

Is there a way I can make partitions for Linux on my 2 TB external HDD and be able to boot into linux from my HDD?
Thank you very much and will look for more postings from you.

Merci Beaucoup,
Kevin

Radhey Radhey said...

Hello There,

Zoooooooom! That’s how speedy and easy this read was! Looking forward to more of such powerful content on Data Solutions : MSBI, Data Science!

I'm new to linux and I'm loving it, but I need help setting up subdomains and users for mail in Fedora 13, I'm using postfix as my MTA. Your assistance would be grealty appreciated.

Awesome! Thanks for putting this all in one place. Very useful!

Grazie,
Radhey

Post a Comment