Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5CAB79FB416 for ; Tue, 20 Mar 2007 00:05:42 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 94309-01 for ; Tue, 20 Mar 2007 00:05:38 -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 70BD59FB2B3 for ; Tue, 20 Mar 2007 00:05:36 -0300 (ADT) Received: (qmail 22329 invoked from network); 20 Mar 2007 03:05:34 -0000 X-NetworkBox-HamSign: 0101;OUT;wbeetaau1;25425ee9ac1e429ef42dc4b04d8f15a0; Received: from mail.weatherbeeta.com.au (10.10.0.2) by network-box.weatherbeeta.com.au with SMTP; 20 Mar 2007 03:05:34 -0000 Received: (qmail 25706 invoked by uid 1166); 20 Mar 2007 14:05:33 +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.459562 secs); 20 Mar 2007 03:05:33 -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.459562 secs Process 25691) 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 14:05:31 +1100 Subject: Re: Issue with copying data from a text file. From: Phillip Smith To: pgsql-sql@postgresql.org In-Reply-To: <45FF4DFA.1020202@autoledgers.com.au> References: <45FF4622.2010404@autoledgers.com.au> <1174358236.10466.14.camel@it-laptop> <45FF4AA3.4060808@autoledgers.com.au> <45FF4DFA.1020202@autoledgers.com.au> Content-Type: multipart/signed; micalg=sha1; protocol="application/x-pkcs7-signature"; boundary="=-mxr8mqqMh734r730T88x" Organization: Weatherbeeta P/L Date: Tue, 20 Mar 2007 14:05:24 +1100 Message-Id: <1174359924.10466.32.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-1174359934.143-22322-000 X-Scanned-By-wbeetaau1: No known viruses found in message (received+scanned in 0.06/0.15 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/261 X-Sequence-Number: 27899 --=-mxr8mqqMh734r730T88x Content-Type: multipart/alternative; boundary="=-ku+FWHofVAZBNg05fR5v" --=-ku+FWHofVAZBNg05fR5v Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Maybe use char 254 or 253 or something similar... Anything that isn't going to be found in the file. Have you tried using a string as a delimiter? QUOTE 'THIS.STRING.ISNT.IN.THE.TEXT.FILE' ~p On Tue, 2007-03-20 at 11:59 +0900, Paul Lambert wrote: > Andrej Ricnik-Bay wrote: > > On 3/20/07, Paul Lambert wrote: > >=20 > >> The source file comes from extracts on our main application which sits > >> inside an in-house pretending-to-be-a-dbms file system. The content of > >> these extracts would be difficult to change - the extract program woul= d > >> need to parse the data looking for quotes and preceed them with the > >> necessary escape character. > >> > >> Not being a proper database dump it's not a simple matter of flicking = a > >> switch to get it to include the escape character. The way the extracts > >> are written would require a few dozen lines of code to each extract, a= nd > >> theres about 40ish extracts. > >> > >> Plus I don't maintain that side of our code, and those that do can be = a > >> bit lazy and I'd likely be waiting months to get it done - if they eve= n > >> decide to do it. > > Pipe it through sed and replace the Carets with TABS? > > sed 's/^/\t/g' c:/temp/autodrs_deal_lines.txt >=20 > > c:/temp/autodrs_deal_lines.tab > >=20 > > Then use copy like so: > > \copy table from 'c:/temp/autodrs_deal_lines.tab' delimiter E'\t' null = '' > >=20 > >=20 > > Cheers, > > Andrej > >=20 > >=20 >=20 > The data contains tabs... don't ask why... I don't have a clue :P >=20 > I'll do something along the lines of sed... but not with sed, I'll use=20 > the command line interpreter on the OpenVMS systems where the extracts=20 > run. I just thought there might have been a quicker way to switch it off=20 > in the copy command, i.e. specifying "quote none" as one of the=20 > parameters to the command. I guess not... >=20 > Thanks for the pointers. >=20 > P. >=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 --=-ku+FWHofVAZBNg05fR5v Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Maybe use char 254 or 253 or something similar... Anything that isn't going= to be found in the file.

Have you tried using a string as a delimiter? QUOTE 'THIS.STRING.ISNT.IN.TH= E.TEXT.FILE'

~p

On Tue, 2007-03-20 at 11:59 +0900, Paul Lambert wrote: 
Andrej Ricnik-Bay wrote:
> On 3/20/07, Paul Lambert <paul.lambert@autoledgers.com.au> w=
rote:
> 
>> The source file comes from extracts on our=
 main application which sits
>> inside an in-house pretending-to-be-a-dbms=
 file system. The content of
>> these extracts would be difficult to chang=
e - the extract program would
>> need to parse the data looking for quotes =
and preceed them with the
>> necessary escape character.
>>
>> Not being a proper database dump it's not =
a simple matter of flicking a
>> switch to get it to include the escape cha=
racter. The way the extracts
>> are written would require a few dozen line=
s of code to each extract, and
>> theres about 40ish extracts.
>>
>> Plus I don't maintain that side of our cod=
e, and those that do can be a
>> bit lazy and I'd likely be waiting months =
to get it done - if they even
>> decide to do it.
> Pipe it through sed and replace the Carets wit=
h TABS?
> sed 's/^/\t/g' c:/temp/autodrs_deal_lines.txt =
> 
> c:/temp/autodrs_deal_lines.tab
> 
> Then use copy like so:
> \copy table from 'c:/temp/autodrs_deal_lines.t=
ab' delimiter E'\t' null ''
> 
> 
> Cheers,
> Andrej
> 
> 

The data contains tabs... don't ask why... I don't =
have a clue :P

I'll do something along the lines of sed... but not=
 with sed, I'll use 
the command line interpreter on the OpenVMS systems=
 where the extracts 
run. I just thought there might have been a quicker=
 way to switch it off 
in the copy command, i.e. specifying "quote no=
ne" as one of the 
parameters to the command. I guess not...

Thanks for the pointers.

P.
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
--=-ku+FWHofVAZBNg05fR5v-- --=-mxr8mqqMh734r730T88x 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 ATAcBgkqhkiG9w0BCQUxDxcNMDcwMzIwMDMwNTI0WjAjBgkqhkiG9w0BCQQxFgQUlw0Fl+0OB4r6 cK6P6SjgiD/jtV4wDQYJKoZIhvcNAQEBBQAEggEANzYCWs6/7dr73PEqi2iUDTVF+2XJKm9wt2DE HkPltOZqu4GLqKBXQpKyZw6VjzNG72ZnFqEDDsyBywYPQsakmcg43ApzvpLkpjXO9ZzVgcUyxYNf DdemFf95UeQQ6WjyunmeVlN7Bv/UlLuvF8V7ALzclCXdR1eSZk4do8iO/24qVFo49jFO5ZMLrFsI oCH3Wp9U9aL3232TQ150g1X01W7fI7ZblNR0mIhlao9DBJk+ilIuYW+LcOypLEqVccN3vI299KdI mUyC10aj1fS+vZ9NYDbrEDjRq5YUpsqzDwE8Fv4K/H3AEPfpmK/Wg6kttLY5bWTTcEvnSD0XAAk2 UgAAAAAAAA== --=-mxr8mqqMh734r730T88x--