Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Insert `tsv` files into postgresql db

I have several files which are saved as tsv. I want to insert them into a postgresql db, to analyze them with sql.

However, my problem is how to INSERT this tsv files into postgresql 9.2 under windows 7?

I appreciate your reply!

PS.: I have created the table with the right values like:

CREATE TABLE ratings (distribution VARCHAR, votes VARCHAR, rank FLOAT, title VARCHAR);

the file is in the directory:

C:/Users/testUser/Desktop/TSV/ratings.list.tsv

like image 463
Carol.Kar Avatar asked Dec 08 '13 15:12

Carol.Kar


People also ask

Can you import CSV into PostgreSQL?

PgAdmin is a Graphical User Interface (GUI) that allows businesses to import data into PostgreSQL databases. With this service, you can convert CSV files into acceptable PostgreSQL database formats, and import the CSV into your PostgreSQL format.


3 Answers

You want something like this:

COPY ratings FROM 'C:/Users/testUser/Desktop/TSV/ratings.list.tsv' DELIMITER E'\t';

The E'\t' is required, otherwise you'll get an error like this:

ERROR: DELIMITER for COPY must be a single one-byte character


If the columns in your TSV don't line up perfectly with your table, you can also define the mapping by doing the following:

COPY ratings (column_1, column_2, ... column_n)
  FROM 'C:/Users/testUser/Desktop/TSV/ratings.list.tsv'
  DELIMITER E'\t';
like image 186
Ryan Atallah Avatar answered Oct 16 '22 14:10

Ryan Atallah


For tab separated values, you can use COPY:

http://www.postgresql.org/docs/current/static/sql-copy.html

Depending on the exact format of your file, it could be something like:

COPY ratings FROM 'C:/Users/testUser/Desktop/TSV/ratings.list.tsv' DELIMITER '\t'
like image 38
Denis de Bernardy Avatar answered Oct 16 '22 14:10

Denis de Bernardy


I'm able to do this with csvsql from csvkit.

To read a TSV (or CSV) and create/insert it to a table, my command line script looks like this:

csvsql --insert input.tsv  --table table_t --tabs --no-constraints --db postgresql://user:passwd@localhost/mydb
like image 45
philshem Avatar answered Oct 16 '22 16:10

philshem