Load .txt file into Postgres Database

Question

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!

Are those padded spaces or tabs? If spaces, can you convert to tabs, or can you have the file be CSV? — Hambone
– Hambone, Commented Jan 25, 2020 at 1:56
If those are spaces, and this is fixed width data... stackoverflow.com/questions/20873227/… — Hambone
– Hambone, Commented Jan 25, 2020 at 1:57

Jim Jones · Accepted Answer · 2020-01-25 17:43:30Z

1

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)

answered Jan 25, 2020 at 17:43

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nicole Over a year ago

Thanks for this code snippet reminder to check for irregular spacing (I didn't think to do that). Since the history_flag field was irregularly spaced, I reformatted these data in Excel to make things easier/get my work finished on time...I hope to go back and automate later. In Excel, since records for the first 4 fields were regularly formatted, I used 'text to columns' on those (i.e. fixed width based on the largest cell length, format=TEXT). I then handled irregular spacing in the history_flag field separately since those values spanned multiple columns (separate from other data fields).

Collectives™ on Stack Overflow

Load .txt file into Postgres Database

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related