Can make it work with amd64.exe or GO source

[Jeftyo]

Hello everyone,

I have the same problem as other people here, when I use pgfutter_windows_amd64.exe it doesn't work and I don't get an error message, i go to a new line on the prompt. I used a simple command like this:

pgfutter_windows_amd64.exe --db "MyBDD" --schema "public" --port "5432" --user "postgres" --table "STATE" --pw "test" csv STATE.csv

The CSV file is in the repertory.
I check the user and port on the parameter.
I renamed all the columns to exclude the SQL keyword.
I tried using --delimiter ";" like that :

pgfutter_windows_amd64.exe --db "MyBDD" --schema "public" --port "5432" --user "postgres" --table "STATE" --pw "test" csv --delimiter ";" STATUS.csv

Same problem.

I then tried following #67 and worked on the source package to add the change to the pgfutter.go file with Notepad.
Then installed GO for Windows and Git.
When I used "go version" at the prompt, it works.
I then tried this on the file repertory :

pgfutter.go --db "MyBDD" --schema "public" --port "5432" --user "postgres" --table "STATE" --pw "test" csv --delimiter ";" STATUS.csv

and

pgfutter --db "MyBDD" --schema "public" --port "5432" --user "postgres" --table "STATE" --pw "test" csv --delimiter ";" STATUS.csv

Do not work.
I'm not really sure what I'm doing here with the GO file

Can someone help my to make it work?
Thanks.

Jefty

[Jeftyo]

I solved my problem with a function from another forum.
I share for those who don't have pgfutter working :

https://stackoverflow.com/questions/21018256/can-i-automatically-create-a-table-in-postgresql-from-a-csv-file-with-headers

Basically you call a function within Postgres (last argument specifies the number of columns).

select load_csv_file('myTable','C:/MyPath/MyFile.csv',24)

copy&paste into PgAdmin SQL Editor or Query Tool 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$

declare

iter integer; -- dummy integer to iterate columns with
col text; -- variable to keep the column name at each iteration
col_first text; -- first column name, e.g., top left corner on a csv file or spreadsheet

begin
    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;

I changed the delimiter for ";" in my case, don't forget to do it too if you need.
Also i got an error "permission denied" error for my file in Download rep, i copy the file on root C: and it work perfectly.

Jefty

[MajorOversight]

Many thanks Jefty for posting that solution here, as I've been having a number of issues using pgfutter recently and have been seeking simple alternatives that do not require python/dbeaver/etc.

I've taken the code and made a few additions so that the columns can be automatically calculated and the column names formatted as per pgfutter's lowercase with underscores standard (to maintain compatibility with existing work) and am posting it back here in case it can assist you or anyone else that has also been experiencing issues with pgfutter.

On Windows this requires a copy of head.exe (from UnxUtils or a similar package) and updating the call to reflect your own path to the utility. On Linux, you should alter the line with the call to head and remove the path (and possibly the quotes "" around %s?).

-- nb: the function assumes the first line of the csv file contains headers
-- original code sourced via https://stackoverflow.com/questions/21018256/can-i-automatically-create-a-table-in-postgresql-from-a-csv-file-with-headers
-- modified to format csv headers per pgfutter standard - lowercase names with non-([a-z],[0-9]) characters replaced with underscore _
-- modifed to automatically calculate the number of columns in the csv file
-- on windows, relies on head.exe from unxutils package to read just the first line of the csv file in order to compute number of columns
-- example: select load_csv_file('faults_temp','C:\Work\open_faults_210514.csv')

CREATE OR REPLACE FUNCTION load_csv_file(
    target_table text,
    csv_path text)
  RETURNS void AS
$BODY$

declare

iter integer;       -- dummy integer to iterate columns with
col text;           -- variable to keep the column name at each iteration
col_count integer;  -- variable to keep the number of columns in the csv file
col_first text;     -- first column name, e.g., top left corner on a csv file or spreadsheet
col_first_new text; -- first column name after reformatting

begin
    set schema 'public';
	
	create temp table columns_tmp_table (content text);  -- temp table used to compute number of columns
	execute format('copy columns_tmp_table from program ''C:\XML\unx\head -n 1 "%s"''', csv_path);  -- update with location of the utility / path not needed on linux
	col_count := (select array_length(string_to_array(content, ','), 1) from columns_tmp_table);
	drop table columns_tmp_table;

    create table import_temp_table ();  -- placeholder table name, changed to desired table name later

    -- add just enough number of columns
    for iter in 1..col_count
    loop
        execute format('alter table import_temp_table add column col_%s text;', iter);
    end loop;

    -- copy the data from csv file
    execute format('copy import_temp_table from %L with delimiter '','' quote ''"'' csv ', csv_path);

    iter := 1;
    col_first := (select col_1 from import_temp_table limit 1);
	col_first_new := lower(translate(col_first,' ,.,?,\,/,<,>,[,],{,},:,~,!,@,#,$,%,^,&,*,(,),-,+,=','_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_'));

    -- 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(import_temp_table::text, ''()''), '','')) from import_temp_table where col_1 = %L', col_first)
    loop
        -- execute format('alter table import_temp_table rename column col_%s to %s', iter, col);
		-- ' ,.,?,\,/,<,>,[,],{,},:,~,!,@,#,$,%,^,&,*,(,),-,+,='
		-- '_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_'
	execute format('alter table import_temp_table rename column col_%s to %s', iter, lower(translate(col,' ,.,?,\,/,<,>,[,],{,},:,~,!,@,#,$,%,^,&,*,(,),-,+,=','_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_')));
        iter := iter + 1;
    end loop;

    -- delete the columns row
    execute format('delete from import_temp_table where %s = %L', col_first_new, 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 import_temp_table rename to %I', target_table);
    end if;

end;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION load_csv_file(text, text)
  OWNER TO postgres;

The above code is also available from the following text file:
import_csv_function.txt