How to import from a mixed-encoding file to a PostgreSQL table

Question

I have a 30 GB text file. the encoding of the file is UTF8 but it also contains some Windows-1252 characters. So, when I try to import, it gives the following error:

ERROR:  invalid byte sequence for encoding "UTF8": 0x9b

How can I fix this?

the file already has UTF8 format, when i run the 'file' command for this file it says the encoding is UTF8. but it also contains some not UTF8 byte sequences. for example when I run the \copy command after a while it gives the above mentioned error for this row:

0B012234    Basic study of <img src="/fulltext-image.asp?format=htmlnonpaginated&src=323K744431152658_html\233_2    basic study of img src fulltext image asp format htmlnonpaginated src 323k744431152658_html 233_2   1975        Semigroup Forum semigroup forum 04861B53        19555

Use `iconv` on Linux/Unix or cygwin (windows). http://stackoverflow.com/questions/4867272/invalid-byte-sequence-for-encoding-utf8/4867690#4867690 — David דודו Markovitz, Dec 29 '16 at 12:57
@DuduMarkovitz the file already has UTF8 format, when i run the 'file' command for this file it says the encoding is UTF8. but it also contains some not UTF8 byte sequences. how inconv command can help? — Ramin Zahedi, Dec 29 '16 at 13:04

score 1 · Accepted Answer · answered Dec 29 '16 at 14:08

1

The issue is caused by the backslash (\).
Use CSV format which does not treat backslash as a special character, e.g. -

\copy t from myfile.txt with csv quote E'\x1' delimiter E'\x2'

answered Dec 29 '16 at 14:08

David דודו Markovitz

42,900
6
64
88

How to import from a mixed-encoding file to a PostgreSQL table

1 Answers1

Linked