Need help removing unwanted commas that sometimes appear in rows in csv files

Posted on 2015-01-12
Medium Priority
Last Modified: 2016-06-10
Need help removing unwanted commas that sometimes appear in rows in csv files

I am looking for  a VBA macro I can run from Excel file (acting as a console) to look at my files in a folder and solve the following:
I receive hundreds of csv files which I need to merge every week but some of the files have rows with an extra comma in column 15 which causes the rows to extend into a 19th column.
I need a solution that will search all selected csv files(one file at a time) in a folder and for any files which it sees rows which have different numbers of data fields (columns) of both 18 and 19 columns;
the code must then (one row at a time starting with row 2) fix every row that has 19 columns by removing the 15th comma in that row - So that all the rows have the correct number of data fields (columns), which is 18.
Then it will save the file back to the same or a different directory which I can select.

I should clarify, this issue is not that there are not 2 commas that are together.
The issue is: on some rows the Logon ID (15th column) is entered in a 'Lastname, Firstname' format. When I open the csv file, I can see that the 'user entered comma' between 'Lastname' and 'Firstname' pushes the 'Firstname' and remaining data fields in the row 1 column to the right. This is why I have to find and remove that extra comma when is appears - and it appears randomly in about 1/3 of all my files I am required to work with.

Thanks for any assistance that may be provided.
Question by:ninjamike
LVL 14

Accepted Solution

Zack Barresse earned 2000 total points
ID: 40545912
Run the first routine to choose a folder and run all CSV files found in it (subfolders not included). Run the second routine to choose one or more individual files.

Option Explicit

Public Enum FilterType
    CSV = 0
    XL = 1
End Enum

Private Const FilterCSV As String = "CSV Files (*.csv), *.csv"
Private Const FilterXL As String = "Excel Files (*.xl*), *.xl*"

Dim TargetCount As Long
Dim TargetCountA As Long

Sub CorrectColumnInCSV_Folder()

    Dim SelectFolder As FileDialog
    Dim FSO As Object
    Dim TargetFolder As Object
    Dim TargetFile As Object

    Set SelectFolder = Application.FileDialog(msoFileDialogFolderPicker)

    TargetCount = 0
    TargetCountA = 0
    SelectFolder.AllowMultiSelect = False
    If SelectFolder.Show Then
        Set FSO = CreateObject("Scripting.FileSystemObject")
        Set TargetFolder = FSO.GetFolder(SelectFolder.SelectedItems.Item(1))
        For Each TargetFile In TargetFolder.Files
            If UCase(Right(TargetFile.Name, 4)) = ".CSV" Then
                TargetCountA = TargetCountA + 1
                If ISFILEOPEN(TargetFile.Name) = False Then
                    TargetCount = CorrectAdditionalColumnInCSV(Target:=TargetFile.Path, _
                                                               ColumnCount:=18, _
                                                               MergeColStart:=15 _
                                                                              ) + TargetCount
                End If
            End If
        Next TargetFile
    End If

    MsgBox TargetCount & " of " & TargetCountA & " files have been processed.", vbInformation, "Complete!"

End Sub

Sub CorrectColumnInCSV_File()

    Dim TargetFile As Variant
    Dim TargetName As String
    Dim LoopStep As Long

    TargetFile = Application.GetOpenFilename(FilterReturn(CSV), MultiSelect:=True)
    If Not IsArray(TargetFile) Then
        Exit Sub
    End If

    TargetCount = 0
    TargetCountA = UBound(TargetFile) - LBound(TargetFile) + 1
    For LoopStep = LBound(TargetFile) To UBound(TargetFile)
        TargetName = Right(TargetFile(LoopStep), Len(TargetFile(LoopStep)) - InStrRev(TargetFile(LoopStep), "\"))
        If ISFILEOPEN(TargetName) = False Then
            TargetCount = CorrectAdditionalColumnInCSV(Target:=TargetFile(LoopStep), _
                                                       ColumnCount:=18, _
                                                       MergeColStart:=15 _
                                                                      ) + TargetCount
        End If
    Next LoopStep

    MsgBox TargetCount & " of " & TargetCountA & " files have been processed.", vbInformation, "Complete!"

End Sub

Private Function CorrectAdditionalColumnInCSV( _
        ByVal Target As Variant, _
        ByVal ColumnCount As Long, _
        ByVal MergeColStart As Long, _
        Optional ByVal Delimiter As String = ",", _
        Optional ByVal ReplaceDelimiter As String = " ") As Long

    Dim TempFile As String
    Dim TempName As String
    Dim TempPath As String
    Dim TempExt As String
    Dim FileNum1 As Long
    Dim FileNum2 As Long
    Dim LineText As String
    Dim LineOutput As String
    Dim LineItems() As String
    Dim NDX As Long
    Dim i As Long

    TempPath = Left(Target, InStrRev(Target, "\"))
    TempName = Right(Target, Len(Target) - Len(TempPath))
    TempExt = Right(TempName, Len(TempName) - InStrRev(TempName, "."))
    TempName = Left(TempName, Len(TempName) - Len(TempExt) - 1) & "(temp write)." & TempExt
    TempFile = TempPath & TempName

    FileNum1 = FreeFile()
    Open Target For Input Access Read As #FileNum1
    FileNum2 = FreeFile()
    Open TempFile For Output Access Write As #FileNum2

    Do While Not EOF(FileNum1)

        LineText = ""
        LineOutput = ""

        Line Input #FileNum1, LineText
        If Right(LineText, 1) = Delimiter Then LineText = Left(LineText, Len(LineText) - 1)

        LineItems = Split(LineText, Delimiter)

        If UBound(LineItems) - LBound(LineItems) + 1 > ColumnCount Then
            LineOutput = WorksheetFunction.Substitute(LineText, Delimiter, ReplaceDelimiter, MergeColStart)
            If InStr(1, LineOutput, Chr(34), vbTextCompare) > 0 Then
                LineOutput = Replace(LineOutput, Chr(34), vbNullString)
            End If
            LineOutput = LineText
        End If

        Print #FileNum2, LineOutput


    CorrectAdditionalColumnInCSV = 1

    Close #FileNum1
    Close #FileNum2

    If Dir(TempFile, vbNormal) <> "" Then
        If Dir(Target, vbNormal) <> "" Then FileCopy TempFile, Target
        Kill TempFile
    End If

    Exit Function

    CorrectAdditionalColumnInCSV = 0
    Resume ExitWithoutError

End Function

Function ISFILEOPEN(FileName As String) As Boolean
    Dim iFilenum As Long
    Dim iErr As Long
    On Error Resume Next
    iFilenum = FreeFile()
    Open FileName For Input Lock Read As #iFilenum
    Close iFilenum
    iErr = Err
    Select Case iErr
    Case 0: ISFILEOPEN = False
    Case 70: ISFILEOPEN = True
    Case Else: Error iErr
    End Select
    On Error GoTo 0
End Function

Private Function FilterReturn(ByVal Value As FilterType) As String
    Select Case Value
    Case FilterType.CSV: FilterReturn = FilterCSV
    Case FilterType.XL: FilterReturn = FilterXL
    End Select
End Function

Open in new window

NB: This was solved here.

Zack Barresse

Author Comment

ID: 40545915
Works great

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question