December 11, 2003 at 3:09 am
I imported an Excel 97 spreadsheet and it was not till some after that a user reported that some integer values were null.
I have retested the original spreadsheet and can not understand why some of the integer values in one column came in as nulls and others came in as integers.
Fortunately on this occasion I was able to import the data into Access and update the tables directly.
However I am a bit concerned for the future that importing integers from Excel seems unreliable.
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply