Обсуждение: Suggestion to reduce COPY command output to csv file

On 3/4/20 10:38 AM, postggen2020 s wrote:
> Hi Team,
> Thanks a lot all of you, for providing support peoples like me.
> Could you please provide a suggestion on COPY command.
> 
> Environment:
> DB Version:9.5.15
> postgis:
> Table contain GIS data.
> I have a table with GIS data, its around 300MB and 2Lacks+ records. I 
> want to export all the records to the CSV file. I can able to export the 
> table data into a CSV file, but the exported CSV file size around 162MB.
> While I am trying to opening the excel it is taking a long time and also 
> while applying vlookup excel is getting hang.
> I am using below command :
> \copy cities TO '/var/lib/pgsql/cities.csv' (DELIMITER ',', FORMAT 
> 'csv', HEADER)
> I am trying to achieve the following :
> 1. The exported CSV file should have within 10MB.

You can select less then the entire table with a query:

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

query

     A SELECT or VALUES command whose results are to be copied. Note 
that parentheses are required around the query.


> 2. The excel file should open quickly and able to perform vlookups 
> without any problems(like hang).

That is something you will need to take up with MS.

> Could you please provide suggestions on below queries:
> 1. what I am trying to achieve, is the right assumption?.
> 2. Can we use this command to use for the above use case?.
> 3. Is there any tool/extension available for the above use case.?

The MS Office data tools for pulling directly from database into 
spreadsheet via ODBC.

Do you use a programming language?
If so use its libraries to pull data out and into CSV or directly into a 
spreadsheet.


> Your inputs are highly appreciated.
> Regards,
> Postgadm.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 3/4/20 10:38 AM, postggen2020 s wrote:
> Hi Team,
> Thanks a lot all of you, for providing support peoples like me.
> Could you please provide a suggestion on COPY command.
> 
> Environment:
> DB Version:9.5.15
> postgis:
> Table contain GIS data.
> I have a table with GIS data, its around 300MB and 2Lacks+ records. I 
> want to export all the records to the CSV file. I can able to export the 
> table data into a CSV file, but the exported CSV file size around 162MB.
> While I am trying to opening the excel it is taking a long time and also 
> while applying vlookup excel is getting hang.
> I am using below command :
> \copy cities TO '/var/lib/pgsql/cities.csv' (DELIMITER ',', FORMAT 
> 'csv', HEADER)
> I am trying to achieve the following :
> 1. The exported CSV file should have within 10MB.

You can select less then the entire table with a query:

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

query

     A SELECT or VALUES command whose results are to be copied. Note 
that parentheses are required around the query.


> 2. The excel file should open quickly and able to perform vlookups 
> without any problems(like hang).

That is something you will need to take up with MS.

> Could you please provide suggestions on below queries:
> 1. what I am trying to achieve, is the right assumption?.
> 2. Can we use this command to use for the above use case?.
> 3. Is there any tool/extension available for the above use case.?

The MS Office data tools for pulling directly from database into 
spreadsheet via ODBC.

Do you use a programming language?
If so use its libraries to pull data out and into CSV or directly into a 
spreadsheet.


> Your inputs are highly appreciated.
> Regards,
> Postgadm.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

You can use this alternative:

\copy (SELECT col1, col2, col3... from cities) TO '/var/lib/pgsql/cities.csv' (DELIMITER ',', FORMAT 'csv', HEADER)

The list col1, col2, col3... should only contain names on necessary columns and not under any circumstances  the geometry column

-- 
Med venlig hilsen / Kind regards

Bo Victor Thomsen

\copy cities TO '/var/lib/pgsql/cities.csv' (DELIMITER ',', FORMAT 'csv', HEADER)