January 26, 2012 at 4:01 am
Hi there. I have a little problem like this. In an Excel file there is a column that, unfortunately, stores text as well as numbers. The numbers can be big, say for instance, 123422220202222. Well, you know what Excel does in this situation, right? It stores it as something like 1.234222e+14. So, when the Excel source component retrieves the number it sees 1.234222e+14, and not 123422220202222. Hence, even if I convert this to the standard form later on, that is without the exponent part, I'll end up with a number like 123422200000000, which - OF COURSE - is far away from the truth! Now, would anyone know a way around it, PLEASE? Any help will be much, much, MUCH, appreciated. Best, Darek.
January 26, 2012 at 5:24 am
In the connection string, add IMEX=1 at the end. http://www.connectionstrings.com/excel
Set the registry setting of TypeGuessRows to 0 for the JET provider. http://support.microsoft.com/kb/281517
This way, the JET provider will scan the entire column to determine the datatype. If there are mixed datatypes, it will import the column as text.
What if the entire column consists of really big numbers? You can import them AS-IS to a staging table in SQL Server. With TSQL you can easily convert numbers in scientific format to normal numbers.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 26, 2012 at 11:06 am
Well, that doesn't solve the problem, to be honest. Even If I import the data to a staging table, it's still in the form of a.aaa+eAA, and there is no way to regain the original value for the simple reason that some digits HAVE BEEN LOST during conversion to the scientific notation. And I have also tried the trick with registry setting change and IMEX - in a word: DOESN'T WORK. Thanks for tips at any rate. Best - Darek
January 26, 2012 at 11:37 am
It's not an IMEX issue. Those cause NULLs instead of text/numeric data, depending on which one it thinks the column is based on a 4-row sample. They don't cause this issue.
Have you tried setting the Excel column to text-format in Excel? That will preserve numbers as text, and won't convert them to scientific notation.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 26, 2012 at 2:57 pm
GSquared (1/26/2012)
It's not an IMEX issue. Those cause NULLs instead of text/numeric data, depending on which one it thinks the column is based on a 4-row sample. They don't cause this issue.Have you tried setting the Excel column to text-format in Excel? That will preserve numbers as text, and won't convert them to scientific notation.
It's not an IMEX issue, but the OP mentioned that the column is mixed text and numbers, so you'll need to configure IMEX and the registry setting anyway, or the NULL problem pops up at a later point in time.
That being said, I only dealt with the scientific notation once, I just dumped it into SQL Server, it recognized those numbers perfectly and put them in the right format.
I didn't lose any digits, but maybe I didn't have so many significant digits as the OP.
Another path worth pursuing is writing a SQL statement to the Excel file and use the FORMAT function to get the data into the format you want. (I'm not sure how that SQL versus Excel thing works, as FORMAT is not a TSQL function. I believe it to be a VBA function of some sort)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 27, 2012 at 6:18 am
Koen Verbeeck (1/26/2012)
GSquared (1/26/2012)
It's not an IMEX issue. Those cause NULLs instead of text/numeric data, depending on which one it thinks the column is based on a 4-row sample. They don't cause this issue.Have you tried setting the Excel column to text-format in Excel? That will preserve numbers as text, and won't convert them to scientific notation.
It's not an IMEX issue, but the OP mentioned that the column is mixed text and numbers, so you'll need to configure IMEX and the registry setting anyway, or the NULL problem pops up at a later point in time.
That being said, I only dealt with the scientific notation once, I just dumped it into SQL Server, it recognized those numbers perfectly and put them in the right format.
I didn't lose any digits, but maybe I didn't have so many significant digits as the OP.
Another path worth pursuing is writing a SQL statement to the Excel file and use the FORMAT function to get the data into the format you want. (I'm not sure how that SQL versus Excel thing works, as FORMAT is not a TSQL function. I believe it to be a VBA function of some sort)
Completely agreed. Didn't mean to sound like I was coming down on your answer, just wanted to clarify that IMEX won't fix the lost digits piece.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 27, 2012 at 6:34 am
GSquared (1/27/2012)
Completely agreed. Didn't mean to sound like I was coming down on your answer, just wanted to clarify that IMEX won't fix the lost digits piece.
I know.
If Excel reads the data as text, it should read the whole number as text and not lose any digits.
But then again, there is what Excel should do, and there is reality... 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 30, 2012 at 6:30 am
Koen Verbeeck (1/27/2012)
GSquared (1/27/2012)
Completely agreed. Didn't mean to sound like I was coming down on your answer, just wanted to clarify that IMEX won't fix the lost digits piece.I know.
If Excel reads the data as text, it should read the whole number as text and not lose any digits.
But then again, there is what Excel should do, and there is reality... 🙂
If Excel thinks the data is numeric, it actually will store it differently than if it thinks the column is text. Not an issue with the import/connection driver, a problem with how the data is stored in Excel. A stupid problem that Microsoft should have remedied about 10 versions of Excel ago, but haven't.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 2, 2012 at 5:05 am
The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type. (In a tie, the numeric type wins.) Most cell formatting options in the Excel worksheet do not seem to affect this data type determination. You can modify this behavior of the Excel driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window. " http://msdn.microsoft.com/en-us/library/ms141683.aspx http://support.microsoft.com/?scid=kb;en-us;194124&x=9&y=12
Search Engine Optimizing | Search Engine Marketing | Social Media Marketing | Pay Per Clicks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply