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 structureCREATE 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\ExcelFor 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:
Thanks for you solution...
How to hide a row while reading the data
Muy buena Solución! Gracias la estaba buscando... me ayudo de mucho!
Saludos Walter.
Good trick, thanks!
Excelente!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Solution:1 Thanks, but how to delete second row using ssis ?
Try...
Free Excel Add-in Convert Excel cell Numbers to Words with Prefix and Suffix features.
Download Link - http://www.xltool.in
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
Hai,
Is it any way to save currency datatype in excel sheet
thankyou
Post a Comment