Data loading error


#1

I have pipe-delimited files where each row ends with a pipe character. Some load without problems using the copy command, but others have failed.

The error is along the lines of: “Incorrect Row (expected 17 columns, has 18) …”. The loader seems to expect an additional column due to the final |.

I have recreated one of the tables concerned in MySql using the same table DDL and loaded the same file without problems.

Have you any suggestions?

Many thanks.


#2

Hi,

Please send a small sample file for us to try to reproduce the issue

regards


#3

The file is 175MB, but here are a few rows.

1|lace spring|MFGR#1|MFGR#11|MFGR#1121|goldenrod|PROMO BURNISHED COPPER|7|JUMBO PKG|
2|rosy metallic|MFGR#4|MFGR#43|MFGR#4318|blush|LARGE BRUSHED BRASS|1|LG CASE|
3|green antique|MFGR#3|MFGR#32|MFGR#3210|dark|STANDARD POLISHED BRASS|21|WRAP CASE|
4|metallic smoke|MFGR#1|MFGR#14|MFGR#1426|chocolate|SMALL PLATED BRASS|14|MED DRUM|
5|blush chiffon|MFGR#4|MFGR#45|MFGR#4510|forest|STANDARD POLISHED TIN|15|SM PKG|
6|ivory azure|MFGR#2|MFGR#23|MFGR#2325|white|PROMO PLATED STEEL|4|MED BAG|
7|blanched tan|MFGR#5|MFGR#51|MFGR#513|blue|SMALL PLATED COPPER|45|SM BAG|
8|khaki cream|MFGR#1|MFGR#13|MFGR#1328|ivory|PROMO BURNISHED TIN|41|LG DRUM|
9|rose moccasin|MFGR#4|MFGR#41|MFGR#4117|thistle|SMALL BURNISHED STEEL|12|WRAP CASE|
10|moccasin royal|MFGR#2|MFGR#21|MFGR#2128|floral|LARGE BURNISHED STEEL|44|LG CAN|

The table is:
CREATE TABLE part(
p_partkey INT NOT NULL,
p_name VARCHAR(22),
p_mfgr CHAR(6),
p_category CHAR(7),
p_brand1 CHAR(9),
p_color VARCHAR(11),
p_type VARCHAR(25),
p_size INT,
p_container CHAR(10)
);

I created it in a new database. I executed:
COPY part from ‘/home/centos/data/part.tbl’ WITH (delimiter = ‘|’, quoted = ‘false’, header=‘false’);

Many thanks.


#4

Hi,

There should be no trailing delimiter.

This looks like TPC-H style generated data. If you have generated the data there is an option on dbgen to stop the trailing delimiter being generated

regards


#5

Thanks for the clarification, although it’s odd that the other files loaded. I’ll try again without.