March 23, 2011 at 2:15 am
Hi,
I have an SSIS package which loads data into SQL server database. In the excel file, one of the column contains big integer values (e.g: 5012170). When the SSIS package is executed, it loads this value as 5.01217e+006 in the database.
I'm not very sure about fixing this and could you please help me in sorting out this problem?
Regards,
Krishna Chaitanya
March 23, 2011 at 2:25 am
KrishnaChaitanya (3/23/2011)
Hi,I have an SSIS package which loads data into SQL server database. In the excel file, one of the column contains big integer values (e.g: 5012170). When the SSIS package is executed, it loads this value as 5.01217e+006 in the database.
I'm not very sure about fixing this and could you please help me in sorting out this problem?
Regards,
Krishna Chaitanya
If you are loading into a numeric field, surely this value does not get loaded but throws an error?
Is it definitely 'e+006'? This seems weird to me, as the two leading zeros are not significant.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 23, 2011 at 3:56 am
Try setting the column in Excel to text instead of number.
Hopefully the JET OLE DB provider will read it in as a string and doesn't screw up the format.
If it does, you can try writing a SQL query to the excel file where you use the FORMAT function.
Alternatively, you could just store it like that in the SQL Server database (maybe in a varchar field) and do the conversion there. SQL Server should be able to convert a number in scientific format to a "normal" number.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 23, 2011 at 3:57 am
Oh yeah, I forgot:
yet another alternative is to load the data from .csv instead of Excel. Less headaches!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 23, 2011 at 11:12 am
SQL Server supports casting strings containing exponential notations to approximate data types:
SELECT CAST('5.01217e+006' AS REAL) AS string_to_real,
CAST('5.01217e+006' AS FLOAT) AS string_to_float
edit: format code so no side-scrolling is required
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 23, 2011 at 10:24 pm
Hi,
Thanks for you suggestions.
I have solved the problem by using the Format function while reading the data from the excel file.
In the Excel Source Editor - SQL command text while reading the data from the excel file, I have used the below query and it read the data as expected (without the exponential)...
Select Format(F1), F2, F3 from [Sheet1$]
Cheers,
Krishna Chaitanya
March 24, 2011 at 12:52 am
Great, glad you found the solution. Thanks for posting back.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 24, 2011 at 1:31 am
Yes, I like that solution.
I had a look around for details of what that format function does (when used with a numeric argument) and the best I could find is here. Still doesn't really explain what is going on though.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 24, 2011 at 1:43 am
Phil Parkin (3/24/2011)
Yes, I like that solution.I had a look around for details of what that format function does (when used with a numeric argument) and the best I could find is here. Still doesn't really explain what is going on though.
Yeah, some time ago I spent hours searching for good documentation on the FORMAT function.
I came up with the same page 🙂
It is quite tricky to find all the allowed format options. Try searching Google with "excel format function format parameter". Good luck with that 🙂
Why did they give the parameter the same name as the function? Ugh.
You can also use abbreviations such as "C" and #. It behaves pretty much like the VBA function Format I guess.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 10, 2012 at 2:16 am
You can try change connection settings to Excel in SSIS.
In Extended properties change "Excel 12.0" -> "Excel 12.0;IMEX=1"
Then all rows from Excel are datatype Unicode string 255 (DT_WSTR). So import works everytime and all transformations you can do in SQL server (stored procedure etc.)
That helps me a lot http://munishbansal.wordpress.com/2009/12/15/importing-data-from-excel-having-mixed-data-types-in-a-column-ssis/
February 10, 2012 at 2:25 am
PanKostka (2/10/2012)
You can try change connection settings to Excel in SSIS.In Extended properties change "Excel 12.0" -> "Excel 12.0;IMEX=1"
Then all rows from Excel are datatype Unicode string 255 (DT_WSTR). So import works everytime and all transformations you can do in SQL server (stored procedure etc.)
That helps me a lot http://munishbansal.wordpress.com/2009/12/15/importing-data-from-excel-having-mixed-data-types-in-a-column-ssis/
That only works if you have mixed data types (InterMIXed). If all the data in the column is numeric with scientific notation, then SSIS imports it as numeric.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply