Loading data from a flat file results in "ERROR: Extra Data after Last Expected Column"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

Loading data from flat files results in the following error message:

ERROR: extra data after last expected column

Environment

Cause

This error will be encounter if the source data has more columns than the target table.

Resolution

Ensure that the source data and table columns align. 

An example of source data having more columns than the target table is below:  
 

Target table

ddata=# \d emp
      Table "public.emp"
Column  " Type    " Modifiers
--------+---------+-----------
id      " integer "
dept    " integer "
role    " integer "
Distributed by: (id)

Source data file

$ cat /tmp/load.dat
1"2"3"4
1"2"3"4
1"2"3"4
1"2"3"4
1"2"3"4
1"2"3"4

Error

ddata=# copy emp from '/tmp/load.dat' with delimiter as '"';
ERROR: extra data after last expected column (seg0 sdw1:55001
pid=12476)
CONTEXT: COPY emp, line 1: "1"2"3"4"

As seen from the example above, the source data file has one more column than the target table, resulting in the error described above.