Обсуждение: Postgresql Copy
Is there away to copy fixed length text files into an Postgresql table. After reading the doc's it looks Postgresql expects some sort of delimiter.
I think that is correct. If I'm wrong, I'm sure I'll be corrected. I import fixed width data using one of two methods: 1. I import the data into MS Access, which can deal with fixed width data; and then I move the data to PostgreSQL via ODBC connection. 2. There's a huge fixed width file that I receive every quarter. Since the structure is the same, I wrote a python script that copies the data to a new file, inserting tab delimiters and stripping out the extra spaces as it goes. The resulting file is often one third the size of the original. I then use copy to import the tab delimited data into PostgreSQL. If you'd like me to send the python script, let me know. Basically, you'll have to adjust the file to fit your data table's definition. (Or send me the definition of one table and I'll adjust it this once.) In any case, the original file is left unchanged should anything go wrong. Best of luck, Andrew Gould --- "samsom, debra" <dsamsom@bristol.ca> wrote: > Is there away to copy fixed length text files into > an Postgresql table. > After reading the doc's it looks Postgresql expects > some sort of delimiter. > __________________________________________________ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com
Yeah, that's what I do as well. With just a little bit of work you can make this sort of thing really easy to do. The trick is to create a script (I generally use Python as well, but Perl is good too) that reads from stdin and writes to stdout and that looks like what pg_dump spits out (which is very trivial). That way you can use it like this: cat my_file | custom_filter | psql -e my_database making it easy to automate. Jason --- Andrew Gould <andrewgould@yahoo.com> wrote: > I think that is correct. If I'm wrong, I'm sure > I'll > be corrected. > > I import fixed width data using one of two methods: > > 1. I import the data into MS Access, which can deal > with fixed width data; and then I move the data to > PostgreSQL via ODBC connection. > > 2. There's a huge fixed width file that I receive > every quarter. Since the structure is the same, I > wrote a python script that copies the data to a new > file, inserting tab delimiters and stripping out the > extra spaces as it goes. The resulting file is > often > one third the size of the original. I then use copy > to import the tab delimited data into PostgreSQL. > > If you'd like me to send the python script, let me > know. Basically, you'll have to adjust the file to > fit your data table's definition. (Or send me the > definition of one table and I'll adjust it this > once.) > In any case, the original file is left unchanged > should anything go wrong. > > Best of luck, > > Andrew Gould > > > > --- "samsom, debra" <dsamsom@bristol.ca> wrote: > > Is there away to copy fixed length text files into > > an Postgresql table. > > After reading the doc's it looks Postgresql > expects > > some sort of delimiter. > > > > > __________________________________________________ > Do You Yahoo!? > Make a great connection at Yahoo! Personals. > http://personals.yahoo.com > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the > unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) __________________________________________________ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com
On Wed, 31 Oct 2001, Andrew Gould wrote: > 2. There's a huge fixed width file that I receive > every quarter. Since the structure is the same, I > wrote a python script that copies the data to a new > file, inserting tab delimiters and stripping out the > extra spaces as it goes. The resulting file is often > one third the size of the original. I then use copy > to import the tab delimited data into PostgreSQL. I've done the same thing using perl. In fact I have actually just read, parsed, and then inserted the data directly into PostgreSQL. Cheers, Rod -- Let Accuracy Triumph Over Victory Zetetic Institute "David's Sling" Marc Stiegler