How to prevent truncation of memo field (field > 255 char) on export to text file. (1 Viewer)

BButler

New member
Local time
Today, 06:44
Joined
Mar 6, 2010
Messages
5
Thought I'd post this extremely simple solution as I wasn't able to easily find it elsewhere. Solution thanks to thealy via BigResouce.com.

Issue:
Memo type table fields (or query generated strings) in excess of 255 characters are complete in Access but are truncated when exported to text file.

Solution:
1) Right click table or query object -> Export -> Text File
2) Select destination path leaving all export options unchecked, click OK
3) In the Export Text Wizard make all your desired export criteria selection and then click Advanced...
4) In the Export Specification, under Field Information:, hover your cursor carefully over (and slightly to the right of) the line that separates the "Field Name" column and the next blank column until you see your cursor go to double line. Then click and drag to reveal the collapsed columns (just like you would in Excel). Keep expanding out the columns until you get to the "Data Type" column.
5) Set the "Data Type" column to "Memo"
6) Click Save As... if you want to reuse the Export Specification and then click OK
7) Back in the Export Text Wizard click Finish

To understand reasons for field truncation within Access (see http://allenbrowne.com/ser-63.html)
 

BButler

New member
Local time
Today, 06:44
Joined
Mar 6, 2010
Messages
5
Re: How to prevent truncation of memo field (field > 255 char) on export to text file

Solution appended:

Short Answer - Confirm your Export Specification "Data Type" is set to Memo.

Step "4)" should begin with -> In the Export Specification, confirm your Export Specification "Data Type" is set to Memo. If you do not see the "Data Type" column available, under Field Information:, hover your cursor...
 

timo1999

Registered User.
Local time
Today, 08:44
Joined
Aug 31, 2009
Messages
13
Re: How to prevent truncation of memo field (field > 255 char) on export to text file

You can also export the file as a "Paradox" file (latest version, probably 5.0). The file will be saved with the extension .db and there may be a second file saved with the same name and an extension of .mb

Open the newfile.db with Excel, and there should not be any truncated data from the fields that are "memo" in Access.

Paradox does not care how large the field is like Access does.
 

ariostel

New member
Local time
Today, 06:44
Joined
Mar 26, 2013
Messages
2
Re: How to prevent truncation of memo field (field > 255 char) on export to text file

A conditional expression in a query creates a new query, and therefore the field type is not preserved:

[that-field] is type "memo":
If [this-field] is null, then do nothing, else export [that-field].
Now [that-field] is type "text".

But directly exporting [that-field] without the conditional preserves the memo type.

This appears to be true for expressions like

"<![CDATA[" + [my-memo-field] +"]]>"

where [my-memo-field] might not always be preserved as memo for export.

Better to do all that upstream, I guess.
 

Users who are viewing this thread

Top Bottom