count commas in a row of a text file using VBA

Question

I have an excel macro which our Finance team will use to convert an excel file to a particular format (comma delimited txt) so they can import the data to our system. The macro works great, with one exception. The final file needs to have 5 fields: Account Number, Card Number, Date, Transaction Code, Amount.

Only the Account Number OR the Card Number is mandatory - you don't need both. The problem is that when saving an Excel file as a comma delimited file (manually or using the macro) and there are no Account Numbers (i.e. all the records use only card numbers) then the first column is blank (file has no header), and the comma delimited file is then saved with only 4 fields, starting at the card number - it should start with a blank field, i.e. with an immediate comma - our system is now rejecting these files.

It saves like this:

1944210004744845,20092014,931,2191.33

It should save like this:

,1944210004744845,20092014,931,2191.33

As long as there is one account number somewhere in the first column everything is okay.

I don't want our developers to make changes, so I was thinking of adding a piece to the end of the macro, after saving the txt file, which will count the number of commas in the first row of the txt file, and IF there are only 3 commas, it must add a comma before every record in the file.

Could someone perhaps help with this, as I have no idea how to do that. Or perhaps there is a better way?

score 1 · Accepted Answer · answered Sep 26 '14 at 14:07

Starting with a file called 'myFile.txt' located in the C:\Temp directory that looks like this:

enter image description here

Run this code:

Sub readTXT()
    Dim FilePath As String
    Dim strFirstLine As String

    FilePath = "C:\temp\myFile.txt"

    'Load txt file into array
    Open FilePath For Input As #1
    dataArray = Split(Input$(LOF(1), #1), vbLf)
    Close #1

    'Test first line if it has three commas
    If Len(dataArray(0)) - Len(Replace(dataArray(0), ",", "")) = 3 Then
        'Add comma to start of strings
        For i = LBound(dataArray) To UBound(dataArray)
            dataArray(i) = "," & dataArray(i)
        Next i

        'Creat new file
        FilePath = Left(FilePath, Len(FilePath) - 4) & "_Comma.txt"
        Open FilePath For Output As #2

        'Write to new file modified data
        For Each Line In dataArray
            Print #2, Line
        Next Line

        Close #2
    End If
End Sub

Result is a new file called myFile_Comma.txt that looks like this:

enter image description here

You don't have to save to a new file but if something goes wrong it makes it easier to go back.

This works great except that each line in the text file now ends with a question mark - how can I remove them from the entire file? — user2725402, Sep 28 '14 at 14:54
One way, add this line: `Line = Left(Line, Len(Line) - 1)` before the `Print` line. It just removes the last character. If it's not always a '?' then test for it like this: `If Right(Line, 1) = "?" Then Line = Left(Line, Len(Line) - 1)` — Automate This, Sep 28 '14 at 15:37

score 0 · Answer 2 · edited May 23 '17 at 12:05

0

Counting characters in string: Count specific character occurrences in string

Iteration through the whole file line by line quick example:

dim c as Range
Set c = Range("A1")
do until IsEmpty(c.Value)
   if countTheCharacters(c.Value,",") < 3 then
     c.Value = "," & c.Value
   end if
   set c = c.Offset(1,0)
loop

edited May 23 '17 at 12:05

Community

1
1

answered Sep 26 '14 at 07:05

AnalystCave.com

4,884
2
22
30

How would I do this in a text file, i.e. my Range wouldn't be in my workbook at all, it would be a txt file I need to open and edit if the first line contains only 3 commas – user2725402 Sep 26 '14 at 07:12

count commas in a row of a text file using VBA

2 Answers2