PgAdmin Exporting text column to csv file

Question

I have a table with 3 columns - type, name, and code. The code column contains the procedure/function source code. I have exported it to a csv file using Import/Export option in PgAdmin 4 v5, but the code column does not stick to a single cell in the csv file. The data in it spreads across to many of the rows and columns. I have checked Encoding as UTF8 which works fine normally while exporting other tables.

Other settings: Format: csv, Encoding: UTF8. Have not changed any other settings

Can someone help how to export it properly.

FYI, it is pgAdmin4 v5, there is no pgAdmin 5. What where the settings you used when you set up the export? — Adrian Klaver, Mar 05 '21 at 15:38
@AdrianKlaver, thank you, edited the correct version and added the settings to the question. — Mano, Mar 05 '21 at 15:59
The default delimiter for csv is ',', I would change that to '|' to begin with. — Adrian Klaver, Mar 05 '21 at 16:08
@AdrianKlaver, changing delimiter to '|' made the format even worse. The 3rd column(of text datatype) of the table I mentioned contains function/procedure code with lot of line breaks which appears to be culprit. Because I have exported many tables where I found no issues at all. — Mano, Mar 05 '21 at 16:30
"code column does not stick to a single cell in the csv file" It almost surely does, if you define "cell" the way it is defined for csv files. If you don't want it in that format, then don't tell it to use that format. You want it exported "properly", but have provided no clue about what you think that means. — jjanes, Mar 05 '21 at 16:53
@jjanes, I want the code column data to be in a single cell of the csv file. But its not getting stored in single cell. Since the column has multiple lines with line breaks, next line is placed in next row and so on of the csv file. — Mano, Mar 05 '21 at 16:58
That is how csv works. A single cell consists of what is in the quotes, even if that includes line breaks. Apparently you don't want csv. Again, what do you want instead? — jjanes, Mar 05 '21 at 17:08
I want csv, the issue is line breaks that are in the column 'code'. I checked exporting the same to txt file and I could see lot of \n and \t in the data, which exactly caused the data to go to next rows while I was trying to get them into csv. — Mano, Mar 05 '21 at 17:30

score 2 · Accepted Answer · answered Mar 05 '21 at 19:37

An explanation of what you are seeing:

CREATE TABLE public.csv_test (
    fld_1 character varying,
    fld_2 character varying,
    fld_3 character varying,
    fld_4 character varying
);

insert into csv_test values ('1', E'line with line end. \n  New line', 'test', 'dog');
insert into csv_test values ('2', E'line with line end. \n  New line', 'test', 'dog');
insert into csv_test values ('3', E'line with line end. \n  New line \n Another line', 'test2', 'cat');
insert into csv_test values ('4', E'line with line end. \n  New line \n \t Another line', 'test3', 'cat');

select * from csv_test ;
 fld_1 |         fld_2         | fld_3 | fld_4 
-------+-----------------------+-------+-------
 1     | line with line end.  +| test  | dog
       |   New line            |       | 
 2     | line with line end.  +| test  | dog
       |   New line            |       | 
 3     | line with line end.  +| test2 | cat
       |   New line           +|       | 
       |  Another line         |       | 
 4     | line with line end.  +| test3 | cat
       |   New line           +|       | 
       |          Another line |       | 

\copy csv_test to csv_test.csv with (format 'csv');
\copy csv_test to csv_test.txt;

--fld_2 has line ends and/or tabs so in CSV the data will wrap inside the quotes.
cat csv_test.csv 
1,"line with line end. 
  New line",test,dog
2,"line with line end. 
  New line",test,dog
3,"line with line end. 
  New line 
 Another line",test2,cat
4,"line with line end. 
  New line 
         Another line",test3,cat

-- In text format the line ends and tabs are shown and not wrapped.
cat csv_test.txt 
1       line with line end. \n  New line        test    dog
2       line with line end. \n  New line        test    dog
3       line with line end. \n  New line \n Another line        test2   cat
4       line with line end. \n  New line \n \t Another line     test3   cat

PgAdmin Exporting text column to csv file

1 Answers1