Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-442

Proper csv export from PostgreSQL

    XMLWordPrintable

    Details

    • Type: How To
    • Status: Published
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Solution:
      Hide

      PostgreSQL

      Option 1)

      Using pgAdmin III:

      • Connect to the database you want to export
      • Open the SQL Editor by clicking the SQL-Button
      • Select the tables individually with separate SELECT Statements
      • To run the query press F5 or use the associated button
      • When the task is finished you can continue the export with opening the integrated Export data to file tool
      • To do so, open the File menu on the top left and proceed with Export from there
      • Now you are able to make some adjustments for your csv format
      • To guarantee a proper csv file we recommend the following settings
      • Select your desired filename and start the process by clicking OK
      • You will receive a message about the successful export

      Notes:

      • pgAdmin III automatically converts NULL to "" (empty string)
      • pgAdmin III automatically masks double quotes ("Example" -> ""Example"")

      Option 2)

      Using PSQL:

      • Connect to the database you want to export
      • Use the \COPY [table_name | query] TO [filename] WITH CSV command
        (Documentation: https://www.postgresql.org/docs/current/static/sql-copy.html)
      • Add the following parameters to export a proper csv file:
        o FORCE QUOTE * (all values will be quoted)
        o ENCODING ‘utf8’ (specifies encoding option)

      Example:

      • Start the export by pressing Enter
      • You will receive a confirmation about the number of rows that have been exported

      Notes:

      • PSQL automatically converts NULL to "" (empty string)
      • PSQL automatically masks double quotes ("Example" -> ""Example"")
      Show
      PostgreSQL Option 1) Using pgAdmin III : Connect to the database you want to export Open the SQL Editor by clicking the SQL-Button Select the tables individually with separate SELECT Statements To run the query press F5 or use the associated button When the task is finished you can continue the export with opening the integrated Export data to file tool To do so, open the File menu on the top left and proceed with Export from there Now you are able to make some adjustments for your csv format To guarantee a proper csv file we recommend the following settings Select your desired filename and start the process by clicking OK You will receive a message about the successful export Notes: pgAdmin III automatically converts NULL to "" (empty string) pgAdmin III automatically masks double quotes ("Example" -> ""Example"") Option 2) Using PSQL : Connect to the database you want to export Use the \COPY [table_name | query] TO [filename] WITH CSV command (Documentation: https://www.postgresql.org/docs/current/static/sql-copy.html ) Add the following parameters to export a proper csv file: o FORCE QUOTE * (all values will be quoted) o ENCODING ‘utf8’ (specifies encoding option) Example: Start the export by pressing Enter You will receive a confirmation about the number of rows that have been exported Notes: PSQL automatically converts NULL to "" (empty string) PSQL automatically masks double quotes ("Example" -> ""Example"")
    • Category 1:
      ETL / Data Integration
    • Category 2:
      3rd Party Tools - ETL

      Attachments

        Issue Links

        1. PostgreSQL1.png
          PostgreSQL1.png
          50 kB
        2. PostgreSQL2.png
          PostgreSQL2.png
          30 kB
        3. PostgreSQL3.png
          PostgreSQL3.png
          29 kB
        4. PostgreSQL4.png
          PostgreSQL4.png
          13 kB
        5. PostgreSQL5.png
          PostgreSQL5.png
          59 kB
        6. PostgreSQL6.png
          PostgreSQL6.png
          9 kB
        7. PostgreSQL7.png
          PostgreSQL7.png
          10 kB

          Activity

            People

            • Assignee:
              CaptainEXA Captain EXASOL
              Reporter:
              CaptainEXA Captain EXASOL
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated: