PostgreSQL use wrong delimiter

Question

I'm trying to add data from a .txt file to an empty table in postgreSQL database with pgAdmin. The .txt file uses '|' as delimiter. For example 321|hello/hi|[email protected]

The table has columns id name email

COPY table FROM 'data.txt'
    using delimiters '|';

The error says the "email" column is missing. I guessed SQL tried to also use "" as delimiter.

ERROR:  missing data for column "email"
CONTEXT:  line 100: "321|"hello"
SQL state: 22P04

How to fix this error ? thanks

Answer 1

It seems the only way is to clean the data so I imported files to python and cleaned ''. Then load the cleaned dataframe to postgreSQL.

param_dic = {
    "host"      : "localhost",
    "database"  : "database",
    "user"      : "user",
    "password"  : "password"
}

def connect(params_dic):
    """ Connect to the PostgreSQL database server """
    conn = None
    try:
        # connect to the PostgreSQL server
        print('Connecting to the PostgreSQL database...')
        conn = psycopg2.connect(**params_dic)
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    print("Connection successful")
    return conn

def execute_many(conn, df, table):
    """
    Using cursor.executemany() to insert the dataframe
    """
    # Create a list of tupples from the dataframe values
    tuples = [tuple(x) for x in df.to_numpy()]
    # Comma-separated dataframe columns
    cols = ','.join(list(df.columns))
    # SQL quert to execute
    query  = "INSERT INTO %s(%s) VALUES(%%s,%%s,%%s)" % (table, cols)
    cursor = conn.cursor()
    try:
        cursor.executemany(query, tuples)
        conn.commit()
    except (Exception, psycopg2.DatabaseError) as error:
        print("Error: %s" % error)
        conn.rollback()
        cursor.close()
        return 1
    print("execute_many() done")
    cursor.close()

More methods can be found here https://naysan.ca/2020/05/09/pandas-to-postgresql-using-psycopg2-bulk-insert-performance-benchmark/