Adding string with new line characters in a file to upload to an SQL UTF8 database

Question

I've discovered the \copy command to upload CSV/TSV files to a table. It's a very convenient method because I have a large amount of data to load at the same time.

My problem is that I have some text to load and some of them contain \n characters. Because I generate utf-8 text files, they are recognized as new line characters and can't be properly loaded in the database. Is there a way to encode them, using python function, to create my file to upload ?

Your file must match the CSV requirements if you would like to import it using CSV mode. Carriage Return is often defined as Row Separator. If it appears in a Columns, it must be escaped or enclosed into a cell quoting. Does the producer of your file can handle this and delivers quoted strings? — jlandercy, Feb 08 '16 at 10:59

score 0 · Accepted Answer · answered Feb 08 '16 at 15:51

To replace the \n I've used the python str replace method when creating my TSV file.

I replace \n by \\\\n. This method gives me some \\n in my TSV files and, because the postgreSQL insertion from the file interpret the special characters, \\n is stored as \n in the database.

So :

my_usable_string = my_string.replace("\n", "\\\\n")

Adding string with new line characters in a file to upload to an SQL UTF8 database

1 Answers1