Re: [GENERAL] importing a messy text file

2014-04-30 Thread Michael Paquier
On Thu, May 1, 2014 at 1:30 AM, bricklen wrote: > > On Wed, Apr 30, 2014 at 1:07 AM, Willy-Bas Loos wrote: >> >> >> Hi, >> >> I have a 56GB textfile that i want to import into postgres. >> The file is tab delimited and not quoted. > > > Would Pgloader be an option? http://tapoueh.org/pgloader/ Or

Re: [GENERAL] importing a messy text file

2014-04-30 Thread bricklen
On Wed, Apr 30, 2014 at 1:07 AM, Willy-Bas Loos wrote: > > Hi, > > I have a 56GB textfile that i want to import into postgres. > The file is tab delimited and not quoted. > Would Pgloader be an option? http://tapoueh.org/pgloader/

Re: [GENERAL] importing a messy text file

2014-04-30 Thread Rob Sargentg
On 04/30/2014 03:50 AM, Willy-Bas Loos wrote: On Wed, Apr 30, 2014 at 11:06 AM, Rémi Cura > wrote: with latest version 9.3 you can use a copy from with a programm as argument. You can use the unix | pipe for most of the stuff. If you have an older ver

Re: [GENERAL] importing a messy text file

2014-04-30 Thread Willy-Bas Loos
On Wed, Apr 30, 2014 at 11:06 AM, Rémi Cura wrote: > with latest version 9.3 you can use a copy from with a programm as > argument. > You can use the unix | pipe for most of the stuff. > > If you have an older version you can use the mkfifo command to "trick" > postgres into believing he will cop

Re: [GENERAL] importing a messy text file

2014-04-30 Thread Willy-Bas Loos
On Wed, Apr 30, 2014 at 11:03 AM, Alberto Cabello Sánchez wrote: > > > What about using "cut" to strip the extra fields? > Wow, i didn't know "cut", this looks promising. thanks. -- Willy-Bas Loos

Re: [GENERAL] importing a messy text file

2014-04-30 Thread Rémi Cura
Hey, with latest version 9.3 you can use a copy from with a programm as argument. I had a similar problem (an extra space at the end of each line), so I used sed in a pipeline to remove it before feeding it to database. You can use the unix | pipe for most of the stuff. If you have an older versi

Re: [GENERAL] importing a messy text file

2014-04-30 Thread Alberto Cabello Sánchez
On Wed, 30 Apr 2014 10:47:12 +0200 Willy-Bas Loos wrote: > Alberto, it would be hard to use sed( s/^I$// ), because there is no > explicit NULL value and there are many NULLs in the last column. > So i can't be sure how many tabs should be in the end of each line. Ok, I understand (I think). Wh

Re: [GENERAL] importing a messy text file

2014-04-30 Thread Willy-Bas Loos
Alberto, it would be hard to use sed( s/^I$// ), because there is no explicit NULL value and there are many NULLs in the last column. So i can't be sure how many tabs should be in the end of each line. Yes, Karsten, maybe scripting would be the easiest way to fix this, i would then probably insert

Re: [GENERAL] importing a messy text file

2014-04-30 Thread Alberto Cabello Sánchez
On Wed, 30 Apr 2014 10:07:09 +0200 Willy-Bas Loos wrote: > Hi, > > I have a 56GB textfile that i want to import into postgres. !!! > The problem is this: > There is a tab after the last column, in many but not all records. You could clean it up with something like s/^I$// , don't you? -- Al

Re: [GENERAL] importing a messy text file

2014-04-30 Thread Karsten Hilbert
On Wed, Apr 30, 2014 at 10:07:09AM +0200, Willy-Bas Loos wrote: > it would probably not be > feasible to manually add tabs for every record that misses one, although i > don't know how many it would be. Why not a) let a script do that, b) remove tabs from the end of lines as needed ? All in all

[GENERAL] importing a messy text file

2014-04-30 Thread Willy-Bas Loos
Hi, I have a 56GB textfile that i want to import into postgres. The file is tab delimited and not quoted. I deleted the header with the column names (using sed) so that i could use COPY with the non-csv text type (because some of the text values contain quotes). I had some minor trouble with the