Importing files from 8i - strip away the padding

[SidUK]

A customer has sent me some files he has exported (actually SQL outputs) from 8i. He says he has delimited them with a semi-colon, but they are effectively fixed-width. The records are padded with spaces. This has made the files massive - one of them is 2GB, others are around 1GB - they should be more like 50MB at most.

I'm trying to import them into Access 97 because of what we are trying to do with the data. Access can't handle the width of the data. I have Oracle 8i available to import the files - would this help?

I can't open them in a text editor (Word, texted) (file too big) and strip out the spaces, textsoap can't handle them (too big). I've written some scripts that read the files line by line, strip out the spaces (and convert the semi-colons to commas) and then append the lines to a new file, but this is taking a huge amount of time (probably be finished in a couple of years). And this is on a 1.3GHz Intel machine (W2000 P), 160MB ram and 1.5GB paged memory available. The customer doesn't know how to export the data without spaces.

Any advice?

Regards, Sid

 

[DBAwhosaysNIE]

hey,

assuming you're just doing spool <file> & select <blah, blah> from sqlplus you could just output everything as one logical column. in other words:

select <varchar column>
||'<delimiter>'
||to_char(<number column>)
||'<delimiter>'
||to_char(<date column>)
--add additional columns as needed
from <table_name>
/

optionally you could also wrap them in trim functions.

hope this helps!

 

[carp]

You might have to load the data into an Oracle table via SQL Loader. If you define the character fields as VARCHAR2 you should be able to get rid of the trailing blanks. Then when you spool the data out you should have a much &quot;thinner&quot; set of rows.