Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Unanswered: Copy Data From Flat File

    I've looked over this reference to copy over a text file of data but for some reason I keep getting this error message:

    Code:
    ERROR:  extra data after last expected column
    CONTEXT:  COPY sun, line 1: "1	4140	09478904812	2092-SUN-1234	8570	"
    Is there something I'm doing wrong in my flat file? I just have the data separated by white space cleanly using the 'tab' key. Should I do it with comma separated values instead to be more specific? I don't think it's reading my flat file correctly or at least understanding where each field entry starts and stops.

    Please any help is greatly appreciated. When I created a more basic table with more basic entries, it coped over fine:

    Code:
    ide=# SELECT * FROM zoo;
     id | fname | lname | emp_id | hire_date 
    ----+-------+-------+--------+-----------
    (0 rows)
    Now I copy the flat file into the table perfectly:

    Code:
    ide=# COPY zoo FROM '/tmp/users.txt';
    COPY 4
    ide=# SELECT * FROM zoo;
     id | fname  |  lname  | emp_id | hire_date  
    ----+--------+---------+--------+------------
      1 | Mark   | Smith   | 157952 | 2010-03-11
      2 | Susan  | Marcus  | 189427 | 2007-03-19
      3 | Jason  | Freenan | 136648 | 2009-06-03
      4 | Justin | Calhoun | 147785 | 2010-11-16
    (4 rows)
    The only difference is the one that fails has far more columns but nothing else. They're all clearly separated by one individual 'tab' spacing.

  2. #2
    Join Date
    Feb 2011
    Posts
    3
    Does your flat file have carraige returns in it or is it just one long string of unbroken text and tabs? I've had a heck of a time trying to read in flat files with no carraige returns. I eventually had to write a Perl program to convert them to .csv files.

  3. #3
    Join Date
    May 2008
    Posts
    277
    Without seeing a few lines from your file, it's difficult to tell. However, the line from your error message does not seem to match up with your table definition:

    Code:
    "1        4140        09478904812        2092-SUN-1234        8570        "
     1|-tab--|-2--|-tab--|-----3-----|-tab--|------4------|-tab--|-5--|-tab--|
    
    1 = id
    2 = fname
    3 = lname
    4 = emp_id
    5 = hire_date
    Also note the trailing tab, which COPY will interpret as signalling an additional column.

    edit:
    The only difference is the one that fails has far more columns but nothing else.
    Actually, I missed this. This is your problem. Your file must match your table definition exactly -- or at least have the same number of columns. You can specify the order of the columns in your copy statement, but the number of total columns must be identical.
    Last edited by futurity; 02-18-11 at 12:20.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •