Not the usual memo truncating issues

[Guest]

Hi all,

Need some serious help - pulling my hair out with this one. I have a memo
field with almost 58000 characters. It is fine as-is in Access, but when I
export to .txt it truncates to 32000 characters.

I am trying to create a delimited flat file in .csv or .txt, but it's just
not working. I know that Excel has character limits, but I thought this
would be possible with Access. There must be something I can do.

Any suggestions?

 

[John Nurick]

Hi Marty,

I take it you've ruled out the possibility that the data is actually
being exported and the truncation is caused by the application you are
viewing the text file with, or importing it into.

Access's text import/export wizard doesn't handle records longer than
64k. Could you be hitting that limit? If so, you'll need to write your
own VBA code to export the data.



On Fri, 20 Jan 2006 12:24:02 -0800, "Toronto Marty" <Toronto

 

[Guest]

Thanks John,

I have ruled that out. The resulting files are 32k, and opening them in
notepad or Access shows only 32k. This is beyond anything I have seen, and
my only thoughts are that I must be missing something obvious.

Do you happen to know if there are limits when "exporting" versus the
TransferText command? Admittedly I am fairly new to this, but I can't
believe that this is a difficult function.

If anyone out there wants to literally paste more than 32k characters in a
memo field and try a simple export to .txt I'd love to hear the results.

Marty

 

[John Nurick]

Marty,

I've just put 60,000 characters into a memo field in a table in a test
database and exported it without problems to a CSV file, using the
standard text import/export wizard, and also using

DoCmd.TransferText acExportDelim,, "FAQ" ,"C:\Temp\FAQ-TT.txt", True

So it seems there's something else going on. Exactly how are you
'"exporting"' the data, and what else is in the records? What versions
of Access and Windows are you using?

 

[Guest]

Hi John,

I'm working on XP with Access 2003. I can't believe this has been so
difficult.

I've got HTML code in the memo-field. You can see the exact types of code I
have here: http://www.selloffdeals.com/flags/italy/italyflag.htm

Are there settings that could be doing this to me that I just don't know
about? The fact that it cuts off at 32k has to mean something - I jut don't
know what.

To export it I just use the EXPORT function, choose text format, comma
delimiter and that's about it. I have tried every variation of text marker
and delimiter that is available, with no success.

Are you doing it differently?

 

[John Nurick]

Hi Marty,

I get the same effect. It seems there's a bug in the delimited text
export routine that truncates a memo field 32,000 characters into the
field if the field contains quote marks.

I'll report it in case it's not been spotted before, but it's unlikely
that Microsoft will consider it worth fixing in an update to Access
2003.

The obvious work-round would be to write your own export code. But
storing biggish chunks of HTML code in CSV files seems a pretty unusual
thing to want to do: maybe there's a better solution to whatever your
problem is.

 

[Guest]

Thanks John.

At least I'm not crazy or otherwise unusual here - That is of some comfort.

This would make life a LOT easier if I was able to do it, but I can work
around it using notepad or textpad and simply pasting the html cosing
wherever I need it to go - I just didn't want to do that, and didn't think
that I would have to given that Access can do it, but doesn't want to with my
specific information.

Maybe someone else will read this post and know some nuance that you and I
are both missing, but in the mean time I'll assume it's a dead issue.

Thanks again for your help here. Even just putting it to bed frees up time
that I would have continued to use as problem solving.

Take care,
Marty

 

[John Nurick]

If you just want to get the contents of the memo field into an ordinary
text file - as opposed to a CSV or other "text data" file - it's easy.

One way is to use the WriteToFile() function at
http://www.j.nurick.dial.pipex.com/Code/index.htm.

You can simply include it in a query, like this, and open the query: The
calculated field will show the WriteToFile error code (0 for success)
and the file is exported as a byproduct (unless you have very strict
security settings).

SELECT WriteToFile(MemoField,"C:\Temp\test.txt") AS ErrorCode
FROM MyTable
WHERE IDCode='Marty';

Or you can use recordset operations in VBA to extract the value of the
field before writing it to disk.