Using TransferText to export to CSV file from Access: Fields over 255 Characters are truncated

[RMittelman]

I have an Access project where I'm exporting my data set to a CSV file so I can do a Word mail merge without having to interact with Access in the merge.


Desired data is in a table, and I do TransferText like this code snippet:

    On Error Resume Next
    If TempVars!MergeDataFileType = "CSV" Then
        datFile = CurrentProject.Path & "\_MergeData.txt"
        If fso.FileExists(datFile) Then fso.DeleteFile datFile, True
        DoCmd.TransferText acExportDelim, , DataSource, datFile, True
        exportErr = Err

When I complete the process, inspecting the CSV file shows that long text fields are truncated. Those text fields in the table are defined as Long Text, and the table field contains the complete text. Only when doing the TransferText command does it get truncated.

Previously, I was using code to loop through the field names and write to the CSV file, then loop records and fields to write the data. I switched from that lengthy process to the TransferText option because it seemed so much simpler. Didn't realize I would have a truncation problem.

So my choices are to revert back to the lengthy code (not positive if the problem would go away with that), or figure out how to prevent the truncation of field values longer than 255 characters.

Any ideas of how to prevent this truncation from happening? Thanks...

[CJ_London]

what is the datasource? if it is an aggregate query or uses DISTINCT then it will get truncated

[Micron]

This is a known problem I think, when not using an import spec? Sometimes reported to be caused by csv from Excel file where some of the first rows of a column are blank, so Access determines the column to be short text. See if this helps

https://www.utteraccess.com/topics/1964676

[RMittelman]

what is the datasource? if it is an aggregate query or uses DISTINCT then it will get truncated

Thanks for the reply Ajax. The problem isn't related to a query. The data source is a table which I'm applying the TransferText method to. The issue is only for fields containing more than 255 characters.

[Gicu]

Have you set up an export specification in which you saved the right data type for each field?
EDIT: sorry for duplication. Question for Admin: How do you delete a duplicate post?
Cheers,

[Gicu]

Have you set up an export specification in which you saved the right data type for each field? Also make sure there is no formatting for those fields in table design.

Cheers,

[RMittelman]

This is a known problem I think, when not using an import spec? Sometimes reported to be caused by csv from Excel file where some of the first rows of a column are blank, so Access determines the column to be short text. See if this helps

https://www.utteraccess.com/topics/1964676

Thanks, but that article is more for importing issues. I'm trying to export a table to CSV file. I created an export spec, and verified that the long columns are type 12, which is for memo / long text. Even with the export spec, it still truncates one of the fields to 255 characters.

[RMittelman]

Have you set up an export specification in which you saved the right data type for each field?
EDIT: sorry for duplication. Question for Admin: How do you delete a duplicate post?
Cheers,

Thanks Vlad. Did setup an export spec. The long fields are all type 12, meaning memo / long text. There's no way to edit the spec in the UI, but you can open the MSysIMEXSpecs and the MSysIMEXColumns tables and verify/change specs there. Still, data is being truncated.

[Gicu]

Anything in the Format property of that field (maybe @)?

[RMittelman]

Here's an update:

It turns out the issue isn't with the export at all. Just caught the real issue. I have a table containing various fields, including the memo/long text field which is the issue. Before trying to export, I write the data into a different "combined" table. This is because my source table has multiple records which I want to combine into a single record based on a key field. Most of the fields are the same for the multiple records, but some differ between records. I can't use a group-by query because it's supposed to append data to a table. Also, instead of having aggregate functions for some fields, I need them accumulated differently. Sort of like this:

ID  Name   Paragraph1             Dates   Amounts
3   Joe    This is a paragraph   1/1/2020   $50
                                 3/2/2020   $100

5   Bill   Another paragraph     5/2/2020   $75
                                 5/9/2020   $37

So, I run a query to select distinct the ID, Name & Paragraph1 and insert them into the combined table. Then I loop thru the source records and collect the dates and amounts into long strings, then update them into the combined table.

To paraphrase my first query, it's like this:

Insert into CombinedTable (ID, Name, Paragraph1) Select Distinct ID, Name, Paragraph1 From OtherTable

This is where the problem occurs, because the OtherTable has a Paragraph 1 of 270 characters, and in the CombinedTable it's truncated to 255 characters. So this post should have been titled "Insert query truncates data".

Do I need to end this thread and start another one? Thanks...

Another Update: Seems the problem is the Select Distinct. If I do a select query without the insert, it shows the full field. If I do a select distinct query, the long field is truncated. Has nothing to do with the insert part of the query above.

[Gicu]

Looks like Ajax gave you the answer in post #2.

Cheers,

[isladogs]

For future reference, here is the complete list of reasons causing truncation of memo/long text fields http://allenbrowne.com/ser-63.html

[RMittelman]

Thanks for all of the replies. Ajax, you got it right, but I didn't realize at the time you answered. I was still thinking the issue was the export, not the query I used to get the data into the combined table.

It's easy to recreate the issue by doing a simple select query which includes a memo field with data longer than 255 characters. If you turn on Unique Values, that puts a "Distinct" in the SQL, and the long field is truncated. If you don't, the entire field comes out.

I was originally using an "Insert Into ... Select ..." SQL which made my code more compact, but introduced that issue. I switched to opening a recordset of the empty Combined table, then opened a recordset of the source table and looped through that. When my key field changed, then I did an Add New on the combined recordset and set each field value from the other recordset's fields. That fixed the issue, and no more truncations.

Once again, thanks so much for all of the suggestions...

[Gicu]

An easier alternative might have been to use a GroupBy (totals) query instead of using Distinct to get your unique values and in that query change the Group By (which would also truncate memo fields) to First or Last for all the memo fields.

Cheers,
Vlad

[RMittelman]

An easier alternative might have been to use a GroupBy (totals) query instead of using Distinct to get your unique values and in that query change the Group By (which would also truncate memo fields) to First or Last for all the memo fields.

Cheers,
Vlad

Seems in my experience a group-by query won't work as an append query. At least in the GUI, it disables the Totals button on query designer if it's an append query.