Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id E949F9FB2FE for ; Mon, 19 Mar 2007 23:37:33 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 29053-07 for ; Mon, 19 Mar 2007 23:37:30 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from weatherbeeta.com.au (218-214-212-153.people.net.au [218.214.212.153]) by postgresql.org (Postfix) with SMTP id 0C4019FB2EE for ; Mon, 19 Mar 2007 23:37:28 -0300 (ADT) Received: (qmail 11966 invoked from network); 20 Mar 2007 02:37:26 -0000 X-NetworkBox-HamSign: 0101;OUT;wbeetaau1;1c16ab3862a3fcb6fab566c9429719af; Received: from mail.weatherbeeta.com.au (10.10.0.2) by network-box.weatherbeeta.com.au with SMTP; 20 Mar 2007 02:37:26 -0000 Received: (qmail 20129 invoked by uid 1166); 20 Mar 2007 13:37:25 +1100 Received: from 172.20.1.142 by mail.weatherbeeta.com.au (envelope-from , uid 1165) with qmail-scanner-1.25-st-qms (clamdscan: 0.88.6/2315. perlscan: 1.25-st-qms. Clear:RC:1(172.20.1.142):. Processed in 0.412657 secs); 20 Mar 2007 02:37:25 -0000 X-Antivirus-mail.weatherbeeta.com.au-Mail-From: phillip.smith@weatherbeeta.com.au via mail.weatherbeeta.com.au X-Antivirus-mail.weatherbeeta.com.au: 1.25-st-qms (Clear:RC:1(172.20.1.142):. Processed in 0.412657 secs Process 20121) Received: from unknown (HELO ?172.20.1.142?) (phillip.smith@weatherbeeta.com.au@172.20.1.142) by mail.weatherbeeta.com.au with SMTP; 20 Mar 2007 13:37:23 +1100 Subject: Re: Issue with copying data from a text file. From: Phillip Smith To: Paul Lambert Cc: pgsql-sql@postgresql.org In-Reply-To: <45FF4622.2010404@autoledgers.com.au> References: <45FF4622.2010404@autoledgers.com.au> Content-Type: multipart/signed; micalg=sha1; protocol="application/x-pkcs7-signature"; boundary="=-8AtepTXUo8dQRNk/O407" Organization: Weatherbeeta P/L Date: Tue, 20 Mar 2007 13:37:16 +1100 Message-Id: <1174358236.10466.14.camel@it-laptop> Mime-Version: 1.0 X-Mailer: Evolution 2.8.1 X-Scanned-By-wbeetaau1: Virus scan performed by network-box X-Scanned-By-wbeetaau1: Scanner file id is wbeetaau1-1174358246.32-11926-000 X-Scanned-By-wbeetaau1: No known viruses found in message (received+scanned in 0.04/0.59 secs) X-Scanned-By-wbeetaau1: Spam-Check-Result: No, hits=0 required=7 tests= autolearn=no version=2.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/257 X-Sequence-Number: 27895 --=-8AtepTXUo8dQRNk/O407 Content-Type: multipart/alternative; boundary="=-A2y0HHYFgjh/qqXic9/g" --=-A2y0HHYFgjh/qqXic9/g Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Can you not export the source file with escape characters? ie,=20 ^17\" Alloy Wheels^ ~p On Tue, 2007-03-20 at 11:25 +0900, Paul Lambert wrote: > I have a procedure in place that copies data from a caret delimited text=20 > file into a table storing some information. >=20 > One of the fields in the table contains an item description which may=20 > contain item dimensions such as - 17" alloy wheels >=20 > The problem I am getting when I do my load is I believe due to the=20 > presence of the double quotation marks giving the copy the impression=20 > that it is to include the information following as a single text string=20 > until it gets to the next set of double quotes. As a result, I get the=20 > following: >=20 > AutoDRS=3D# COPY deal_lines_temp_load FROM=20 > 'c:/temp/autodrs_deal_lines.txt' > WITH DELIMITER AS '^' CSV HEADER; > ERROR: value too long for type character varying(30) > CONTEXT: COPY deal_lines_temp_load, line 87, column order_desc: "17 5=20 > spoke alloy wheels.^1291.18^117.38^983.69^1291.18^^C^^ >=20 > The column as you can see is defined as a 30 character field, the load=20 > contains in this column ^17" 5 spoke alloy wheels.^ >=20 > I note an option in the COPY command to specify the quote character,=20 > defaulting to double quote. The problem being a single quote will also=20 > be used in the data, as will other characters. Is there any way to get a=20 > copy to have no quote character? I.e. read the file and put whatever is=20 > between the caret characters straight into the appropriate field exactly=20 > as is. >=20 > TIA, > Paul. >=20 Phillip Smith IT Coordinator Weatherbeeta P/L 8 Moncrief Rd Nunawading, Vic, 3131 AUSTRALIA P. +613 9845 0600 F. +613 9845 0655 E. phillip.smith@weatherbeeta.com.au --=-A2y0HHYFgjh/qqXic9/g Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Can you not export the source file with escape characters? ie,

