Re: 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(dot)klaver(at)aklaver(dot)com