Trim spaces from merge data fields

[war4d]

I am working on a Word document that uses an export from our accounting software. We have no control over how this data is provided to us. This is for listing transactions and emailing them a remittance. There is a "Table" in the doc that is really just one field per line. They send the whole line as one field and if the line is blank we get several spaces, so line suppression is not working.



I would like to clean up the fields and remove the leading and tailing spaces. It would be even better if I could split a field into several to become a better output. Sometimes their output field wraps around to the next line and the "Columns do not line up.

I tried adding =Trim(<<mergefield>>) but it did not like that either.

I feel like we can do this with EditDataSource but the only examples I can find are about adding a line, not looping through each, and modifying the value.

This is what I have been trying but Datafields.Value is Read Only.

Sub CleanData()

Dim masterDoc As Document, singleDoc As Document, field As String, cols As Integer, rows As Integer
Set masterDoc = ActiveDocument
Set singleDoc = Documents.Add
If masterDoc.MailMerge.DataSource.Type = _
    wdMergeInfoFromWord Then
   With masterDoc.MailMerge
   .EditDataSource
     For Each afield In .DataSource.DataFields
     
         field = Trim(afield.Value)
         afield.Value = field
         singleDoc.Content.InsertAfter Text:=afield.Name  & ":" & afield.Value & vbCrLf
     Next afield
    End With
End If

End Sub

The new doc is just so I can see the results. Could it make a temp datasource from the current one and then connect to that? I could have it delete the temp file on close.

Here is some sample data. It is the L## fields that are the biggest issue.

sampleData.txt

This is the issue we get with some of the remittances:

Bad: RemirBadLines.png

Normal: remitGoodLines.png


I hope this makes sense, I was interrupted a few times writing this.

Thanks for any help,
Luke