Trailing commas (1 Viewer)

[aziz rasul]

Using

DoCmd.TransferText acExportDelim, "tbl001 MRHDATA Export Specification", "qryTemp", strFolder & strFilename, False

I have the following header record.

H,PRODMASTFULL,4,2017-04-19 15:16:00,PRODUCT MASTER FULL,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

How do I get rid of the trailing commas using code?

 

[RuralGuy]

I would use a function like:

Public Function DelTrailing(InString As String) As String

DelTrailing = Left(InString, InStr(InString, ",,") - 1)

End Function

 

[aziz rasul]

I wanted to know how to do this in the text file itself using code in Ms Access. After the changes save the file.

 

[The_Doc_Man]

aziz, that would depend on what you are using for the file. If it is a WORD file, for example, the code might appear one way, while if it is NOTEPAD it would look another way. What is your context for the text file?

By the way, Allan's suggest is well-founded. If the problem occurs because of something you do in Access, you should try to fix it in Access. I.e. where you can do so, fix something where it occurs, not after-the-fact. The farther away you get from the error, the harder it becomes to fix.

 

[aziz rasul]

I create a header record on the first row in the first field in a table. There follows about 100,000 records followed by a trailing record agin in the first field field. When I use TransferText I get trailing commas in the resulting txt file in the header and trailing rows. I'm trying to get rid of the trailing commas.

If there is a way doing this at source that would be good but can't how. I was hoping to point to the first and last record in the txt file and resolve it that way.

 

[isladogs]

I would use a function like:

Public Function DelTrailing(InString As String) As String

DelTrailing = Left(InString, InStr(InString, ",,") - 1)

End Function

That's rather neat though it will trip up where there was a space in the string of commas

 

[static]

    Const filepath As String = "C:\test.txt"
    
    s = CreateObject("scripting.filesystemobject").opentextfile(filepath, 1).readall
    s = Split(s, vbNewLine)
    
    i = InStr(s(0), ",,")
    If i Then
        s(0) = Left(s(0), i-1)
        s = Join(s, vbNewLine)
        CreateObject("scripting.filesystemobject").opentextfile(filepath, 2).write s
    End If

 

[isladogs]

Hi Static

After reading your post, I'm just wondering if you could spare the time to cast your eye over a thread of mine in case you are able to offer advice

https://www.access-programmers.co.uk/forums/showthread.php?t=293232

Many thanks in advance

 

[aziz rasul]

static, your code works great for the header record. How would I modify the code for the last record? There are about 100,000 records between the first and last records.

 

[static]

The code reads the file contents into a string and splits it into an array.
Each line of the array is a line in the file.
Arrays usually start at index 0, so s(0) is the first line.
They don't always start at 0 and if you aren't sure you can check using LBound(array) (lower bound).

To find and change the last line you need to know how many items are in the array. For that you get the upper bound, UBound(array).

Const filepath As String = "C:\test.txt"

s = CreateObject("scripting.filesystemobject").opentextfile(filepath, 1).readall
s = Split(s, vbNewLine)

i = InStr(s(0), ",,")
If i Then s(0) = Left(s(0), i-1)

j = InStr(s(ubound(s)), ",,")
If j Then s(ubound(s)) = Left(s(ubound(s)), j-1)

if i or j then
    s = Join(s, vbNewLine)
    CreateObject("scripting.filesystemobject").opentextfile(filepath, 2).write s
end if

 

[aziz rasul]

Many thanks static.