CODEWITHSUNDEEP
postgresqlcsv
SillyPerson
SillyPerson

Reputation: 619

import big csv with lots of columns into postgreSQL

I have a csv file with 2550 columns and I want to import it into postgreSQL.

For importing csv in postgreSQL at first I should create the table and them use \copy to copy from csv to table. but what if the table has huge amount of columns like my case that I cannot create table manually.

any solution?

Update

Data structure is as following: dZ(01) till dZ(2550) are basically between -50 to +50:

id | date    | time      | localtime | pid | dZ(0)..dZ(1)......dZ(2550)|
---|---------|-----------|-----------|-----|---------------------------|
17|11-11-2014| 16:33:21 | 1.45E+15   |2375 |0 0 0 0 0 -1 0 -1 0 -5 -10|

CSV structure: (I used '';' delimiter)

17;13-11-2014;08:09:37;1.45E+15;4098;0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 -4 3 0 -2 3 -2 1 0 0 1 1 3 -2 3 4 2 -2 -2 ....

This is one line of data.

Upvotes: 1

Views: 4190

Answers (3)

Iqbal Singh
Iqbal Singh

Reputation: 21

Bit old to reply, just incase for those who still looking. This can be done using python.

Considering your data in a DataFrame as df.

df= df.rename(columns=str.lower)

from sqlalchemy import create_engine
engine = create_engine('postgresql://username:password@localhost:5432/mydatabase')

df.to_sql('table_name', engine)

Read more here

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

Import the dZ column into a text column and later turn it into an array:

Create the temporary table:

create table s (
    id int,
    date date,
    time time,
    localt double precision,
    pid int,
    dz text
);

Set the date style:

set datestyle = 'DMY';

Import into the temporary table;

\copy s from '/home/cpn/codigo/file.csv' with (format csv, delimiter ';')

Create the definitive table merging date and time into a timestamp and turning dZ into an array:

create table t (
    id int,
    datetime timestamp,
    localt double precision,
    pid int,
    dz integer[]
);

Populate the definitive from the temporary:

insert into t (id, datetime, localt, pid, dz)
select
    id, date + time, localt, pid,
    regexp_split_to_array(dz, '\s')::int[]
from s

Upvotes: 3

SillyPerson
SillyPerson

Reputation: 619

Considering that the maximum is 1600 columns, so i devided the csv into two tables of each 1000, I wrote a python code to create dz columns and it works fine:

import psycopg2
sqlstring="CREATE TABLE z(id bigint, date date, time time,"
for i in range(1001):
    if(i<1000):
        sqlstring+="dz"+str(i)+ " int,"
    elif i==1000:
        sqlstring+="dz"+str(i)+ " int"        
sqlstring += ");"

connection = psycopg2.connect("dbname='mydb' user='qfsa' host='localhost' password='123' port=5433")
cursor=connection.cursor();
cursor.execute(sqlstring)
connection.commit()

now i could import csv into the table.

Upvotes: 0

Related Questions