When using Excel as a data source, you can experience problems with data types. Typically you will see that a text field is detected as a decimal field, but other mistakes can also occur.
By default, Excel uses the first eight rows of data to determine the data type. So if the first eight rows of data for a particular field is numeric, then the data type will be declared as numeric. If the ninth row of data contains alphanumerical characters then it will cause an error or simply be assigned no value (Null) in the staging table.
You can change the behaviour for detecting the data types by changing a registry parameter called "TypeGuessRows".
- Click Start -> Run -> type "regedit" -> press Enter
- Press F3 to bring up the search dialogue and enter "TypeGuessRows".
- When found, right click on the value TypeGuessRows and choose Modify...
- Enter a value higher than eight to increase the number of rows Excel uses to determine the data type or zero to make Excel read all rows to determine the data type.