General

Expand all | Collapse all

Data loading error

  • 1.  Data loading error

    Posted 11-08-2017 14:20

    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.  RE: Data loading error

    Posted 11-08-2017 16:38

    Hi,

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

    regards



  • 3.  RE: Data loading error

    Posted 11-08-2017 20:07

    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.  RE: Data loading error

    Posted 11-08-2017 20:28

    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.  RE: Data loading error

    Posted 11-09-2017 06:35

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