Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a text file with | delimited data that I want to import to a table in PostgreSQL database. PgAdminIII only exports CSV files. I converted the file to a CSV file using MS excel but still was unsuccessful importing data to PostgreSQL database.

It says an error has occurred: Extradata after last expected column. CONTEXT: COPY , line1:

What I am doing wrong here?

Column header:

KNUMBER,APPLICANT,CONTACT,STREET1,STREET2,CITY,STATE,ZIP,DEVICENAME,DATERECEIVED,DECISIONDATE,DECISION,REVIEWADVISECOMM,PRODUCTCODE,STATEORSUMM,CLASSADVISECOMM,SSPINDICATOR,TYPE,THIRDPARTY,EXPEDITEDREVIEW,,  

Sample data:

K000001,BOSTON SCIENTIFIC SCIMED/ INC.,RON  BENNETT,5905 NATHAN LN.,,MINNEAPOLIS,MN,55442,WALLGRAFT TRACHEOBRONCHIAL ENDOPROSTHESIS AND UNISTEP DELIVERY SYSTEM,1/3/00,6/5/00,SE,SU,JCT,Summary,SU,,Traditional,N,N,,
K000002,USA INSTRUMENTS/ INC.,RONY  THOMAS,1515 DANNER DR.,,AURORA,OH,44202,MAGNA 5000 PHASED ARRAY CTL SPINE COIL,1/3/00,2/23/00,SE,RA,MOS,Summary,RA,,Traditional,N,N,,

Table Schema:

CREATE TABLE medicaldevice1
(
  medical_device_id serial NOT NULL,
  k_number character varying(8),
  applicant character varying(150) NOT NULL,
  contact character varying(50),
  street1 character varying(80),
  street2 character varying(40),
  city character varying(50),
  state character varying(8),
  zip character varying(16),
  device_name character varying(500) NOT NULL,
  date_received character varying(8),
  decision_date character varying(8),
  decision character varying(2),
  review_advise_comm character varying(2),
  product_code character varying(3),
  state_or_summary character varying(16),
  class_advise_comm character varying(2),
  ssp_indicator character varying(25),
  third_party character varying(2),
  expedited_review character varying(4),
  CONSTRAINT medical_device_id_pk PRIMARY KEY (medical_device_id)
)
share
3  
It seems that the structure of the CSV file didn't match that of the PostgreSQL table, you should post an extract of the first few lines of the file and the schema of the table if you want to get a proper answer. –  toro2k Jun 2 '13 at 7:42
    
For starters, to ask a serious question, you need to provide your PostgreSQL version, the full command you are using, some sample data and the table definition. –  Erwin Brandstetter Jun 2 '13 at 17:15
1  
Unix and Windows use different characters for end-of-line (also called new line). The Windows newline is two characters, and sometimes Unix programs that are expecting Unix formatted input see the second part of the Windows end-of-line as "extra". And it's invisible. There are utilities (e.g., dos2unix) that will convert line endings. Of course, it is also possible you just have the wrong number of commas in a line. –  Andrew Lazarus Jun 4 '13 at 18:55
1  
We still don't know what your command is. Could you please include it, too? –  dezso Jun 5 '13 at 8:19
1  
Your header (and data) contains an "empty" column at the end, because it ends with two commas: ..EXPEDITEDREVIEW,, –  a_horse_with_no_name Jun 5 '13 at 9:43
show 5 more comments

migration rejected from stackoverflow.com Jun 8 '13 at 11:00

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as too localized by Jack Douglas Jun 8 '13 at 11:00

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.