Having troubles loading data in InfoBright ICE

Question

ICE Version: infobright-3.5.2-p1-win_32

I’m trying to load a large file but keep running into problems with errors such as:

Wrong data or column definition. Row: 989, field: 5.

This is row 989, field 5:

”(450)568-3***"

Note: The last 3 chars are numbers as well, but didn’t want to post somebodys phone number on here.

It’s really no different to any of the other entries in that field.

The datatype of that field is VARCHAR(255) NOT NULL

Answer 1

I've run into this issue when the last line of the file is not terminated by the value of --lines-terminated-by="\n". For example If I am importing a file with 9000 lines of data I have to make sure there is a new line at the end of the file. Depending on the size of the file, you can just open it with a text editor and hit the return k

Answer 2

I have found this to be consistent with the '\r\n' vs. '\n' difference. Even when running on the loader on Windows, '\n' succeeds 100% of times (assuming you don't have real issues with your data vs. col. definition)

Answer 3

Also, if you upgrade to the current release 4.0.6, we now support row level error checking during LOAD and support a reject file.

To enable the reject file functionality, you must specify BH_REJECT_FILE_PATH and one of the associated parameters (BH_ABORT_ON_COUNT or BH_ABORT_ON_THRESHOLD). For example, if you want to load data from the file DATAFILE.csv to table T but you expects that 10 rows in this file might be wrongly formatted, you would run the following commands:

set @BH_REJECT_FILE_PATH = '/tmp/reject_file';

set @BH_ABORT_ON_COUNT = 10;

load data infile DATAFILE.csv into table T;

If less than 10 rows are rejected, a warning will be output, the load will succeed and all problematic rows will be output to the file /tmp/reject_file. If the Infobright Loader finds a tenth bad row, the load will terminate with an error and all bad rows found so far will be output to the file /tmp/reject_file.