^17\" Alloy Wheels^

~p

On Tue, 2007-03-20 at 11:25 +0900, Paul Lambert wrote: 
I have a procedure in place that copies data from a=
 caret delimited text 
file into a table storing some information.

One of the fields in the table contains an item des=
cription which may 
contain item dimensions such as - 17" alloy wh=
eels

The problem I am getting when I do my load is I bel=
ieve due to the 
presence of the double quotation marks giving the c=
opy the impression 
that it is to include the information following as =
a single text string 
until it gets to the next set of double quotes. As =
a result, I get the 
following:

AutoDRS=3D#       COPY deal_lines_temp_load FROM 
'c:/temp/autodrs_deal_lines.txt'
WITH DELIMITER AS '^' CSV HEADER;
ERROR:  value too long for type character varying(3=
0)
CONTEXT:  COPY deal_lines_temp_load, line 87, colum=
n order_desc: "17 5 
spoke alloy wheels.^1291.18^117.38^983.69^1291.18^^=
C^^

The column as you can see is defined as a 30 charac=
ter field, the load 
contains in this column ^17" 5 spoke alloy whe=
els.^

I note an option in the COPY command to specify the=
 quote character, 
defaulting to double quote. The problem being a sin=
gle quote will also 
be used in the data, as will other characters. Is t=
here any way to get a 
copy to have no quote character? I.e. read the file=
 and put whatever is 
between the caret characters straight into the appr=
opriate field exactly 
as is.

TIA,
Paul.
Phillip Smith
IT Coordinator
Weatherbeeta P/L
8 Moncrief Rd
Nunawading, Vic, 3131
AUSTRALIA

