none
Outputting to a CSV RRS feed

  • Question

  • Hello everyone,

    I am trying to read a fix width file and output it to a csv file.  I have the reading part working, but I am stuck on outputting to csv.

    newfNam is the output file name, and it may or may not exist.  I don't know how to handle it if it does, I want to overwrite it.

    fPath is the path of the text file.

    Where I have the ****Place holder, I want to output each currentField and a comma until I reach the end of the line where i want to put in a vbCrLf.  But I want to read each line and make sure it doesn't contain a comma already, and if it does, I want to encase that field in double quotes, so excel doesn't explode when I try to open the csv.

    I've tried using objWriter but I'm not sure I'm using it correctly.  I've backed this code out to a point where I know it is parsing each field correctly.

    Public Class Ripper
        Public fPath As String = ""
        Private fName As String
        Public Sub New(ByVal fNam As String)
            fPath = fNam
            Dim newfNam As String
    
            newfNam = fPath.Substring(0, fPath.Length - 4) & ".csv"
            'MsgBox(newfNam)
            'MsgBox(fPath)
    
            Using Reader As New Microsoft.VisualBasic.FileIO.TextFieldParser(fPath)
    
                Reader.TextFieldType =
                   Microsoft.VisualBasic.FileIO.FieldType.FixedWidth
                Reader.SetFieldWidths(25, 150, 50, 50, 50, 50, 25, 50, 25, 300, 161, 25, 25, 50, 25, 49, 50, 150, 5, 25, 25, 50, 242, 15, 750, 4, 3, 10, 11, 18, 1, 1, 1, 1, 1, 1, 1, 52, 1, 27, 1, 1, 45)
                Dim currentRow As String()
    
                While Not Reader.EndOfData
    
                    Try
                        currentRow = Reader.ReadFields()
                        Dim currentField As String
                            For Each currentField In currentRow
                                'MsgBox(currentField)
                                '******* Place Holder
                            Next
                        Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                            MsgBox("Line " & ex.Message &
                            "is not valid and will be skipped.")
                        End Try
    
                End While
    
            End Using
    
        End Sub
    End Class

    Any help would be amazing.  Thanks.

    Wednesday, September 26, 2018 6:29 PM

