CSV to SQL Server

[teqmem]

Hello,

How do you load from a CSV file to SQL Server using VB.Net?

Thank you.

 

[AlexCuse]

AFAIK, you need to look at this as two steps.

1. Get your .csv into a data table
2. Use SqlBulkCopy class to load to SQL Server

I wrote this in C# and did my best to change to VB. By playing around with this (console app) you ought to be able to figure out the rest (requires a file called c:\test.csv):

Imports System.Data.SqlClient
Imports System.Data.OleDb

Module Module1

    Sub Main()
        'set up ole connection for CSV file, fill data table
        Dim oConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended Properties=""text;HDR=Yes;FMT=Delimited"";")
        Dim oAr As New OleDbDataAdapter("select * from Test.csv", oConn)

        'create/fill data table
        Dim mDt As New DataTable("test")
        oAr.Fill(mDt)

        'create SqlConnection (used to capture counts & by BulkCopy object)
        Dim sConn As New SqlConnection("Data Source=[b]myServer[/b];Initial Catalog=TEST;User Id=[b]myID[/b];Password=[b]myPass[/b];")
        sConn.Open()

        'command to capture counts
        Dim sCom As New SqlCommand("select count(*) from CSVTest", sConn)

        'capture initial rowcount
        Dim sRows As Integer = Convert.ToInt32(sCom.ExecuteScalar())

        'create bulk copy object, connection (in constructor) & destination table
        Dim sBC As New SqlBulkCopy(sConn)
        sBC.DestinationTableName = "CSVTest"

        'write Data Table to Destination
        sBC.WriteToServer(mDt)

        'capture final rowcount
        Dim eRows As Integer = Convert.ToInt32(sCom.ExecuteScalar())

        'display lines copied
        Console.WriteLine("Success: " + (eRows - sRows).ToString() + " rows copied")

        'clean up
        oConn.Dispose()
        oAr.Dispose()
        sConn.Dispose()
        sCom.Dispose()
        mDt.Dispose()
        sBC.Close()
        sBC = Nothing

        'keep console open
        Console.ReadLine()

    End Sub

End Module

Let me konw if you have any problems/questions.

Hope this helps,

Alex



[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom