Wrong number of columns! COPYing 99 columns from a source file containing: FEWER columns.
Hello,
COPY loader return error, BUT I count column in row loaded normally - 99 and column in reject row 99.
Why this error else can occurs ?
0
Hello,
COPY loader return error, BUT I count column in row loaded normally - 99 and column in reject row 99.
Why this error else can occurs ?
Comments
Can you paste the CREATE TABLE of the target table into your post, and also exactly the rejected row?
create table ALL_EVENTS
(
id VARCHAR(500) not null,
version NUMBER(10) not null,
title Long Varchar,
description Long Varchar,
solution Long Varchar,
state VARCHAR(500),
severity VARCHAR(500),
priority VARCHAR(500),
category VARCHAR(500),
subcategory VARCHAR(500),
type VARCHAR(500),
related_ci_hint Long Varchar,
om_service_id Long Varchar,
related_ci_id VARCHAR(500),
related_ci_type VARCHAR(500),
node_id VARCHAR(500),
node_type VARCHAR(500),
sequence_number NUMBER(19),
nodehints_hint VARCHAR(8000),
nodehints_dnsname VARCHAR(500),
nodehints_ipaddress VARCHAR(500),
nodehints_coreid VARCHAR(500),
sourcecihints_hint Long Varchar,
sourcecihints_dnsname VARCHAR(500),
sourcecihints_ipaddress VARCHAR(500),
sourcecihints_coreid VARCHAR(500),
originating_dnsname VARCHAR(500),
originating_ipaddress VARCHAR(500),
originating_coreid VARCHAR(500),
sending_dnsname VARCHAR(500),
sending_ipaddress VARCHAR(500),
sending_coreid VARCHAR(500),
om_user VARCHAR(500),
assigned_user NUMBER(10),
assigned_group NUMBER(19),
cause_id VARCHAR(500),
time_created TIMESTAMP(6),
time_changed TIMESTAMP(6),
time_state_changed TIMESTAMP(6),
time_received TIMESTAMP(6) not null,
duplicate_count NUMBER(10),
eti_hint VARCHAR(500),
eti_subcomponent_id VARCHAR(500),
ua_host_dnsname VARCHAR(500),
ua_host_ipaddress VARCHAR(500),
ua_host_coreid VARCHAR(500),
ua_call Long Varchar,
ua_status VARCHAR(500),
ua_add_anno NUMBER(1),
ua_will_resolve NUMBER(1),
aa_host_dnsname VARCHAR(500),
aa_host_ipaddress VARCHAR(500),
aa_host_coreid VARCHAR(500),
aa_call Long Varchar,
aa_status VARCHAR(500),
aa_add_anno NUMBER(1),
aa_will_resolve NUMBER(1),
application VARCHAR(500),
object VARCHAR(500),
event_key Long Varchar,
close_key_pattern Long Varchar,
original_data Long Varchar,
log_only NUMBER(1),
no_dedup NUMBER(1),
received_on_ci_downtime NUMBER(1),
instruction_available NUMBER(1),
source_ci_id VARCHAR(500),
source_ci_type VARCHAR(500),
policy_type VARCHAR(500),
policy_name VARCHAR(500),
condition_id VARCHAR(500),
condition_name VARCHAR(500),
original_id VARCHAR(500),
correlation_type VARCHAR(500),
correlation_rule_id VARCHAR(500),
correlation_weight VARCHAR(500),
eti_indicator_id VARCHAR(500),
eti_value_id VARCHAR(500),
eti_reset_value_id VARCHAR(500),
eti_numeric_value FLOAT,
control_dnsname VARCHAR(500),
control_server_port NUMBER(10),
control_server_id VARCHAR(500),
control_external_id VARCHAR(500),
control_external_url VARCHAR(500),
rule_name VARCHAR(500),
transfer_state VARCHAR(500),
initiated_by_id NUMBER(10),
source_dnsname VARCHAR(500),
source_ia_management_port NUMBER(10),
source_ia_management_protocol VARCHAR(500),
source_server_id VARCHAR(500),
source_external_id VARCHAR(500),
source_external_url VARCHAR(500),
cires_hint_count NUMBER(10),
cires_matched_hint_count NUMBER(10),
cires_quality_metric NUMBER(10),
cires_status Long Varchar,
received_as_notify NUMBER(1)
)
PARTITION BY EXTRACT (MONTH FROM time_received)
;
content reject_row
Host: host01
Title: Count files in folder «C:\oris\UPS!Unrecognized:.*» = 1, upper threshold 1
System: ORIS OS
ID TM: 10091000
URL by TM: http://superorders/monpoint/OpenMonPoint.jsf?id=10091000
Scheduler time: 00:00-23:59XSan,Mon
Emergensy team: Capitan America,Iron Man,АМ
URL Emergency: http://duty/DutyList/web/DutyList.jsf?groupId=399991500
Event ID: 2ba84514-400d-71e7-1e5f-0a4805420000
URL by event: https://hpbsm/opr-web/opr-evt-details?eventId=2ba84514-400d-71e7-1e5f-0a4805420000
List operations for duty team: If you got Healph event ""Heartbeat"" create incident on emegensy team Microsoft
URL by IMS: https://ims/view.asp?BLOCK=MAIN&ID=696000@host01"" resolved.";"0"
information from previous post
Hi!
File
example_reject_row.txtcontains non UTF-8 char sequences. Are you sure original file encoding is URF-8 and not ISO-8859-14?original file has UTF-8 char
for loading I used python script
with open(file_name, "rt", encoding='UTF-8') as fs:
my_file = fs.read()
cursor.copy("COPY %s.%s from stdin PARSER fcsvparser(type='traditional', delimiter=';', record_terminator='\n')" %(SCHEMA_NAME, TABLE_NAME), my_file)
The row that got rejected has two properties - in one string - that cause problems with a normal COPY command:
1. You have the double quote as the string encloser, and the string contains double quotes, which are doubled in your case:
[...]Healph event ""Heartbeat"" create incident [...].2. The same string, the one beginning with
"Gadukinoat position 1242 in the file, has a big number of newlines inside. The normal COPY command reads the input until the next newline and tries to load it. The fact that the newline is in a string, does not count.I would suggest that you look at the Vertica documentation on the CSV parser, and try :
COPY <table> FROM '<file>' PARSER fcsvparser( ....). Check the documentation for format and contents of the parameters forfcsvparser().Good luck -
Marco
I just saw that you use the
fcsvparser()already. I would try to removetype=traditional.I tried it myself. Does not work. The problem persists because of the newlines in the string.