How to import headerless csv into postgresql with django_postgres_copy?

Question

I have a massive csv file to import into Postgres and my django model is all done, my trouble is that the csv file doesn't have any headers that i can map to, and I'm trying to use postgres_copy http://django-postgres-copy.readthedocs.io/en/latest/ to do this for me, but I can't find a way to do it without headers.

'123131','data','data','d','d','123112','d'

That's how my csv looks like. And I have like 5 million lines. If there is other methods I'm open to them as well.

from .models import MyModel
from postgres_copy import CopyMapping
from django.core.management.base import BaseCommand
import os

class DataToPostGres(BaseCommand):
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
DATA_ROOT = os.path.join(BASE_DIR, 'data/bigcsv.csv')

def handle(self, *args, **kwargs):
    c = CopyMapping(
        # Give it the model
        MyModel,
        # The path to your CSV
        DATA_ROOT,
        # And a dict mapping the  model fields to CSV headers
        dict(name='NAME', number='NUMBER', dt='DATE')
    )
    # Then save it.
    c.save()

Here is what I have so far but it can obviously not work cause I can't map my model fields to any CSV headers.

I looked around but I couldn't find anything that answers my question so far. Thank you in advance.

bimsapi · Accepted Answer · 2016-11-22 14:57:15Z

You can go straight to the psycopg2 driver, and use the copy command directly (which lets you map columns without having a header). Something like:

from django.db import connection

sql = 'copy table_name (col1, col2, col3) from stdin with (format csv)'
with open(DATA_ROOT) as infile:
    with connection.cursor() as stmt:
        stmt.copy_expert(sql, infile)

You specify the cols in the order they appear in the .csv, but note that the copy command is sensitive to data formatting and integrity - malformed dates, numbers, bools, as well as integrity checks, will cause the load to fail. As a workaround, I've used copy to load into temporary tables and done a "cleaning" load into model tables via more robust SQL. Your mileage may vary...

See http://initd.org/psycopg/docs/cursor.html#cursor.copy_expert And https://www.postgresql.org/docs/9.5/static/sql-copy.html

Hope this helps.

Collectives™ on Stack Overflow

How to import headerless csv into postgresql with django_postgres_copy?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related