P. +613 9845 0600
F. +613 9845 0655
E. phillip.smith@weath= erbeeta.com.au
--=-A2y0HHYFgjh/qqXic9/g-- --=-8AtepTXUo8dQRNk/O407 Content-Type: application/x-pkcs7-signature; name=smime.p7s Content-Disposition: attachment; filename=smime.p7s Content-Transfer-Encoding: base64 MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGQzCCAvww ggJloAMCAQICECgGLzg4fvuywaSUPjOvpbowDQYJKoZIhvcNAQEFBQAwYjELMAkGA1UEBhMCWkEx JTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQ ZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA3MDIwNzA1MTc0M1oXDTA4MDIwNzA1MTc0 M1owUzEfMB0GA1UEAxMWVGhhd3RlIEZyZWVtYWlsIE1lbWJlcjEwMC4GCSqGSIb3DQEJARYhcGhp bGxpcC5zbWl0aEB3ZWF0aGVyYmVldGEuY29tLmF1MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIB CgKCAQEA0g7kg7BnVTuTt66o+M4w+YJ2sAt6abjbBLngvHoEx79/wrvCTMK0SlVdpR1P+qxM+Qpd Hnzi2iMvBbHJjJIgFndQtpF31RIlj1+/MSB7B4yzRwDIgiljPwVYUfJ8s2/M2++/wcCOi74OpSNF Um0HwZ8bPUohFpHeo3rjGjH4Ma3jOs2TH7jVI808T8bU24JleARbMpm8rCTcK2j0PTxYGCjvN15J FAOTr93yOJS0nMZbC7XCABPCxxpfWUi4x39Gx7dOKc/SskT1P+HHpDS41hSJ6C1fgnHgrjiUWF61 wkG+5pPRTsesxYDoIDsCOthz1VY2u7ncfg6itOLD41HrTQIDAQABoz4wPDAsBgNVHREEJTAjgSFw aGlsbGlwLnNtaXRoQHdlYXRoZXJiZWV0YS5jb20uYXUwDAYDVR0TAQH/BAIwADANBgkqhkiG9w0B AQUFAAOBgQACDFff/JMrNXly4WjbURYqld66NXpVnF6EMR/32JlA5eh/hiA49EKKbrkUgFETxfPD QasscsNEkBjcXoibq+Fk6JZNG3mA5Rct2aks3gP9fupVtcBJETXpLqlJ3CZsiX+fnFw/HgZtKRNV vam0kvXdHBY+WfYMQTTugskfCHVaqjCCAz8wggKooAMCAQICAQ0wDQYJKoZIhvcNAQEFBQAwgdEx CzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEa MBgGA1UEChMRVGhhd3RlIENvbnN1bHRpbmcxKDAmBgNVBAsTH0NlcnRpZmljYXRpb24gU2Vydmlj ZXMgRGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBDQTErMCkGCSqG SIb3DQEJARYccGVyc29uYWwtZnJlZW1haWxAdGhhd3RlLmNvbTAeFw0wMzA3MTcwMDAwMDBaFw0x MzA3MTYyMzU5NTlaMGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAo UHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBDQTCB nzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAxKY8VXNV+065yplaHmjAdQRwnd/p/6Me7L3N9Vvy Gna9fww6YfK/Uc4B1OVQCjDXAmNaLIkVcI7dyfArhVqqP3FWy688Cwfn8R+RNiQqE88r1fOCdz0D viv+uxg+B79AgAJk16emu59l0cUqVIUPSAR/p7bRPGEEQB5kGXJgt/sCAwEAAaOBlDCBkTASBgNV HRMBAf8ECDAGAQH/AgEAMEMGA1UdHwQ8MDowOKA2oDSGMmh0dHA6Ly9jcmwudGhhd3RlLmNvbS9U aGF3dGVQZXJzb25hbEZyZWVtYWlsQ0EuY3JsMAsGA1UdDwQEAwIBBjApBgNVHREEIjAgpB4wHDEa MBgGA1UEAxMRUHJpdmF0ZUxhYmVsMi0xMzgwDQYJKoZIhvcNAQEFBQADgYEASIzRUIPqCy7MDaNm rGcPf6+svsIXoUOWlJ1/TCG4+DYfqi2fNi/A9BxQIJNwPP2t4WFiw9k6GX6EsZkbAMUaC4J0niVQ lGLH2ydxVyWN3amcOY6MIE9lX5Xa9/eH1sYITq726jTlEBpbNU1341YheILcIRk13iSx0x1G/11f ZU8xggH8MIIB+AIBATB2MGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGlu ZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBD QQIQKAYvODh++7LBpJQ+M6+lujAJBgUrDgMCGgUAoF0wGAYJKoZIhvcNAQkDMQsGCSqGSIb3DQEH ATAcBgkqhkiG9w0BCQUxDxcNMDcwMzIwMDIzNzE2WjAjBgkqhkiG9w0BCQQxFgQUpvoE3B3DjObS eqjqoR9Ao+KZq9QwDQYJKoZIhvcNAQEBBQAEggEAH0w79qhoxGdg7r3XZed5G6/ySF3sexj9IQj7 q8mRy1CwGJB0i0ZH5ZumCWU3ui3xH6Yu15JORX0rkrADiLmRd9+ODD62fRaiAtKp+n2IStNInZ/F X8ahEnMr3fPaGUphZvtyWaVHl91v37ax+6fCgEL0vxxZ7Xh2RgO6a9+kblDu40fJwfox1AKBIBoh dItcOYXRk03Dwv2tL7VSowHa89NgVVLgv1XsFQqsfrUJOe+zO+nlLfqgU30zlSmBaVyhomew3LmH PjezsFiXLxZYP6Yq8mjW/Uj+ZVnJvsanZkwAPUDSiewthIYakf9khe/lxBdLoEq8Wl4SoF1YG8jE vQAAAAAAAA== --=-8AtepTXUo8dQRNk/O407--