Importing text passages (memo length)

[Guest]

Hi,

I am having trouble importing text records -- meaning snetences and
paragraphs -- into a simple Access 2002 database. I’d be grateful for any
help; it would save me hours and hours of manual cut-and-paste from Word into
Access

The records I want to import are text passages which originate in Word.
Within some records you might find more than one paragraph. I want that
formatting preserved. I signify the end of a record with a “#†character.

I create an Access database consisting of two fields: a) one for the text
records, with the data type set to memo so it can handle long text passages,
and b) one for the primary key ID, which when prompted I let Access create
automatically.

Here’s what happens when I try to import the data to my “Item†fieldâ€.
First, I cut and paste all my original text from Word into Notepad. Then in
Access I go File / Get External Data, and then choose Import, and choose Text
as the data type. In the ensuing wizard I choose the option for delimitation
by a specified character, and make “#†that character. I then finish the
wizard. I get error messages that not all data imports successfully, due to
key violations, and when I check, nothing has been imported.

Here are the record I'm trying to import from Notepad:

Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

#

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

#

ccccccccccccccccccccccccccccccccccccccccccccc

ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc

cccccccccccccccc

#

ddddddddddddddddddddddddddddddddddddddddddd

#

(end of records)

I've asked on two other forums but no propsed solution works. Can this
really be so difficult? Any advice?

 

[John Nurick]

Hi Lingyai,

Access's built-in text file routines assume that records are separated
by line breaks, and there is no way to change this. The "delimiter"
option refers to the character that is used to separate fields within
records.

So this presents a problem when, like yours, the field values themselves
contains line breaks. The standard way to handle this is to enclose each
field value in quote marks (the Microsoft terminology is "text
qualifier"), in which case your data would need to be like this:

"Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
"
"bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
"
"ccccccccccccccccccccccccccccccccccccccccccccc

ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc

cccccccccccccccc
"
"ddddddddddddddddddddddddddddddddddddddddddd
"

If you do that, you must also make sure that any quote marks in the
field values are doubled, e.g. if the original value in Word is
"Hello," he said.
it must first be changed to
""Hello,"" he said.
and finally appear in the text file as
"""Hello,"" he said."