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

Поиск
Список
Период
Сортировка

Suggestion to reduce COPY command output to csv file

От
postggen2020 s
Дата:
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.
2. The excel file should open quickly and able to perform vlookups without any problems(like hang).
 
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.?
 
Your inputs are highly appreciated.
 
Regards,
Postgadm.

Re: Suggestion to reduce COPY command output to csv file

От
Adrian Klaver
Дата:
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



Re: Suggestion to reduce COPY command output to csv file

От
Adrian Klaver
Дата:
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



Re: Suggestion to reduce COPY command output to csv file

От
"David G. Johnston"
Дата:
On Wed, Mar 4, 2020 at 11:39 AM postggen2020 s <postggen2020@gmail.com> 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.

Then you probably need to redefine your problem and solution.
 
2. The excel file should open quickly and able to perform vlookups without any problems(like hang).

See #1
 
 
Could you please provide suggestions on below queries:
1. what I am trying to achieve, is the right assumption?.

Not really...
 
2. Can we use this command to use for the above use case?.

Not without changing your approach to the problem...in which case \copy may or may not be a useful tool

3. Is there any tool/extension available for the above use case.?

Not sure on an additive suggestion but you probably can get considerable mileage by removing Excel from the equation and bring the external data into PostgreSQL and use joins (i.e., SQL's version of VLOOKUP).

David J.

Re: Suggestion to reduce COPY command output to csv file

От
"David G. Johnston"
Дата:
On Wed, Mar 4, 2020 at 11:39 AM postggen2020 s <postggen2020@gmail.com> 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.

Then you probably need to redefine your problem and solution.
 
2. The excel file should open quickly and able to perform vlookups without any problems(like hang).

See #1
 
 
Could you please provide suggestions on below queries:
1. what I am trying to achieve, is the right assumption?.

Not really...
 
2. Can we use this command to use for the above use case?.

Not without changing your approach to the problem...in which case \copy may or may not be a useful tool

3. Is there any tool/extension available for the above use case.?

Not sure on an additive suggestion but you probably can get considerable mileage by removing Excel from the equation and bring the external data into PostgreSQL and use joins (i.e., SQL's version of VLOOKUP).

David J.

Re: Suggestion to reduce COPY command output to csv file

От
Bo Victor Thomsen
Дата:

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



Den 04-03-2020 kl. 19:38 skrev postggen2020 s:
\copy cities TO '/var/lib/pgsql/cities.csv' (DELIMITER ',', FORMAT 'csv', HEADER)