Results 1 to 8 of 8

Thread: [RESOLVED] CSV Helper

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Resolved [RESOLVED] CSV Helper

    I have a VB.Net (Framework 4.7.2) application and a Mysql database. I use Mysql bulkloader to insert just over 1 million lines from .csv file. It takes about 1 minute 46 sec to load. I am trying to use CSV Helper to see if that would be faster (https://joshclose.github.io/CsvHelper/)

    Here is the code I use :

    Code:
    Using streamReader = File.OpenText(extractPath & GebruikHierdieCSV & ".csv")
                Using csvReader As New CsvReader(streamReader, CultureInfo.CurrentCulture)
                         Using dr = New CsvDataReader(csvReader)
                        Dim dt = New DataTable()
                        dt.Load(dr)
                    End Using
                End Using
            End Using
    However I get an error message : CsvHelper.BadDataException: You can ignore bad data by setting BadDataFound to null.

    If I add the following line just after Using csvReader As New CsvReader :

    Code:
    csvReader.Configuration.BadDataFound = null
    I get an error null is not declared. It may be inaccessible due to its protection level.
    I have tried DbNull with no luck.

    What am I missing?

  2. #2
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: CSV Helper

    Try, = nothing
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  3. #3
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: CSV Helper

    sepator is right in that you should assign the value to Nothing. To elaborate, the Visual Basic keyword for null is Nothing (documentation).
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Re: CSV Helper

    Thank you both. I have tried csvReader.Configuration.BadDataFound = Nothing

    I get the following error : Proprty BadDatafound is readonly. The author's remarked that
    The config is read only now. You need to pass it into the constructor.
    .

    He then refers to the following change log https://joshclose.github.io/CsvHelper/change-log where he states under version 23.0.0
    Removed the large CsvConfiguration constructor. The properties are now settable, so this isn't needed for VB.NET.
    .

    But I cannot figure out how to do that.

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: CSV Helper

    The CsvReader.Configuration property is type IReaderConfiguration and the IReaderConfiguration.BadDataFound property is indeed read-only, so you cannot set it the way you're trying to. What is not read-only is the CsvConfiguration.BadDataFound property, so you need to access the property via a reference of that type. You can either do that with a cast:
    vb.net Code:
    1. DirectCast(csvReader.Configuration, CsvConfiguration).BadDataFound = Nothing
    or you can create your own configuration first, set that property and then pass it into the reader:
    vb.net Code:
    1. Dim config As New CsvConfiguration(CultureInfo.CurrentCulture) With {.BadDataFound = Nothing}
    2.  
    3. Using streamReader = File.OpenText(extractPath & GebruikHierdieCSV & ".csv"),
    4.       csvReader As New CsvReader(streamReader, config),
    5.       dr = New CsvDataReader(csvReader)
    6.     Dim dt = New DataTable()
    7.  
    8.     dt.Load(dr)
    9. End Using
    Note that I have also removed your unnecessary nested Using blocks. You could also get rid of the extra declaration and move the object creation into the constructor argument:
    vb.net Code:
    1. Using streamReader = File.OpenText(extractPath & GebruikHierdieCSV & ".csv"),
    2.       csvReader As New CsvReader(streamReader, New CsvConfiguration(CultureInfo.CurrentCulture) With {.BadDataFound = Nothing}),
    3.       dr = New CsvDataReader(csvReader)
    4.     Dim dt = New DataTable()
    5.  
    6.     dt.Load(dr)
    7. End Using

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: CSV Helper

    BTW, you should also be using the Path.Combine method to construct a file/folder path from parts, not concatenating strings. Doing so means that you can never accidentally include too many or too few delimiters at the concatenation points:
    vb.net Code:
    1. Using streamReader = File.OpenText(Path.Combine(extractPath, GebruikHierdieCSV & ".csv")),

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Re: CSV Helper

    Thank you jmchilhinney. That worked. (I don't know whether I should put this on a new thread.)

    My plan is to use streamreader and put the data into a datable :
    Code:
     'First Streamreader :
            Using streamReader = File.OpenText(Path.Combine(extractPath, GebruikHierdieCSV & ".csv")),
                          csvReader As New CsvReader(streamReader, New CsvConfiguration(CultureInfo.CurrentCulture) With {.BadDataFound = Nothing, .HasHeaderRecord = True}),
                          dr = New CsvDataReader(csvReader)
    
    
                Dim dt2 = New DataTable()
    
                dt2.Load(dr)
    
    
            End Using

    You'll notice I have also added .HasHeaderRecord = True as a configuration. BTW thanx for the Path.Combine tip.


    Then use streamwriter to create a csv file :

    Code:
    'Second Streamwriter :
            Using writer = New StreamWriter("D:\Wingerdbou\LutzvilleProgram\InvoerDataFiles\EZY Wine\Vessel_Composition\CSVStreamwriterFile.csv")
                Using csv = New CsvWriter(writer, CultureInfo.InvariantCulture)
                    csv.WriteRecords(dt2)
                End Using
            End Using
    I get an error at csv.WriteRecords(dt2) : System.InvalidCastException: 'Unable to cast object of type 'System.Data.DataTable' to type 'System.Collections.IEnumerable'.'

    Also I cannot view the datatable(dt2) with table visualizer with a break?
    Maybe I should add the rows line by line - but surely that would slow things down as there are over 1 mil rows?

    Finally I want to use MySqlbulkload to insert into my database :

    Code:
    'Then MySqlbulkload :
            Dim bl As New MySqlBulkLoader(conn)
            bl.TableName = "ezycomposition_staging"
            bl.FieldTerminator = ","
            bl.LineTerminator = "\r\n"
            bl.FileName = "D:\Wingerdbou\LutzvilleProgram\InvoerDataFiles\EZY Wine\Vessel_Composition\CSVStreamwriterFile.csv"
            bl.NumberOfLinesToSkip = 1
            bl.FieldQuotationCharacter = """"
    
            conn.Open()
            Dim count As Integer = bl.Load()
            System.Threading.Thread.Sleep(500)
    Am I on the right track? Regards

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: CSV Helper

    Quote Originally Posted by GideonE View Post
    That worked. (I don't know whether I should put this on a new thread.)
    Yes you should. If the advice I provided fixed the problem you asked about then this issue is resolved and you should use the Thread Tools menu to mark this thread Resolved. If you have a new issue then you should create a new thread that contains ALL and ONLY the information relevant to that issue. One thread per topic and one topic per thread.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width