importing multiple lines of text into a field in table

[Kiran]

Hi,
I'm new to both Access and VBA.
I have a text file with multiple fields to be imported to access.
But text file does not have fixed width.
Some fields have multiple lines of data. Example:
Name: John
Designation: Supervisor_Thermal
Responsibilities: line1
line2
line2
Achievements: line1
line2
Shift: general

I have created a table with the respective fields.
I can import fields, using macro, which end in the same line by using InStr.
But I don't know how to import fields with multiple lines.

Also, please note that there are thousands of records with this format
continuing in the same text file.
Any help would be greatly appreciated.
Thanks,

 

[wphx]

One trick I often use in this kind of thing, is to pre-format the text file
using a word processor. Especially easy in word perfect. I do a
search/replace on the field delimeter and replace it with a [Tab] character
(to do this in word you would need to use 'special characters'). I then
replace everything ending in a <carriage return> followed by x number of
spaces and replace it with a <carriage return> followed by a [Tab]
character.

I then save this as a text file, and import it into excel or access using
the [Tab] character as the field delimiter. This will have the effect of
import the text information into the correct fields.

You could then write a simple vba routine to read through the table, joining
together the different lines until you get to a new record header.

Easy if you only have to do it once. If its something you need to repeatedly
do, either design a word macro, or do everything in vba

FWIW