Importing from Word table with carriage returns/paragraph marks

[Guest]

Hi,

I have a huge Word table that I have to import into an Access database. Some
of the fields have carriage returns and/or paragraph marks. I tried importing
into Excel first, but Excel creates a new row every time it sees a carriage
return or paragraph mark. Of course, if I was able to import directly into an
Access database it would be even better, but data is harder to manipulate in
Access. Any ideas how I can do this? I searched the forums but found only
dead links...

Thank you.

 

[Guest]

Hi Audrey,

A trick that often works is
1) Paste the table into a new Word document
2) Save as HTML
3) Go to Access and import from the HTML document.

Alternatively, replace all the paragraph marks in the table with something
that doesn't appear anywhere in the document (e.g. replace all "^p" with
"&&&"). Then when you get it into Access restore the paragraphing by
replacing all "&&&" with Chr(13) & Chr(10). Typically you'd do this in an
update query, e.g.

UPDATE MyTable
SET TheField = Replace(TheField, "&&&", Chr(13) & Chr(10))
;