Answers



  • Where I have the ****Place holder, I want to output each currentField and a comma until I reach the end of the line where i want to put in a vbCrLf.  But I want to read each line and make sure it doesn't contain a comma already, and if it does, I want to encase that field in double quotes, so excel doesn't explode when I try to open the csv.


    It's not clear exactly with which part of the task you need help.
    Writing a file? Building a comma-delimited string? Other?

    Here's one approach. It assumes you don't want a comma at the end of each 
    line. If you do, then adjust it accordingly.

    Public Class Ripper
        Public fPath As String = ""
        Private fName As String
        Public Sub New(ByVal fNam As String)
            fPath = fNam
            Dim newfNam As String
    
            newfNam = fPath.Substring(0, fPath.Length - 4) & ".csv"
            'MsgBox(newfNam)
            'MsgBox(fPath)
    
            Using fout As StreamWriter = New StreamWriter(newfNam)
    
                Using Reader As New Microsoft.VisualBasic.FileIO.TextFieldParser(fPath)
    
                    Reader.TextFieldType =
                       Microsoft.VisualBasic.FileIO.FieldType.FixedWidth
                    Reader.SetFieldWidths(25, 150, 50, 50, 50, 50, 25, 50, 25, 300, 161, 25, 25, 50, 25, 49, 50, 150, 5, 25, 25, 50, 242, 15, 750, 4, 3, 10, 11, 18, 1, 1, 1, 1, 1, 1, 1, 52, 1, 27, 1, 1, 45)
                    Dim currentRow As String()
    
                    While Not Reader.EndOfData
                        Try
                            currentRow = Reader.ReadFields()
                            Dim currentField As String
                            Dim lineout As String = ""
    
                            For Each currentField In currentRow
                                'MsgBox(currentField)
                                '******* Place Holder
                                If currentField.Contains(","c) Then
                                    currentField = currentField.Insert(0, """")
                                    currentField &= """"
                                End If                            
                                If lineout.Length <> 0 Then
                                    lineout &= ","
                                End If
                                lineout &= currentField
                            Next
                            fout.WriteLine(lineout)
                        Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                            MsgBox("Line " & ex.Message &
                                "is not valid and will be skipped.")
                        End Try
                    End While
                End Using
            End Using
        End Sub
    End Class
    

    - Wayne


    • Marked as answer by stopiamwarren Thursday, September 27, 2018 5:18 AM
    • Edited by WayneAKing Thursday, September 27, 2018 1:08 PM
    Thursday, September 27, 2018 2:55 AM

All replies

  • Perhaps this conceptual example may help.

    Dim sb As New Text.StringBuilder
    Dim Line As String = "Karen,,Payne,Developer"
    Dim items = Line.Split(","c)
    For Each item In items
        If Not String.IsNullOrEmpty(item) Then
            sb.Append(item)
        Else
            sb.Append(","""",")
        End If
    Next


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, September 26, 2018 8:36 PM
    Moderator


  • Where I have the ****Place holder, I want to output each currentField and a comma until I reach the end of the line where i want to put in a vbCrLf.  But I want to read each line and make sure it doesn't contain a comma already, and if it does, I want to encase that field in double quotes, so excel doesn't explode when I try to open the csv.


    It's not clear exactly with which part of the task you need help.
    Writing a file? Building a comma-delimited string? Other?

    Here's one approach. It assumes you don't want a comma at the end of each 
    line. If you do, then adjust it accordingly.

    Public Class Ripper
        Public fPath As String = ""
        Private fName As String
        Public Sub New(ByVal fNam As String)
            fPath = fNam
            Dim newfNam As String
    
            newfNam = fPath.Substring(0, fPath.Length - 4) & ".csv"
            'MsgBox(newfNam)
            'MsgBox(fPath)
    
            Using fout As StreamWriter = New StreamWriter(newfNam)
    
                Using Reader As New Microsoft.VisualBasic.FileIO.TextFieldParser(fPath)
    
                    Reader.TextFieldType =
                       Microsoft.VisualBasic.FileIO.FieldType.FixedWidth
                    Reader.SetFieldWidths(25, 150, 50, 50, 50, 50, 25, 50, 25, 300, 161, 25, 25, 50, 25, 49, 50, 150, 5, 25, 25, 50, 242, 15, 750, 4, 3, 10, 11, 18, 1, 1, 1, 1, 1, 1, 1, 52, 1, 27, 1, 1, 45)
                    Dim currentRow As String()
    
                    While Not Reader.EndOfData
                        Try
                            currentRow = Reader.ReadFields()
                            Dim currentField As String
                            Dim lineout As String = ""
    
                            For Each currentField In currentRow
                                'MsgBox(currentField)
                                '******* Place Holder
                                If currentField.Contains(","c) Then
                                    currentField = currentField.Insert(0, """")
                                    currentField &= """"
                                End If                            
                                If lineout.Length <> 0 Then
                                    lineout &= ","
                                End If
                                lineout &= currentField
                            Next
                            fout.WriteLine(lineout)
                        Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                            MsgBox("Line " & ex.Message &
                                "is not valid and will be skipped.")
                        End Try
                    End While
                End Using
            End Using
        End Sub
    End Class
    

    - Wayne


    • Marked as answer by stopiamwarren Thursday, September 27, 2018 5:18 AM
    • Edited by WayneAKing Thursday, September 27, 2018 1:08 PM
    Thursday, September 27, 2018 2:55 AM
  • An alternative to this:

    If currentField.Contains(","c) Then
        currentField = currentField.Insert(0, """")
        currentField &= """"
    End If                            
    

    could be this:

    If currentField.Contains(","c) Then
        currentField = """" & currentField & """"
    End If
    

    - Wayne

    Thursday, September 27, 2018 3:26 AM
  • You are amazing man.

    Thanks you, seriously!

    Thursday, September 27, 2018 5:19 AM