Absolutely—pgAdmin can import most CSV files into PostgreSQL tables, but you’ll need to fix the issues you’re hitting first. Let’s break down your problems and walk through solutions:

1. Fixing the pgAdmin "extra data after last expected column" error #

This error pops up when your CSV has more columns than the table you created, or unescaped special characters (like commas inside fields) confuse the parser. Here’s how to resolve it:

• Map CSV structure to your table first: Open your CSV file, count its columns, and note each column’s data type (text, integer, date, etc.). When creating the table in pgAdmin, ensure the number of columns, their order, and data types exactly match the CSV.
• Handle special characters in CSV: If any fields contain commas (e.g., "Doe, Jane"), set the Quote character to " in the pgAdmin import dialog’s "Options" tab. This tells PostgreSQL to treat text inside quotes as a single field.
• Clean malformed rows: Check line 2 of your Test_table CSV (called out in the error) to see if there’s an extra comma, trailing space, or invalid data adding an unexpected column.
• Verify header settings: When selecting "header" as columns, make sure your CSV’s first row matches the table’s column names exactly (case sensitivity might matter depending on your PostgreSQL config).

2. Fixing the psycopg2 "UndefinedTable" error #

Your Python code has a small syntax issue with how you reference the table. The copy_from method expects the table name as a string, not a Python-style schema.table reference. Update your code like this:

import psycopg2
conn = psycopg2.connect("host='xxx.xx.xx.x' port='5432' dbname='postgres' user='abc' password='xxx'")
cur = conn.cursor()
f = open(r'test.csv', 'r')
# Wrap the table name in quotes to specify the schema correctly
cur.copy_from(f, 'public.test', sep=',')
# Don't forget to commit the transaction!
conn.commit()
f.close()
cur.close()
conn.close()

• Also, confirm you’ve actually created the test table in the public schema before running this code. If the table exists but you still get the error, try quoting the table name like "public"."test" to avoid case sensitivity issues.

Final Answer: Yes, pgAdmin can import your CSV files #

As long as you:

• Match the CSV’s column count, order, and data types to your table (or use pgAdmin’s import wizard to auto-create the table for you)
• Configure import settings correctly (separator, quote character, header option)
• Clean up any malformed rows or special characters in the CSV

For a smoother workflow, try pgAdmin’s full Import/Export Data wizard (right-click your database or schema → Import). It lets you preview the CSV, auto-detect columns, and create the table on the fly if you don’t have one already.

内容的提问来源于stack exchange，提问作者The Great