Hello dear friends.

I have this file:
"0050002999";"1999";"0001";"06/11/2016"
"0050002930";"1999";"0001";"07/11/2016"
"0050002931";"1999";"0001";"15/11/2016"
"0050002932";"1999";"0001";"22/11/2016"

How is the correct sentence in SQL for to copy data of this file to 
Postgresql witn ODBC?

If I do:

cSql := "COPY ALBARANES FROM 'D:\PRUEBA3.csv' WITH DELIMITER ';' QUOTE '"';"

or

cSql := "COPY ALBARANES FROM 'D:\PRUEBA3.csv' WITH DELIMITER ';' QUOTE '"'"


The compiler says newline character detected in string

Thansk in advance.
César.

César,

You have a double quote character inside your literal string that is 
delimited by in double quotes. Use this instead:

cSql := "COPY ALBARANES FROM 'D:\PRUEBA3.csv' WITH DELIMITER ';' QUOTE '" + chr(34) + "';"

or possibly this:

cSql := [COPY ALBARANES FROM 'D:\PRUEBA3.csv' WITH DELIMITER ';' QUOTE '"';]

Hope that helps,

Andreas

Andreas Gehrs-Pahl
Absolute Software, LLC

phone: (989) 723-9927
email: Andreas@AbsoluteSoftwareLLC.com
web:   http://www.AbsoluteSoftwareLLC.com
[F]:   https://www.facebook.com/AbsoluteSoftwareLLC

Thanks Andreas.

I don´t know why this error with your code for both possiblilities:

= SQL ERROR ===========
Date: 20161111 23:34:04
D:\CCC_PROGRAMACION\CCC_PLANTILLAS_XBASE\APP_SDI_DBFTID_ADT_PQ\SINFOGECO.EXE, 
Thread: 1
Windows 10 10.00 Build 14393, SQLXpp: 3.2.20, Runtime: 2.00.726
SQLState: 0A000,  ErrorCode:7
ERROR: el «quote» de COPY está disponible sólo en modo CSV;
Error while executing the query

Thread ID 1
Called from SQLSTATEMENT:EXECUTE(522)
Called from ALBARANES_1(159)
Called from PROCESO_2(110)
Called from (B)CREATESDIMENU(24)
Called from APPEXEC(239)
Called from MAIN(55)

SQLString: COPY ALBARANES FROM 'D:\PRUEBA3.csv' WITH DELIMITER ';' QUOTE 
'"';

= SQL ERROR ===========
Date: 20161111 23:34:36
D:\CCC_PROGRAMACION\CCC_PLANTILLAS_XBASE\APP_SDI_DBFTID_ADT_PQ\SINFOGECO.EXE, 
Thread: 1
Windows 10 10.00 Build 14393, SQLXpp: 3.2.20, Runtime: 2.00.726
SQLState: 0A000,  ErrorCode:7
ERROR: el «quote» de COPY está disponible sólo en modo CSV;
Error while executing the query

Thread ID 1
Called from SQLSTATEMENT:EXECUTE(522)
Called from ALBARANES_1(160)
Called from PROCESO_2(110)
Called from (B)CREATESDIMENU(24)
Called from APPEXEC(239)
Called from MAIN(55)

SQLString: COPY ALBARANES FROM 'D:\PRUEBA3.csv' WITH DELIMITER ';' QUOTE 
'"';


SOURCE CODE
---------------------

AppReadIni( "SINFOGECO.INI" )

cSIP := AppIni():GetEntry("DATA","SIP")

oConStr := dsConnectionString():new(";")
oConStr:AddEntry("Driver","PostgreSQL ANSI")
oConStr:AddEntry("Server", cSIP)
//oConStr:AddEntry("host", "localhost")
oConStr:AddEntry("Port", 5432)
oConStr:AddEntry("Database","dbftid")
oConStr:AddEntry("Uid", "postgres")
oConStr:AddEntry("Pwd", "200fivew")

cConStr := oConStr:GetConnectString()

oCon1 := SQLConnection():new()

IF !oCon1:driverConnect(nil, cConStr)
   MsgBox("Unable to connect to server!")
   RETURN
   ELSE

   Infobox("Conectado")

   cSql := "DROP TABLE ALBARANES;"

   oStmt := oCon1:NewStatement()
   oStmt:DisplayErrors := .f.
   oStmt:SQLString := cSql
   oStmt:Execute()


    cSql := "CREATE TABLE ALBARANES "
    cSql += "( "
    cSql += "DAL CHAR(10), "
    cSql += "EJE INTEGER, "
    //cSql += "POS CHAR(04), "
    cSql += "POS CHAR(04), "
    cSql += "FCO DATE "
    cSql += " )"


   cSql := "COPY ALBARANES FROM 'D:\PRUEBA3.csv' WITH DELIMITER ';' QUOTE '" 
