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
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';
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'
ERROR: DELIMITER for COPY must be a single one-byte character\tab. Thx for your help!
(FORMAT CSV, DELIMITER E'\t', HEADER true) after the 'FROM' clause. Hope this helps anyone.Sad to say, but the easiest way is to convert the TSV to a CSV. Most of the built in Postgres import utilities to do things like converting empty strings to nulls, skipping headers, etc are Only for CSV.
See this simple 6 line Python answer on SO. I use it and then CSV loaded like normal without a problem in Postgres after trying for and hour to load a TSV.
