Carriage Return in Imports

[Ted]

I have a Word document which holds a table. The table has four columns.
In the fourth column is the text of a series of documents. I want the
four columns to be four fields in an Access database.

These documents (fourth column) contain carriage returns (chr(13)).
Howevwer if I convert this to a tab delimited file the carriage returns
in the table are converted into chr(11) and when I import into Access
using Get External Data they show as square symbols rather than line breaks.

What can I do (in Access or Word) to make the fourth field (a memo
field) to hold chr(13) as a line break?

Thanks

Ted

 

[Dirk Goldgar]

I have a Word document which holds a table. The table has four
columns.
In the fourth column is the text of a series of documents. I want the
four columns to be four fields in an Access database.

These documents (fourth column) contain carriage returns (chr(13)).
Howevwer if I convert this to a tab delimited file the carriage
returns
in the table are converted into chr(11) and when I import into Access
using Get External Data they show as square symbols rather than line
breaks.

What can I do (in Access or Word) to make the fourth field (a memo
field) to hold chr(13) as a line break?

Thanks

Ted

One thing you could do, if you know that the original CR or CR/LF
combinations are being translated on import into Chr(11), is just run an
update query to convert them back after import. In Access 2002 or
later, and later SP-levels of Access 2000, your query can use the
built-in Replace function; e.g.,

UPDATE ImportedTable SET TheMemoField =
Replace(TheMemoField, Chr(11), Chr(10) & Chr(13));

If you're using an earlier version of Access, you'll need to write a
substitute for the Replace() function, but I won't bother posting that
unless you need it.

 

[Harvey Thompson]

One thing you could do, if you know that the original CR or CR/LF
combinations are being translated on import into Chr(11), is just run
an update query to convert them back after import. In Access 2002 or
later, and later SP-levels of Access 2000, your query can use the
built-in Replace function; e.g.,

UPDATE ImportedTable SET TheMemoField =
Replace(TheMemoField, Chr(11), Chr(10) & Chr(13));

If you're using an earlier version of Access, you'll need to write a
substitute for the Replace() function, but I won't bother posting that
unless you need it.

Dirk and Ted,

The "Chr(10) & Chr(13)" above *must* be "Chr(13) & Chr(10)."

 

[Dirk Goldgar]

Dirk and Ted,

The "Chr(10) & Chr(13)" above *must* be "Chr(13) & Chr(10)."

D'oh! You're absolutely right. I must have been half-asleep when I
posted that. Sorry, Ted.

 

[Michael J. Strickland]

You might try copying the table into Notepad and saving it.

Select table (drag over) and copy it (Ctl-C) into the clipboard.

Open Notepad and paste it into Notepad (Ctl-V).

Then save it from Notepad (as *.txt).

This should be a tab delimited file.