On Sun, 2003-08-03 at 06:08, Jason Godden wrote: > Hi Ron, > > I have the same issue - best thing is to run the data through sed but pipe it > straight to the psql import table - that way you never have to worry about > creating misc. files. I also always use an intermediate temporary import > table rather than populate my actual db tables and usually copy from stdin > rather than a file. That way you can do your imports without having the data > in a directory readable by the pg process owner, ie: > > cat unprocesseddata.txt | sed "s/\",\"/$TAB/g" | sed "s/\"//g" | sed "s/.$//g" > | psql -dmydatabase -c "copy importtable from stdin delimiter '\t';"
Great... cat first10.unl | sed "s/\"//g" | \ psql test1 -c "copy t_lane_tx2 from stdin delimiter ',';" Then do some date-related transforms before inserting into the main table. [snip] > Note here that my file format never contains any " in a field so I can safely > run the second sed. If your import file contains these then you will need to > change the regex or use awk. [snip] > cat unprocesseddata.txt | psql -dmydatabase -c "copy importtable from stdin > delimiter '\",\"';" Wouldn't work, because the 1st field has it's leading \", and the last field has it's trailing \". Thanks [snip] > On Sun, 3 Aug 2003 05:36 pm, Ron Johnson wrote: > > Hi, > > > > PostgreSQL 7.3.3 on i386-pc-linux-gnu, compiled by GCC gcc > > (GCC) 3.3 (Debian) > > > > I must load a file in which each field in each row is contained > > in double quotes, i.e. > > "1","foo" > > "2","bar" > > "3","puddle" > > > > test1=# copy foo from '/usr/local/data/zip/foo.unl'; > > ERROR: copy: line 1, pg_atoi: error in ""1","foo"": can't parse > > ""1","foo"" > > > > I have a very large dataset (14.3GiB in 22 files) that has such > > a format, and would prefer not to sed each one of them, creating > > new files in the process. > > > > Any suggestions? > > > > Many TIA -- +-----------------------------------------------------------------+ | Ron Johnson, Jr. Home: [EMAIL PROTECTED] | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!" | | unknown | +-----------------------------------------------------------------+ ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html