efficient way to add csv to database

StewartBW 505 Reputation points
2024-04-26T08:05:21.28+00:00

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

C#
C#
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
VB
VB
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
    2024-04-29T07:03:43.11+00:00

    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.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 41,411 Reputation points
    2024-04-26T08:20:32.44+00:00

    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