Best method For Sorting a tab delimited CSV into an object?

Question

I have an object that looks like this:

Public Class KeyWordObject
Private LocalSearches As String
Private AdvertiserCompetition As String
Private NumberOfWords As String

Public Sub New()
    ' Leave fields empty. 
End Sub

Public Sub New(ByVal LS As String, ByVal AC As String, ByVal NW As String)
    LocalSearches = LS
    AdvertiserCompetition = AC
    NumberOfWords = NW
End Sub

Public Property LocalSearchAmount As String
    Get
        Return LocalSearches
    End Get
    Set(ByVal value As String)
        LocalSearches = value
    End Set
End Property

Public Property AdvertiserCompetitionAmount As String
    Get
        Return AdvertiserCompetition
    End Get
    Set(ByVal value As String)
        AdvertiserCompetition = value
    End Set
End Property

Public Property WordCount As String
    Get
        Return NumberOfWords
    End Get
    Set(ByVal value As String)
        NumberOfWords = value
    End Set
End Property

End Class

I am trying parse a CSV file with the following Headers like this:

Ad group    Keyword     Avg. Monthly Searches (exact match only)    Competition    Suggested bid    Impr. share Organic impr. share

I would like to get the data from the Keyword, Avg. Monthly Searches, And competition columns and place them into an object. What would be the best way to parse this data out?

CSVHelper would allow that pretty easily. Tell it to ignore the first line, then set up a Map to tell it which csv cols map to which properties with some being ignored. The return is an `IEnumerable`, if you want a list of `KeyWordObject` items, add `.ToList()` and you are done. — Ňɏssa Pøngjǣrdenlarp, Jul 17 '16 at 22:19
It is not clear if your objective is to count/sum the lines with the same keywords or if you want to create an object of your class for every line in the file. Could you explain better? — Steve, Jul 17 '16 at 22:20
Hi sorry for not being clear. I wanted to create an object for every row in the file only getting the data from certain columns. — Zach Johnson, Jul 17 '16 at 22:24
less code with OleDb http://stackoverflow.com/questions/6813607/parsing-csv-using-oledb-using-c-sharp — Slai, Jul 18 '16 at 00:01

Zach Johnson · Accepted Answer · 2016-07-18T00:53:50.947

I ended up going with CSVHelper and this is the solution that worked for me. Thanks for the other suggestions.

     Dim TEMPKEYWORDPATH = Directory.GetCurrentDirectory + "/" + "KeywordsTemp" + ".csv"
Public Function CSVtoObject(CsvPath As String)
    Dim csv = New CsvReader(File.OpenText(TEMPKEYWORDPATH))
    csv.Configuration.RegisterClassMap(Of MyCustomObjectMap)()
    csv.Configuration.WillThrowOnMissingField = False
    csv.Configuration.Delimiter = vbTab

    Dim ListOfKeywords = csv.GetRecords(Of KeyWordObject).ToList()
    Return ListOfKeywords
End Function

Public NotInheritable Class MyCustomObjectMap
    Inherits CsvClassMap(Of KeyWordObject)
    Public Sub New()
        Map(Function(m) m.Keyword).Index(1)
        Map(Function(m) m.LocalSearchAmount).Index(3)
        Map(Function(m) m.AdvertiserCompetitionAmount).Index(4)
        Map(Function(m) m.WordCount).Ignore()
    End Sub
End Class

Best method For Sorting a tab delimited CSV into an object?

1 Answers1