Load .txt file into Postgres Database

I created a new Postgresql table and I’m having trouble loading a .txt file into that table. I striped the headers out of the text file and tried loading the data but got this error:

ERROR: extra data after last expected column CONTEXT: COPY crd_codes1, line 1: “01 00 000 Alabama 1” SQL state: 22P04

I’ve included a snapshot of data within the text file in case it helps. Here is the code for the blank table that I created in the database:

CREATE TABLE CRD_codes1 
(state char(2), 
 crd char(2), 
 county char(3), 
 crd_name varchar(50), 
 history_flag char(1));

Any help would be much appreciated!

Answer

Welcome to Stack Overflow.

In case your columns are split by tab, try this out:

COPY CRD_codes1 FROM '/home/jones/file.txt' CSV DELIMITER E't'

• The E't' states that the values are split by TAB

But in case your delimiter isn’t properly set, as your screenshot suggests, you have to clean this data before inserting into your table. The following script imports data into a temporary table, replaces the redundant tab characters and populates the target table.

CREATE TEMPORARY TABLE t (c TEXT);
COPY t FROM '/home/jones/file.txt' CSV ESCAPE E't';

WITH j AS (
  SELECT 
    string_to_array(
      regexp_replace(c, 't+', ',', 'g'),
    ',') AS val 
  FROM t
) INSERT INTO CRD_codes1
SELECT j.val[1],j.val[2],j.val[3],j.val[4],j.val[5] FROM j;

SELECT * FROM CRD_codes1;

 state | crd | county |       crd_name        | history_flag 
-------+-----+--------+-----------------------+--------------
 01    | 10  | 077    | Lauderdale            | 1
 01    | 10  | 888    | D10 Combined Counties | 1
(2 Zeilen)