Solved

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

Posted on 2015-01-12
3
36 Views
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.
0
Comment
Question by:ninjamike
3 Comments
 
LVL 14

Accepted Solution

by:
Zack Barresse earned 500 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
        Else
            LineOutput = LineText
        End If

        Print #FileNum2, LineOutput

    Loop

    CorrectAdditionalColumnInCSV = 1

ExitWithoutError:
    Close #FileNum1
    Close #FileNum2

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

    Exit Function

ExitWithError:
    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.

Regards,
Zack Barresse
0
 

Author Comment

by:ninjamike
ID: 40545915
Works great
..Thanks..
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

With the internet and the ease of information transference, many professional jobs can be done anywhere today.  Why should it make a difference whether an x-ray is read in India or the United States as long as the radiologist is qualified?   Outso…
A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now