Import a abnormal text file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been reading the forums for a while without finding an answer to my
question. I have report file created by a mainframe that I need to import to
a database for manageability. The file contains 16 fields in three different
columns The file looks somewhat like this:

COLUMN 1 COLUMN 2 COLUM 3
SUBSCRIBER ID: Data
NAME/ADDRESS: Data TAX ID:
Data
STATUS: Data DAY: data DOB: Data
EMPLOYEE: Data EVE: data ADD: data
etc.

I cannot use the import wizard because each colum has several fields. Is
there a way to import this file?

Is there a code that will turn every X rows into one long row so I can use
the standard fixed width import wizard?

Changing the mainframe layout requires an act of congress before I can
change anything on it so that's not an option. Any and all help is greatly
appreciated.
 
I'd open up the file in Word. Then I'd do a search and replace on the
following:

^pSTATUS {space}{space}{space}{space}STATUS
^pEMPLOYEE {space}{space}{space}{space}EMPLOYEE

Replace the {space} with actual spaces.

Then you could also get rid of the first row where it says COLUMN1 etc.

Next get rid of all the things like SUBSCRIBER ID: by doing a search and
replace with nothing.

You could even record a Word macro to clean up things for you automatically
if you need to do this task often.

Save the file as text and see how well it imports.
 
Thanks for your reply. Your advice gave me an idea. I imported the file to
Excel and created one row with the information I needed. For example G1=A1,
H1=A2, I1=A3, etc. I then copied the formula to all rows in the report,
copied and pasted special to a new sheet. From there it was easy to import to
access and do a final clean out there.

This will be a reiterated procedure. With the help of macros both in Excel
and Access, I was able to accomplish what I needed.
 
Back
Top