+ chr(34) + "';"

    or

   cSql := [COPY ALBARANES FROM 'D:\PRUEBA3.csv' WITH DELIMITER ';' QUOTE 
'"';]

   oStmt := oCon1:NewStatement()
   oStmt:DisplayErrors := .t.
   oStmt:SQLString := cSql
   oStmt:Execute()

   oStmt:destroy()

   oCon1:destroy()

ENDIF


--------------------


"Andreas Gehrs-Pahl" escribió en el mensaje de 
noticias:1qabuxv0rsnt7.19suqvq1haj16$.dlg@40tude.net...

César,

You have a double quote character inside your literal string that is
delimited by in double quotes. Use this instead:

cSql := "COPY ALBARANES FROM 'D:\PRUEBA3.csv' WITH DELIMITER ';' QUOTE '" + 
chr(34) + "';"

or possibly this:

cSql := [COPY ALBARANES FROM 'D:\PRUEBA3.csv' WITH DELIMITER ';' QUOTE '"';]

Hope that helps,

Andreas

Andreas Gehrs-Pahl
Absolute Software, LLC

phone: (989) 723-9927
email: Andreas@AbsoluteSoftwareLLC.com
web:   http://www.AbsoluteSoftwareLLC.com
[F]:   https://www.facebook.com/AbsoluteSoftwareLLC

try this syntax

CREATE TABLE nodes(
  name numeric,
  hitem numeric,
  parent numeric,
  caption text,
  __record serial,
  CONSTRAINT nodes_pkey PRIMARY KEY (__record) ) ;
  COPY nodes FROM 'X:\ALASKA\PG\DATA.CSV' DELIMITER ',' ;
  UPDATE nodes SET __record = nextval('nodes___record_seq');

Thansk Jim.

This is the file for copy:
"0050002999";"1999";"0001";"06/11/2016"
"0050002930";"1999";"0001";"07/11/2016"
"0050002931";"1999";"0001";"15/11/2016"
"0050002932";"1999";"0001";"22/11/2016"

The problem is in the character " which indicates that it is a text

If I try without ", example:
0050002999;1999;0001;06/11/2016
0050002930;1999;0001;07/11/2016

, it works fine and copy the data to the table.

Regards.
César.

"Jim Lee" escribió en el mensaje de 
noticias:695feaf2$1a82849b$46efb@news.alaska-software.com...

try this syntax

CREATE TABLE nodes(
  name numeric,
  hitem numeric,
  parent numeric,
  caption text,
  __record serial,
  CONSTRAINT nodes_pkey PRIMARY KEY (__record) ) ;
  COPY nodes FROM 'X:\ALASKA\PG\DATA.CSV' DELIMITER ',' ;
  UPDATE nodes SET __record = nextval('nodes___record_seq');

In this moment I have got it.

cSql := [COPY ALBARANES FROM 'D:\PRUEBA3.csv' WITH DELIMITER ';' CSV QUOTE 
'"';]

Thanks Andreas and Jim.

César.

"César Calvo"  escribió en el mensaje de 
noticias:fc57607$11b7889f$4f814@news.alaska-software.com...

Thansk Jim.

This is the file for copy:
"0050002999";"1999";"0001";"06/11/2016"
"0050002930";"1999";"0001";"07/11/2016"
"0050002931";"1999";"0001";"15/11/2016"
"0050002932";"1999";"0001";"22/11/2016"

The problem is in the character " which indicates that it is a text

If I try without ", example:
0050002999;1999;0001;06/11/2016
0050002930;1999;0001;07/11/2016

, it works fine and copy the data to the table.

Regards.
César.

"Jim Lee" escribió en el mensaje de
noticias:695feaf2$1a82849b$46efb@news.alaska-software.com...

try this syntax

CREATE TABLE nodes(
  name numeric,
  hitem numeric,
  parent numeric,
  caption text,
  __record serial,
  CONSTRAINT nodes_pkey PRIMARY KEY (__record) ) ;
  COPY nodes FROM 'X:\ALASKA\PG\DATA.CSV' DELIMITER ',' ;
  UPDATE nodes SET __record = nextval('nodes___record_seq');

Why this error?:
ERROR: el valor es demasiado largo para el tipo character(10); Error while 
executing the query
ERROR: value too long for type character varying (10)

Is this caused for the format of the file attacment.

Thanks.
César.

"César Calvo"  escribió en el mensaje de 
noticias:6a5fd30$1ba251d4$5055e@news.alaska-software.com...

