efficient way to add csv to database

StewartBW 505 Reputation points


I read a csv text file line by line and use oldb sql command insert into table to add each line's fields to the table's row.

But it takes a very long time, I wonder if there's faster achievements, please advise the most efficient and fastest way to go?

Thanks all

An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,383 questions
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,605 questions
0 comments No comments
{count} vote

Accepted answer
  1. Jiachen Li-MSFT 27,166 Reputation points Microsoft Vendor

    Hi @StewartBW ,

    You can construct SQL insert statements to insert multiple rows at once using the OleDbCommand and OleDbParameter classes.

    Dim sqlInsert As String = "INSERT INTO YourTable (Column1, Column2, Column3) VALUES (?, ?, ?)"
    Using connection As New OleDbConnection(connectionString)
        Using command As New OleDbCommand(sqlInsert, connection)
            Using reader As New StreamReader("your_csv_file.csv")
                While Not reader.EndOfStream
                    Dim line As String = reader.ReadLine()
                    Dim fields As String() = line.Split(","c) ' Assuming CSV fields are comma-separated
                    command.Parameters.AddWithValue("@param1", fields(0))
                    command.Parameters.AddWithValue("@param2", fields(1))
                    command.Parameters.AddWithValue("@param3", fields(2))
                End While
            End Using
        End Using
    End Using

    Best Regards.

    Jiachen Li

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 41,411 Reputation points

    If the target database is MS SQL Server, then you can use Bulk Operation, see