efficient way to add csv to database

Question

Hello

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

Answer 1

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)
    connection.Open()

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

                command.ExecuteNonQuery()

                command.Parameters.Clear()
            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.

Answer 2

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

https://learn.microsoft.com/en-us/sql/connect/oledb/features/performing-bulk-copy-operations?view=sql-server-ver16