December 11, 2003 at 6:57 am
Something like this was mentioned in another thread. It seems that if you use Excel import in DTS, Excel will not convert char and numeric in same column, if it thinks a column is char then it will ignore numbers in that column and if it thinks a column is numeric then it will ignore chars in that column.
Edited by - davidburrows on 12/11/2003 06:57:55 AM
Far away is close at hand in the images of elsewhere.
Anon.
December 12, 2003 at 1:29 am
I have made the same experience.
I came across one document on Microsoft site describing this behaviour. It said that the first 8 lines are used for determining whether the column will be imported as char or float. This feature is not a bug, it is by design.
I usually export the excel into text file, load it into temporary table, and then do the required type conversions in SQL database.
Franta
December 12, 2003 at 2:18 am
quote:
It said that the first 8 lines are used for determining whether the column will be imported as char or float. This feature is not a bug, it is by design.
True, but once it has decided it will only accept numbers in numeric columns and alpha in char columns, anything else will result in nulls.
Edited by - davidburrows on 12/12/2003 02:18:17 AM
Far away is close at hand in the images of elsewhere.
Anon.
December 12, 2003 at 3:40 am
But the strange thing is that in the same column it imported some integers but not others. The only text in the column was the column header. Otherwise just nulls.
In view of Microsoft's design flaw I will avoid using Excel for imports in the future.
December 13, 2003 at 4:17 pm
quote:
quote:
It said that the first 8 lines are used for determining whether the column will be imported as char or float. This feature is not a bug, it is by design.True, but once it has decided it will only accept numbers in numeric columns and alpha in char columns, anything else will result in nulls.
ah, but you can change whether or not sql will determine the values based on the first 10 lines. You can actually turn it off by editing the following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel
the value for TypeGuessRows must be set to 0
Hope this helps.
Viewing 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply