Received: from web13404.mail.yahoo.com (web13404.mail.yahoo.com [216.136.175.62]) by postgresql.org (8.11.3/8.11.4) with SMTP id fA6IZBm86436 for ; Tue, 6 Nov 2001 13:35:12 -0500 (EST) (envelope-from andrewgould@yahoo.com) Message-ID: <20011106183506.37951.qmail@web13404.mail.yahoo.com> Received: from [4.18.79.155] by web13404.mail.yahoo.com via HTTP; Tue, 06 Nov 2001 10:35:06 PST Date: Tue, 6 Nov 2001 10:35:06 -0800 (PST) From: Andrew Gould Subject: Re: copy command with PSQL To: Jeff Eckermann , shaunnx@my-deja.com, pgsql-general@postgresql.org In-Reply-To: <20011106163842.90342.qmail@web20803.mail.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200111/241 X-Sequence-Number: 17239 I'm a little late stepping into this thread. (sorry) Does the space delimited file enclose text in quotes or double quotes? Most database applications that export data in a space delimited format will export text enclosed in quotes or double quotes (or will at least have the option). I've written a python script that will copy a space delimited file with double quotes around text fields to to a tab delimited file without the double quotes. It tracks the occurrences of double quotes to know whether to convert a space to a tab or treat it as text. Postgres copies the new file format easily. If my script will help, let me know and I'll attach it to an email. Best of luck, Andrew Gould --- Jeff Eckermann wrote: > You need some kind of field separator in your data, > a > single character that is not found elsewhere in your > data. > That means you will need to find a way to get your > source application to export the data with separator > characters in place; or else you will need to edit > the > output to add those separators. How you do the > editing depends on the nature of the data and the > format of the output. If you send me a sample I may > be able to suggest something for you (if the first > alternative is unworkable for you). > I have written some notes on the use of "copy", > which > you can find at the techdocs site: > http://techdocs.postgresql.org > > --- X wrote: > > Sorry if this is a repost -- > > > > Howdy: > > > > Regarding the 'copy' command, are there any > examples > > as to it's usage? Let me give you some background > > on > > what I'm doing. > > > > From my DB2/Mainframe to NT/DB2Connect > environment, > > I have > > an SQL script that pulls data down. I can append > > the > > data into a flat ASCII file format, but because > some > > of the data > > in some columns are broken up, it doesn't seem > > possible to > > import the data into Postgres. (e.g., the address > > col. may > > have "66 w. baker street" ... but the format of > the > > file is > > space delimited, so each group of words are looked > > at as a > > column). > > > > DB2 will, however, let me create a file that is a > > binary > > format. (e.g., EXPORT to F:\backup\test.ixf of > IXF) > > After looking at some of the Postgres > documentation, > > there is a copy command that says, 'copies data > > between files > > and tables'. > > > > Now, I'm trying to figure out how can I copy this > > binary file > > and load it into a table on Postgres? Is it > > possible? > > > > Thanks! > > > > -X > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please > > send an appropriate > > subscribe-nomail command to > majordomo@postgresql.org > > so that your > > message can get through to the mailing list > cleanly > > > __________________________________________________ > Do You Yahoo!? > Find a job, post your resume. > http://careers.yahoo.com > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org __________________________________________________ Do You Yahoo!? Find a job, post your resume. http://careers.yahoo.com