0

I'm trying to export data from PostgreSQL and import it into SAP Hana. The problem which is '\n' i.e. line breaks are getting automatically removed from the TEXT data.

Example: 

A 1,750 word essay is 11 to 12 paragraphs.
A 2,000 word essay is 13 to 14 paragraphs.
A 2,500 word essay is 16 to 17 paragraphs.
A 3,000 word essay is 20 paragraphs.

Is becoming 

A 1,750 word essay is 11 to 12 paragraphs.A 2,000 word essay is 13 to 14 paragraphs.A 2,500 word essay is 16 to 17 paragraphs.A 3,000 word essay is 20 paragraphs.

I'm using below commands:

PostgreSQL 

\COPY table_name TO 'path\data.csv' WITH CSV DELIMITER ',';

Hana

IMPORT FROM CSV FILE path
INTO table_name
WITH RECORD DELIMITED BY '\n'
FIELD DELIMITED BY '\t'
OPTIONALLY ENCLOSED BY '"';
Raj Paliwal
  • 943
  • 1
  • 9
  • 22
Prashant Singh
  • 1
  • 1

2 Answers2

0

To have the \ to be recognized as a escape character it is necessary to use the text format.

COPY table_name  from 'path\data.csv' delimiter ',' TEXT

Source : Postgres COPY command with literal delimiter

redhatvicky
  • 1,912
  • 9
  • 8
  • My question was export from PostgreSQL. I've data in the table with line breaks(\n) in text fields. It should get exported with line break between texts. That is \n will not be there after getting exported from PostgreSQL – Prashant Singh Dec 02 '19 at 11:42
  • Exactly , But when we copy using the copy command , It should understand the \n as line breaks , In order to do that we have to copy it as TEXT , So that you can get the line breaks in the data too, Did you try copying the data and check how the data is ? – redhatvicky Dec 02 '19 at 12:01
  • That command is not working - ERROR: value too long for type character varying(3). I've data with a field which has ',' as field data. That might be the reason. – Prashant Singh Dec 02 '19 at 12:32
  • try this will export with line breaks, psql yourdatabase -c "COPY (SELECT * FROM table_to_export) TO STDOUT" > data.csv – redhatvicky Dec 02 '19 at 13:02
0

Instead of exporting and importing CSV files, you could use DBeaver, which supports both - SAP HANA and PostgreSQL.

An instruction for moving data between dbms can be found here: https://dbeaver.com/docs/wiki/Data-migration

Mathias Kemeter
  • 933
  • 2
  • 11