Importing a "vertical" flat-file to Access

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

I didn't really know how to phrase the subject properly.

I have a flat-file (a ".dat" data source from/for a Word
Perfect Mail Merge document) that I converted to a ".txt"
file. The records are vertically-aligned, rather than
horizontally (today's only seeming "standard" for files).
Example:

RECIPIENT-NAME
RECIPIENT-AGENCY
RECIPIENT-ADDR1
RECIPIENT-CITY-ST-ZIP
REGARDING
ID-NUMBER
GREETING
DATE1
DATE2
CC-TO

The file has multiple entries, with the fields oriented
vertically, as the above example (but with actual data). I
am TRYING to get the data into an access table (or excel
spreadsheet for that matter), but the vertical orientation
makes it impossible for the translation. The orientation
the inport wizards seem to need is:

RECIPIENT-NAME;RECIPIENT-AGENCY;RECIPIENT-ADDR1;RECIPIENT-
CITY-ST-ZIP

And so on.

What can I do in order to make the importation possible.
There are a fixed number of fields per record, and records
are separated by CR/LF. However, in the text file, I COULD
insert any character for a record sep. Any information
someone can provide would be very helpful. Is there a 3rd
party program out there that will help? Do I have to write
my own?

Thanks in advance.
 
Put the data into Excel. Then select your data range and copy the data. Now, "Paste Special" and select transpose (bottom right corner of the special dialogue box). Paste the data into a new worksheet. Now import from that worksheet.
 
Hi Dennis,

If there's a blank line between records and a fixed number of fields per
record, this is easily done with find-and-replace in a word processor or
text editor.

First, replace all the blank lines with a string that doesn't appear
anywhere in the data, such as
%$%
To do this in Word, you'd need to find two successive paragraph marks,
which is done by entering
^p^p
in the Find What field of the Replace dialog.

Then replace all the line breaks (paragraph marks) with a suitable
separator character. The tab character is usually a good choice: in Word
you specify it with
^t

Finally replace all the
%$%
with paragraph marks and you'll have the data lined up so Access can
import it.
 
MAKE SURE that you work with a copy of the file.
(Not that I ever did this and ended up with one long paragraph.)
 
Back
Top