Import a abnormal text file

[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.

 

[Guest]

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.

 

[Guest]

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.