Re: how to convert Access to PG unless odbc driver(with sql file)

Postgres is not mSQL. While I don't know what exportSQL2+pg.txt, it
apparently has no idea about proper SQL quoting. See lines:
> LOCALITA' varchar(50),
(you are not allowed to have quotes in field names without quoting entire
field "LOCALITA'" and even then I doubt it'd work.

> 'LUNEDI\'',
You must use 'LUNEDI''' (proper way to quote a single quote is with a
double single quote).

-alex
On Tue, 22 May 2001, Ludovico Romano wrote:

> I wont to convert an Access database to a PG on different computer and i
> don't have network connection. I try to use exportSQL2+pg.txt but don't work!
> the head of file generated from Access database is:
>
> -- Exported from MS Access to mSQL
> -- (C) 1997-98 CYNERGI - www.cynergi.net, info(at)cynergi(dot)net
>
>
> CREATE TABLE DATI
> (
> ESERCIZIO varchar(100),
> VIA_E_NUMERO varchar(61),
> LOCALITA' varchar(50),
> TELEFONO varchar(30),
> TIPOLOGIA varchar(50),
> ORARIO_DI_APERTURA varchar(100),
> RIPOSO varchar(51),
> A1 varchar(2),
> B1 varchar(2),
> B2 varchar(10),
> B3 varchar(3),
> B4 varchar(30),
> B5 varchar(5),
> B6 varchar(2),
> B7 varchar(4),
> B8 varchar(2),
> B9 varchar(15),
> B10 varchar(4),
> B11 varchar(2),
> B12 varchar(3),
> B13 varchar(2),
> B14 varchar(20),
> B15 varchar(3),
> C1 varchar(10),
> C2 varchar(2),
> C3 varchar(2),
> C6 varchar(10),
> C7 varchar(15),
> D2 varchar(7),
> D5 varchar(5),
> E2 varchar(11),
> E5 varchar(30),
> OSSERVAZIONI1 text,
> F4 varchar(2),
> F5 varchar(2),
> F6 varchar(4),
> G5 varchar(2),
> G6 varchar(2),
> G7 varchar(2),
> G8 varchar(2),
> G9 varchar(2),
> G10 varchar(2),
> G11 varchar(2),
> G12 varchar(2),
> G13 varchar(2),
> G14 varchar(2),
> H1 varchar(3),
> H2 varchar(3),
> H3 varchar(3),
> H4 varchar(3),
> H6 varchar(3),
> H7 varchar(11),
> OSSERVAZIONI2 text,
> I1 varchar(2),
> I2 varchar(2),
> I3 varchar(3),
> M1 varchar(20),
> M2 varchar(2),
> M3 varchar(2),
> M8 varchar(50),
> M9 varchar(50),
> M10 varchar(50),
> M11 varchar(50),
> OSSERVAZIONI3 text
> )\g
>
> INSERT INTO DATI
> VALUES (
> 'MUSEO STORICO D/RISORGIMENTO C/O CASTELLO BUONCONSIGLIO',
> 'VIA B. CLESIO 3',
> 'TRENTO CENTRO',
> '0461-230482',
> 'BIBLIOTECA',
> '09.00 - 12.00 14.30 - 16.30',
> 'LUNEDI\'',
> 'NO',
> 'SI',
> '18',
> '13',
> 'ESTERNI',
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> 'MANUALE',
> 'NO',
> 'SI',
> '206',
> 'INTERNO',
> NULL,
> '110',
> 'ORIZZONTALE',
> '160',
> 'LE PORTE IN ORARIO DI VISITA SONO SEMPRE APERTE.',
> 'NO',
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> 'SI',
> 'NO',
> '110',
> '91',
> 'NO',
> 'SI',
> 'SI',
> 'SI',
> '1.82',
> '1.86',
> 'I SERVIZI IGIENICI SONO ESTERNI ALLA SALA DEL MUSEO.' )\g
>
> INSERT INTO DATI
> VALUES (
> 'SALA DI PUBBLICA LETTURA',
> 'VIA SALE\' 1',
> 'POVO',
> '0461-810241',
> 'BIBLIOTECA',
> 'LU. GIO. 10.00 - 12.00 14.30 - 18.30 MA. ME. VE. 14.30 - 18.30',
> 'SABATO',
> 'SI',
> 'NO',
> .......................................................
>
> and the output-error of psql is:
> -- Exported from MS Access to mSQL
> -- (C) 1997-98 CYNERGI - www.cynergi.net, info(at)cynergi(dot)net
>
>
> CREATE TABLE DATI
> (
> ESERCIZIO varchar(100),
> VIA_E_NUMERO varchar(61),
> LOCALITA' varchar(50),
> TELEFONO varchar(30),
> TIPOLOGIA varchar(50),
> ORARIO_DI_APERTURA varchar(100),
> RIPOSO varchar(51),
> A1 varchar(2),
> B1 varchar(2),
> B2 varchar(10),
> B3 varchar(3),
> B4 varchar(30),
> B5 varchar(5),
> B6 varchar(2),
> B7 varchar(4),
> B8 varchar(2),
> B9 varchar(15),
> B10 varchar(4),
> B11 varchar(2),
> B12 varchar(3),
> B13 varchar(2),
> B14 varchar(20),
> B15 varchar(3),
> C1 varchar(10),
> C2 varchar(2),
> C3 varchar(2),
> C6 varchar(10),
> C7 varchar(15),
> D2 varchar(7),
> D5 varchar(5),
> E2 varchar(11),
> E5 varchar(30),
> OSSERVAZIONI1 text,
> F4 varchar(2),
> F5 varchar(2),
> F6 varchar(4),
> G5 varchar(2),
> G6 varchar(2),
> G7 varchar(2),
> G8 varchar(2),
> G9 varchar(2),
> G10 varchar(2),
> G11 varchar(2),
> G12 varchar(2),
> G13 varchar(2),
> G14 varchar(2),
> H1 varchar(3),
> H2 varchar(3),
> H3 varchar(3),
> H4 varchar(3),
> H6 varchar(3),
> H7 varchar(11),
> OSSERVAZIONI2 text,
> I1 varchar(2),
> I2 varchar(2),
> I3 varchar(3),
> M1 varchar(20),
> M2 varchar(2),
> M3 varchar(2),
> M8 varchar(50),
> M9 varchar(50),
> M10 varchar(50),
> M11 varchar(50),
> OSSERVAZIONI3 text
> )\g
>
> INSERT INTO DATI
> VALUES (
> 'MUSEO STORICO D/RISORGIMENTO C/O CASTELLO BUONCONSIGLIO',
> 'VIA B. CLESIO 3',
> 'TRENTO CENTRO',
> '0461-230482',
> 'BIBLIOTECA',
> '09.00 - 12.00 14.30 - 16.30',
> 'LUNEDI\'',
> 'NO',
> 'SI',
> '18',
> '13',
> 'ESTERNI',
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> 'MANUALE',
> 'NO',
> 'SI',
> '206',
> 'INTERNO',
> NULL,
> '110',
> 'ORIZZONTALE',
> '160',
> 'LE PORTE IN ORARIO DI VISITA SONO SEMPRE APERTE.',
> 'NO',
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> 'SI',
> 'NO',
> '110',
> '91',
> 'NO',
> 'SI',
> 'SI',
> 'SI',
> '1.82',
> '1.86',
> 'I SERVIZI IGIENICI SONO ESTERNI ALLA SALA DEL MUSEO.' )\g
>
> ERROR: parser: parse error at or near "'"
>
> INSERT INTO DATI
> VALUES (
> 'SALA DI PUBBLICA LETTURA',
> 'VIA SALE\' 1',
> 'POVO',
> '0461-810241',
> 'BIBLIOTECA',
> 'LU. GIO. 10.00 - 12.00 14.30 - 18.30 MA. ME. VE. 14.30 - 18.30',
> 'SABATO',
> 'SI',
> .........................................
> and in the standard optput there was nothing!
>
> Why?
>
> Thanks
>
> Ludo
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
>