Memo fields truncated by TransferText


U

usenetseu3

Ok, so this is a topic virtually beaten into the ground on this
newsgroup. After searching and working on this issue for a while, I
have decided I won't get any further much faster without help.

As the topic states, my memo fields are being truncated on export. I am
using a VBA script in my Access 2003 database to read in a CSV file,
process it, and export it into CSV again. I have verified that my memo
fields are preserved during import and during the processing, but my
exported CSV files are all truncated to 255.

Any help, criticism, advice is welcome.

I use a TransferText command to import a CSV file into a table named
"importedData". This table contains a field, "memoField" with data that
is typically around 600 text characters long. Using Design View I am
able to verify that "memoField" is indeed of type Memo (although I of
course made sure of this using an import specification) and using
Datasheet View I verify that the data in "importedData" has not been
truncated. Good so far.

I now need to process the fields in "importedData" and export the
results into another CSV file, using TransferText. Per the instructions
on the Microsoft Help website,
http://support.microsoft.com/default.aspx?scid=kb;en-us;207668, I
attempt to prevent truncation of the data in "memoField" by creating a
table with a Memo data field and using an append query, like so:

sql_string = "CREATE TABLE " & newTable & " ( newMemoFieldMemo );"
DoCmd.RunSQL sql_string
sql_string = "INSERT INTO " & newTable & " ( newMemoField )" & _
" SELECT DISTINCT importedData.newMemoField AS newName" &_
" FROM importedData" & _
" WHERE newMemoField Is Not Null;" & _

and then export, like so:

DoCmd.TransferText acExportDelim, "Export Spec", newTable, newFile,
True

I _believe_ I have covered all my bases. Does anyone think if any of
the cases under which a Memo is truncated to 255 characters apply here?
Is there something I am overlooking?

Let me know if I can provide any more information that would help shed
some light on the issue. Many, many thanks in advance.

Beaten into the ground,
Usenet Seu
 
Ad

Advertisements

Ad

Advertisements

Joined
Oct 14, 2016
Messages
1
Reaction score
0
I encountered the same problem in Access 2010 when I tried to export a Query to a text file.
What I did to solve the issue is the following;
  1. Go to Navigation Options and enable show system objects (File>Options>Navigation Options>...)
  2. Open MSysIMEXColumns table
  3. Find the FieldName and set the DataType to "12" (Memo) instead of "10" (Text)
  4. That's it!
Note: Just be sure you are editing the correct Import Export Specification. You may check the SpecID in MSysIMEXSpecs table. Not tested in CSV or in any Import Specs but I think it should work. Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top