Results 1 to 2 of 2

Thread: Using CsvHelper to speed up inserting from .csv to database

  1. #1

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

    Using CsvHelper to speed up inserting from .csv to database

    I am currently using Mysqlbulkloader to insert data from a .csv file to a Mysql Database. The .csv file has just over 1 mil lines and take just under two minutes to complete.

    I want to use CSVhelper (https://joshclose.github.io/CsvHelper/) to see if that will speed up things.

    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
    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

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

    Re: Using CsvHelper to speed up inserting from .csv to database

    As suggested, a DataTable doesn't implement the IEnumerable interface. You might try its Rows property, or you can call the AsEnumerable extension method.

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