Can I automatically create a table in PostgreSQL from a csv file with headers?

by

There is a very good tool that imports tables into Postgres from a csv file.
It is a command-line tool called pgfutter (with binaries for windows, linux, etc.). One of its big advantages is that it recognizes the attribute/column names as well.

The usage of the tool is simple. For example if you’d like to import myCSVfile.csv:

pgfutter --db "myDatabase" --port "5432" --user "postgres" --pw "mySecretPassword" csv myCSVfile.csv

This will create a table (called myCSVfile) with the column names taken from the csv file’s header. Additionally the data types will be identified from the existing data.

A few notes: The command pgfutter varies depending on the binary you use, e.g. it could be pgfutter_windows_amd64.exe (rename it if you intend to use this command frequently). The above command has to be executed in a command line window (e.g. in Windows run cmd and ensure pgfutter is accessible). If you’d like to have a different table name add --table "myTable"; to select a particular database schema us --schema "mySchema". In case you are accessing an external database use --host "myHostDomain".

A more elaborate example of pgfutter to import myFile into myTable is this one:

pgfutter --host "localhost" --port "5432" --db "myDB" --schema "public" --table "myTable" --user "postgres" --pw "myPwd" csv myFile.csv

Most likely you will change a few data types (from text to numeric) after the import:

alter table myTable
  alter column myColumn type numeric
    using (trim(myColumn)::numeric)

More Related Contents:

  1. How to import CSV file data into a PostgreSQL table
  2. How to export table as CSV with headings on Postgresql?
  3. Importing .csv with timestamp column (dd.mm.yyyy hh.mm.ss) using psql \copy
  4. Why the “Group BY” by month didn’t work? [closed]
  5. Truncating all tables in a Postgres database
  6. How can I drop all the tables in a PostgreSQL database?
  7. How to change PostgreSQL user password?
  8. How to get the count of current month Sunday’s in psql?
  9. quick random row selection in Postgres
  10. Restore a postgres backup file using the command line?
  11. Running PostgreSQL in memory only
  12. Refresh a materialized view automatically using a rule or notify
  13. User defined variables in PostgreSQL
  14. PostgreSQL query very slow with limit 1
  15. How to add an auto-incrementing primary key to an existing table, in PostgreSQL?
  16. Postgres query optimization (forcing an index scan)
  17. Difference between timestamps with/without time zone in PostgreSQL
  18. Omitting the double quote to do query on PostgreSQL
  19. Psql could not connect to server: No such file or directory, 5432 error?
  20. Strange behaviour in Postgresql
  21. Postgres: Distinct but only for one column
  22. Cursor based records in PostgreSQL
  23. How to add column if not exists on PostgreSQL?
  24. Maximum characters in labels (table names, columns etc)
  25. Docker – Postgres and pgAdmin 4 : Connection refused
  26. Configuration parameter work_mem in PostgreSQL on Linux
  27. How to delete an enum type value in postgres?
  28. Why unsigned integer is not available in PostgreSQL?
  29. What’s the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)
  30. JPA and PostgreSQL with GenerationType.IDENTITY

Leave a Comment