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

11 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

Post a Comment