PostgreSQL use copy with SQLConnector Lazarus 2.2.2 Windows 10 64bits lazarus / 16-Databases ---------------------------------------------------------------------- Root2 · 2022-09-02 21:36 dear community, a cordial greeting to all, after trying to execute without success by batch the postgres copy command I decided to try with lazarus, for that I am using a SQLConnector to execute this command, but I am not getting to do it, it seems that I am missing something else, this is the code that I am using. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.Button1Click(Sender: TObject);var  consulta: String;begin  begin   consulta:='COPY recetasv1(fecha, hora, sapreceta, lote, orden, coordenada, sapinsumo, volumen, tiempo, temperatura, velagitador, velancla, velobular, velsilverson) FROM 'C:\Borrame\satosap.csv' DELIMITER ',' CSV HEADER';  SQLQuery1.Close;  SQLQuery1.SQL.Clear;  SQLQuery1.SQL.Add(consulta);  SQLQuery1.ExecSQL; end;end.     when executing it I get this error. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Compilar proyecto, Objetivo: project1.exe: Código de salida 1, Errores: 1unit1.pas(42,173) Fatal: Syntax error, ";" expected but "identifier C" found it seems that it recognized that it is not sql so it gives me an error. Maybe my approach is wrong? Thank you for your kind attention. ---------------------------------------------------------------------- dsiders · 2022-09-02 22:58 Quote from: Root2 on September 02, 2022, 11:36:49 pmdear community, a cordial greeting to all, after trying to execute without success by batch the postgres copy command I decided to try with lazarus, for that I am using a SQLConnector to execute this command, but I am not getting to do it, it seems that I am missing something else, this is the code that I am using. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.Button1Click(Sender: TObject);var  consulta: String;begin  begin   consulta:='COPY recetasv1(fecha, hora, sapreceta, lote, orden, coordenada, sapinsumo, volumen, tiempo, temperatura, velagitador, velancla, velobular, velsilverson) FROM 'C:\Borrame\satosap.csv' DELIMITER ',' CSV HEADER';  SQLQuery1.Close;  SQLQuery1.SQL.Clear;  SQLQuery1.SQL.Add(consulta);  SQLQuery1.ExecSQL; end;end.     when executing it I get this error. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Compilar proyecto, Objetivo: project1.exe: Código de salida 1, Errores: 1unit1.pas(42,173) Fatal: Syntax error, ";" expected but "identifier C" found it seems that it recognized that it is not sql so it gives me an error. Maybe my approach is wrong? Thank you for your kind attention. Your statement in consulta is not properly quoted. Use QuotedStr(): https://www.freepascal.org/docs- html/rtl/sysutils/quotedstr.html. For UTF-8, use: https://dsiders.gitlab.io/lazdocsnext/lazutils/lazutf8 /utf8quotedstr.html. ---------------------------------------------------------------------- dseligo · 2022-09-03 07:48 As dsiders said, but you can also do it like this (use two quote signs, so you escape quote sign): Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   consulta:='COPY recetasv1(fecha, hora, sapreceta, lote, orden, coordenada, sapinsumo, volumen, tiempo, temperatura, velagitador, velancla, velobular, velsilverson) FROM ''C:\Borrame\satosap.csv'' DELIMITER '','' CSV HEADER'; ---------------------------------------------------------------------- Zvoni · 2022-09-03 09:05 What i don‘t understand: independent of ready-to-use code for processing CSV‘s, a CSV is the easiest file to process „manually“ in regards to import it to a database. 1) open CSV for reading 2) read first line 3) if you know the first line are the column headers, ignore that line, or use column headers as field names later 4) if delimiter is comma you read each following line as is, otherwise replace the delimiter with comma. Take care for delimiters within quoted parts 5) construct INSERT-command from the columnheaders and the VALUES-part from the lines you read in 4 6) encase everything inside a transaction Done At a guess: a first draft for something like this maybe 10 minutes ---------------------------------------------------------------------- dseligo · 2022-09-03 09:16 Quote from: Zvoni on September 03, 2022, 11:05:45 amWhat i don‘t understand: independent of ready-to-use code for processing CSV‘s, a CSV is the easiest file to process „manually“ in regards to import it to a database. 1) open CSV for reading 2) read first line 3) if you know the first line are the column headers, ignore that line, or use column headers as field names later 4) if delimiter is comma you read each following line as is, otherwise replace the delimiter with comma. Take care for delimiters within quoted parts 5) construct INSERT-command from the columnheaders and the VALUES-part from the lines you read in 4 6) encase everything inside a transaction Done At a guess: a first draft for something like this maybe 10 minutes But it may get complicated. For instance, values could be quoted and they must be quoted if value contain delimiter. And if OP had some CSV generated by Postgres, you could expect that they took care of processing CSV, so OP doesn't have to worry about header line, delimiters, quotes and similar. ---------------------------------------------------------------------- zeljko · 2022-09-03 10:14 Think that you're missing WITH before DELIMITER ---------------------------------------------------------------------- Zvoni · 2022-09-03 10:17 Quote from: dseligo on September 03, 2022, 11:16:43 amQuote from: Zvoni on September 03, 2022, 11:05:45 amWhat i don‘t understand: independent of ready-to-use code for processing CSV‘s, a CSV is the easiest file to process „manually“ in regards to import it to a database. 1) open CSV for reading 2) read first line 3) if you know the first line are the column headers, ignore that line, or use column headers as field names later 4) if delimiter is comma you read each following line as is, otherwise replace the delimiter with comma. Take care for delimiters within quoted parts 5) construct INSERT-command from the columnheaders and the VALUES-part from the lines you read in 4 6) encase everything inside a transaction Done At a guess: a first draft for something like this maybe 10 minutes But it may get complicated. For instance, values could be quoted and they must be quoted if value contain delimiter. And if OP had some CSV generated by Postgres, you could expect that they took care of processing CSV, so OP doesn't have to worry about header line, delimiters, quotes and similar. My post was more in regards to his other thread inquiring about processing CSV, which didn’t come to a conclusion. Looks like he‘s trying other ways, and i just pointed out, that CSV is the easiest for „manual“ processing ---------------------------------------------------------------------- Root2 · 2022-09-13 22:15 thanks for the help, it no longer gives error but it is not executing the command, I thought that the command was wrong so I executed it separately and it worked without problems, but when I execute it from lazarus it does nothing. Thanks. ---------------------------------------------------------------------- gucao · 2022-09-14 02:20 please try it Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   with SQLQuery1 do  begin    //create recetasv1    Active:=False;    SQL.Clear;    SQL.Add('CREATE TABLE public.recetasv1 ');    SQL.Add('(                             ');    SQL.Add('    fecha character varying,  ');    SQL.Add('    hora character varying    ');    SQL.Add(');                            ');    SQL.Add('ALTER TABLE IF EXISTS public.recetasv1 ');    SQL.Add('    OWNER to "Administrator";');    ExecSQL;    //insert data to recetasv1    Active:=False;    SQL.Clear;    SQL.Add('Insert Into recetasv1(fecha,hora) values(''A1'',''A2''),(''B1'',''B2''),(''C1'',''C2'');');    ExecSQL;    //copy to file    Active:=False;    SQL.Clear;    SQL.Add('COPY recetasv1(fecha, hora) to ''D:\satosap.csv'' DELIMITER '','' CSV HEADER;');    ExecSQL;    //create recetasv2    Active:=False;    SQL.Clear;    SQL.Add('CREATE TABLE public.recetasv2 ');    SQL.Add('(                             ');    SQL.Add('    fecha character varying,  ');    SQL.Add('    hora character varying    ');    SQL.Add(');                            ');    SQL.Add('ALTER TABLE IF EXISTS public.recetasv2 ');    SQL.Add('    OWNER to "Administrator";');    ExecSQL;    //copy from file    Active:=False;    SQL.Clear;    SQL.Add('COPY recetasv2(fecha, hora) from ''D:\satosap.csv'' DELIMITER '','' CSV HEADER;');    ExecSQL;    //check recetasv2    Active:=False;    SQL.Clear;    SQL.Add('Select * from recetasv2');    Open;    ShowMessage(IntToStr(RecordCount)); //is 3    First;    ShowMessage(FieldByName('hora').AsString); //is A2  end;