Thread: A very novice question about ascii files
Hi all, This is probably a very stupid question but I'm working on a really tight deadline and I'm very confused. I've got several incredibly hefty ascii files that were at one point Oracle databases. Now they're just plain ascii dump files and today I got asked to put them in PGSQL but I have no idea how (I've never even worked with databases before except for the offline variety.) Can anybody please point me towards some documentation that would be helpful or give me some advice? Thanks so much, Leslie
On Fri, 8 Sep 2000, Leslie Johnson - Contractor wrote: > Hi all, > This is probably a very stupid question but I'm working on a really tight > deadline and I'm very confused. It's isn't quite verbose enough for me to give but a start on an answer. Someone else may have more knowledge. > I've got several incredibly hefty ascii > files that were at one point Oracle databases. Now they're just plain > ascii dump files and today I got asked to put them in PGSQL but I have no > idea how (I've never even worked with databases before except for the > offline variety.) Can anybody please point me towards some documentation > that would be helpful or give me some advice? I guess you've looked a little at these files. Do they look like fixed format or delimited files? I.e., is the first field always columns 1-N, the second, N+1 to M, ... Good luck, Gord Matter Realisations http://www.materialisations.com/ Gordon Haverland, B.Sc. M.Eng. President 101 9504 182 St. NW Edmonton, AB, CA T5T 3A7 780/481-8019 ghaverla @ freenet.edmonton.ab.ca
I guess you've looked a little at these files. Do they look like fixed format or delimited files? I.e., is the first field always columns 1-N, the second, N+1 to M, ... Actually, I'm not sure because I'm having to download them right now and look at them in notepad and I'm sure that's messing it up (for some reason VI doesn't seem to be working). They're in columns with column headings...granted they're wrapped around a little strangely. Maybe somebody out there knows how to change it either way? Thanks again, Leslie
On Fri, 8 Sep 2000, Leslie Johnson - Contractor wrote: > Actually, I'm not sure because I'm having to download them right now and > look at them in notepad and I'm sure that's messing it up (for some reason > VI doesn't seem to be working). If a line has more than 1024 columns, notepad may not display them properly either. (I'm guessing a limit like that might be happening here.) > They're in columns with column > headings...granted they're wrapped around a little strangely. Maybe > somebody out there knows how to change it either way? A local LUG member was having Progress problems, his files were non-trivially delimited. It sounds like yours are fixed. If you do something like head -1 | wc head -2 | tail -1 | wc head -3 | tail -1 | wc And the wc's all say that lines are a common length, that would be a real quick indicator of fixed format output. Hopefully this gets thigns going for you. I'm off for the weekend, so hopefully others can pick up if you need more help (as little as I've helped). Gord Matter Realisations http://www.materialisations.com/ Gordon Haverland, B.Sc. M.Eng. President 101 9504 182 St. NW Edmonton, AB, CA T5T 3A7 780/481-8019 ghaverla @ freenet.edmonton.ab.ca
On Fri, 8 Sep 2000, Leslie Johnson - Contractor wrote:
> Hi all,
> This is probably a very stupid question but I'm working on a really tight
> deadline and I'm very confused. I've got several incredibly hefty ascii
> files that were at one point Oracle databases. Now they're just plain
> ascii dump files and today I got asked to put them in PGSQL but I have no
> idea how (I've never even worked with databases before except for the
> offline variety.) Can anybody please point me towards some documentation
> that would be helpful or give me some advice?
> Thanks so much,
> Leslie
Leslie,
I had to load quite a few flat files dumped from a PROGRESS database.
They contained lots of errors (PROGRESS is so sloppy it allowed the
user to enter control characters into varchar() fields!)
Anyhoo, it was no picnic but I have some shell scripts that ask sed
to fix a few things so they are more to PG's liking. If you like
send a sample of the data to the list and I'll have a go...
It doesn't matter if you send fake data (working in psychiatry I'm
familiar with confidentiality issues) as long as it's representative
of the problem in front of you. If I can help I'll send you back
a shell script...
With luck your data is tab delimited. ;-)
Mine was but had embedded tabs! He he...sed prevailed nonetheless.
Cheers,
Tom
--------------------------------------------------------------------
SVCMC - Center for Behavioral Health
--------------------------------------------------------------------
Thomas Good tomg@ { admin | q8 } .nrnet.org
IS Coordinator / DBA Phone: 718-354-5528
Fax: 718-354-5056
--------------------------------------------------------------------
Powered by: PostgreSQL s l a c k w a r e FreeBSD:
RDBMS |---------- linux The Power To Serve
--------------------------------------------------------------------
Sounds like a tab-delimited file. You should be able to import that information. -----Original Message----- From: Leslie Johnson - Contractor [mailto:ljohnson@arl.army.mil] Sent: Friday, September 08, 2000 1:30 PM To: pgsql-novice@postgresql.org Subject: Re: [NOVICE] A very novice question about ascii files I guess you've looked a little at these files. Do they look like fixed format or delimited files? I.e., is the first field always columns 1-N, the second, N+1 to M, ... Actually, I'm not sure because I'm having to download them right now and look at them in notepad and I'm sure that's messing it up (for some reason VI doesn't seem to be working). They're in columns with column headings...granted they're wrapped around a little strangely. Maybe somebody out there knows how to change it either way? Thanks again, Leslie
On Fri, 8 Sep 2000, Leslie Johnson - Contractor wrote:
>
> I guess you've looked a little at these files. Do they
> look like fixed format or delimited files? I.e., is the first
> field always columns 1-N, the second, N+1 to M, ...
>
> Actually, I'm not sure because I'm having to download them right now and
> look at them in notepad and I'm sure that's messing it up (for some reason
> VI doesn't seem to be working). They're in columns with column
> headings...granted they're wrapped around a little strangely. Maybe
> somebody out there knows how to change it either way?
vi usually has a file size limit...if you have mc (midnight commander)
you can peek at them that way (F3)...
--------------------------------------------------------------------
SVCMC - Center for Behavioral Health
--------------------------------------------------------------------
Thomas Good tomg@ { admin | q8 } .nrnet.org
IS Coordinator / DBA Phone: 718-354-5528
Fax: 718-354-5056
--------------------------------------------------------------------
Powered by: PostgreSQL s l a c k w a r e FreeBSD:
RDBMS |---------- linux The Power To Serve
--------------------------------------------------------------------
If you have access to an Oracle box, you can try reloading using SQL Loader, then any number of tools can be used to move the data from one environment to another. I once moved Oracle 8i database to Postgres 7.0.2 via the MS-SQL 7 Enterprise manager. All you're usually left with is making a script to enable any constraints. Nothing imports better than the system that generated the dump... You can download (super slow) Oracle from their site. The Linux version is a pain if you don't RTFM , but if you're short on time then try the brain-dead installer for the NT version. If you don't want to get into a data massage nightmare ( they aren't that bad after the first few if it boils down to that !) , try to get the data back into it's native format and export it to something more meaningful to the system you're trying to import to. P ----- Original Message ----- From: "Leslie Johnson - Contractor" <ljohnson@arl.army.mil> To: <pgsql-novice@postgresql.org> Sent: Friday, September 08, 2000 3:54 PM Subject: [NOVICE] A very novice question about ascii files > Hi all, > This is probably a very stupid question but I'm working on a really tight > deadline and I'm very confused. I've got several incredibly hefty ascii > files that were at one point Oracle databases. Now they're just plain > ascii dump files and today I got asked to put them in PGSQL but I have no > idea how (I've never even worked with databases before except for the > offline variety.) Can anybody please point me towards some documentation > that would be helpful or give me some advice? > Thanks so much, > Leslie