How can I remove tailing white space while loading data from CSV file into an Postgres Table?

Question

I want to remove the trailing whitespaces from CSV file.

Sample CSV file Data:(Delimitor=";")

X ;Y;Z

X1 ; Y1;Z1

X2;Y2; Z2

I would have gone for something like SED or GREP but the file size is huge so it may impact the performance because of preprocessing.

I am looking for a way to remove these whites spaces at the time of loading only.

Vao Tsun · Accepted Answer · 2017-11-16T08:34:07.550

2

COPY command does not support preprocessing - you can't do it "at the time of loading "

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

In CSV format, all characters are significant. A quoted value surrounded by white space, or any characters other than DELIMITER, will include those characters. This can cause errors if you import data from a system that pads CSV lines with white space out to some fixed width. If such a situation arises you might need to preprocess the CSV file to remove the trailing white space, before importing the data into PostgreSQL.

I think here the best solution would be importing data with spaces and then

update t set attr = rtim(attr);

edited Nov 16 '17 at 08:34

answered Nov 16 '17 at 08:32

Vao Tsun

47,234
13
100
132

Do you have any optimised way to do this at the time of preprocessing? – Akshay Patil Nov 16 '17 at 08:34
I just answered a very similar question https://stackoverflow.com/a/47324806/5315974 - import with space and then update removong it – Vao Tsun Nov 16 '17 at 08:35
Even for importing with spaces I am getting this error `ERROR: invalid byte sequence for encoding "UTF8": 0xa0` – Akshay Patil Nov 16 '17 at 08:37
Regarding this issue, you would probably have to check here - https://stackoverflow.com/questions/4867272/invalid-byte-sequence-for-encoding-utf8 – Georgi Raychev Nov 16 '17 at 08:39
@AkshayPatil encoding not matching for you csv file content and db settings is not related to OP – Vao Tsun Nov 16 '17 at 08:40
This error is coming because of the white spaces in file. – Akshay Patil Nov 16 '17 at 08:41

How can I remove tailing white space while loading data from CSV file into an Postgres Table?

1 Answers1