Can I automatically create a table in PostgreSQL from a csv file with headers?
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)
There is a second approach, which I found here (from mmatt). Basically you call a function within Postgres (last argument specifies the number of columns).
select load_csv_file('myTable','C:/MyPath/MyFile.csv',24)
Here is mmatt's function code, which I had to modify slightly, because I am working on the public schema. (copy&paste into PgAdmin SQL Editor and run it to create the function)
CREATE OR REPLACE FUNCTION load_csv_file( target_table text, csv_path text, col_count integer) RETURNS void AS$BODY$declareiter integer; -- dummy integer to iterate columns withcol text; -- variable to keep the column name at each iterationcol_first text; -- first column name, e.g., top left corner on a csv file or spreadsheetbegin set schema 'public'; create table temp_table (); -- add just enough number of columns for iter in 1..col_count loop execute format('alter table temp_table add column col_%s text;', iter); end loop; -- copy the data from csv file execute format('copy temp_table from %L with delimiter '','' quote ''"'' csv ', csv_path); iter := 1; col_first := (select col_1 from temp_table limit 1); -- update the column names based on the first row which has the column names for col in execute format('select unnest(string_to_array(trim(temp_table::text, ''()''), '','')) from temp_table where col_1 = %L', col_first) loop execute format('alter table temp_table rename column col_%s to %s', iter, col); iter := iter + 1; end loop; -- delete the columns row execute format('delete from temp_table where %s = %L', col_first, col_first); -- change the temp table name to the name given as parameter, if not blank if length(target_table) > 0 then execute format('alter table temp_table rename to %I', target_table); end if;end;$BODY$ LANGUAGE plpgsql VOLATILE COST 100;ALTER FUNCTION load_csv_file(text, text, integer) OWNER TO postgres;
Note: There is a common issue with importing text files related to encoding. The csv file should be in UTF-8 format. However, sometimes this is not quite achieved by the programs, which try to do the encoding. I have overcome this issue by opening the file in Notepad++ and converting it to ANSI and back to UTF8.
I am using csvsql
to generate the table layout (it will automatically guess the format):
head -n 20 table.csv | csvsql --no-constraints --tables table_name
And then I use \COPY
in psql
. That's for me the fastest way to import CSV file.
You can also use sed
with csvsql
in order to get the desired datatype:
head -n 20 table.csv | csvsql --no-constraints --tables table_name | sed 's/DECIMAL/NUMERIC/' | sed 's/VARCHAR/TEXT/'