|
|
|
|
|
see Data Loader error with MS SQL Server and Sybase
see MS Access: load unable to start
MS Excel appears to have a bug with dates prior to 1 Mar 1900. As Excel incorrectly thinks that 29 Jan 1900 is a leap day, all dates prior to this are one day out of sync between how they appear in Excel and how they appear in AQT. If you are loading date values from an Excel spreadsheet into a table, you may notice this problem.
You may run into problems loading data into these columns. When running the load, the ODBC driver misreports the datatypes of these columns. We have no resolution to this.
The Data Loader makes extensive use of parameter markers. However, some ODBC Drivers cannot process parameter markers successfully. For these databases, you can specify the Load using Insert statements option. This is not as fast as the parameter-marker method, however for some databases it provides a more reliable method for loading data.
Both Oracle and DB2 cannot load zero-length LOBs. The ODBC Driver hangs or crashes when this is attempted. To circumvent this, for these databases AQT will load a single blank into the LOB.
Excel dates often come across to AQT in dd/mm/yyyy format, which won't load into most databases. The solution to this is to use the date function to convert the date to yyyy-mm-dd format. See Map to target from Source for information on the date function.
By default, AQT loads data by using parameter markers. MS Access has a number of limitations with the use of parameters. As a result, if you have character columns larger than 100 bytes, the load will fail with message String data, right truncated.
The solution is to load using the Load using Insert statements option.
When loading a date into MS Access, you may get the message:
22005;Invalid character value for cast specification (null)
This error is quite misleading; the cause of the problem is the date is not in the correct format.
Many countries use a decimal separator of comma. A numeric value will be displayed as 1234,56. However the period must still be used when loading the numeric value. For instance, you must specify:
insert into numeric_table values(1234.56)
even when your decimal separator is a comma. If you use values(1234,56) you will get a syntax error.
As a result, the Data Loader will always specify numeric values with a period rather than a comma.
The only place this doesn't work is with Oracle when your NLS_NUMERIC_CHARACTERS is set to ',.'. This specifies to Oracle that the decimal separator is comma, and in this case Oracle will give an error when AQT passes a numeric value with a period.
There are two ways to get around this:
Alter session set NLS_NUMERIC_CHARACTERS='.,'
In Oracle, columns can be defined as NUMERIC without a precision or scale specified. While this is very flexibile from a data point of view, it creates a problem with the way these columns interface to AQT. Such columns appear to AQT as having a scale and precision of zero. While AQT has a workaround to deal with this, the decimal digits are truncated. This appears to be a limitation of the ODBC Driver.
The only workaround is to load your data you Load using Inserts statements.