Solved

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

Posted on 2015-01-12
3
48 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
excel column find duplicate count and compare value 28 69
Excel 2016 - Row 1 missing 5 67
Excel - Weightage 3 87
Calculate number of weeks in a month in MS Excel 2013 12 72
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.

932 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

11 Experts available now in Live!

Get 1:1 Help Now