In this moment I have got it.

cSql := [COPY ALBARANES FROM 'D:\PRUEBA3.csv' WITH DELIMITER ';' CSV QUOTE
'"';]

Thanks Andreas and Jim.

César.

"César Calvo"  escribió en el mensaje de
noticias:fc57607$11b7889f$4f814@news.alaska-software.com...

Thansk Jim.

This is the file for copy:
"0050002999";"1999";"0001";"06/11/2016"
"0050002930";"1999";"0001";"07/11/2016"
"0050002931";"1999";"0001";"15/11/2016"
"0050002932";"1999";"0001";"22/11/2016"

The problem is in the character " which indicates that it is a text

If I try without ", example:
0050002999;1999;0001;06/11/2016
0050002930;1999;0001;07/11/2016

, it works fine and copy the data to the table.

Regards.
César.

"Jim Lee" escribió en el mensaje de
noticias:695feaf2$1a82849b$46efb@news.alaska-software.com...

try this syntax

CREATE TABLE nodes(
  name numeric,
  hitem numeric,
  parent numeric,
  caption text,
  __record serial,
  CONSTRAINT nodes_pkey PRIMARY KEY (__record) ) ;
  COPY nodes FROM 'X:\ALASKA\PG\DATA.CSV' DELIMITER ',' ;
  UPDATE nodes SET __record = nextval('nodes___record_seq');

> Why this error?:
> ERROR: el valor es demasiado largo para el tipo character(10); Error while
> executing the query
> ERROR: value too long for type character varying (10)

what is you Table Structure ?
look into x:\Program Files\PostgreSQL\9.xxx\data\pg_log\*.LOG

Hi Jim.
There is a first strange character in the csv file that has two characters 
(this was created with the export option of ARC).
If I change

cSql := "CREATE TABLE ALBARANES "
cSql += "( "
cSql += "DAL CHAR(10), "
.
.
.
.
cSql += " )"

By

cSql := "CREATE TABLE ALBARANES "
cSql += "( "
cSql += "DAL CHAR(13), "
.
.
.
.
cSql += " )"

Then it starts to import but now the problem is when a field of date type 
has no value, I mean Instead of "10/11/2016" with ""

Now I have to see how do for resolve the import for Null values or change 
the structure.

Regards.
César.

"Jim Lee" escribió en el mensaje de 
noticias:499ab45c$4a78efb9$868fa@news.alaska-software.com...

> Why this error?:
> ERROR: el valor es demasiado largo para el tipo character(10); Error while
> executing the query
> ERROR: value too long for type character varying (10)

what is you Table Structure ?
look into x:\Program Files\PostgreSQL\9.xxx\data\pg_log\*.LOG

Jim, If I change fields date type to char type then there is other question:

2016-11-13 08:14:15 CET ERROR:  carácter con secuencia de bytes 0x90 en 
codificación «WIN1252» no tiene equivalente en la codificación «UTF8»
2016-11-13 08:14:15 CET CONTEXTO:  COPY albaranes, línea 14
2016-11-13 08:14:15 CET SENTENCIA:  COPY ALBARANES FROM 'D:\PRUEBA4.csv' 
WITH DELIMITER ';' CSV QUOTE '"';

Thanks.
César.

"César Calvo"  escribió en el mensaje de 
noticias:3ce364fe$57945de7$8e528@news.alaska-software.com...

Hi Jim.
There is a first strange character in the csv file that has two characters
(this was created with the export option of ARC).
If I change

cSql := "CREATE TABLE ALBARANES "
cSql += "( "
cSql += "DAL CHAR(10), "
.
.
.
.
cSql += " )"

By

cSql := "CREATE TABLE ALBARANES "
cSql += "( "
cSql += "DAL CHAR(13), "
.
.
.
.
cSql += " )"

Then it starts to import but now the problem is when a field of date type
has no value, I mean Instead of "10/11/2016" with ""

Now I have to see how do for resolve the import for Null values or change
the structure.

Regards.
César.

"Jim Lee" escribió en el mensaje de
noticias:499ab45c$4a78efb9$868fa@news.alaska-software.com...

> Why this error?:
> ERROR: el valor es demasiado largo para el tipo character(10); Error while
> executing the query
> ERROR: value too long for type character varying (10)

what is you Table Structure ?
look into x:\Program Files\PostgreSQL\9.xxx\data\pg_log\*.LOG

> Then it starts to import but now the problem is when a field of date type
> has no value, I mean Instead of "10/11/2016" with ""

use DTOS( DATE() ) Syntax YYMMDDHHMMSS