Import date from CSV to sql table with sqlbulkcopy

[ChrisE]

Hi GideonE,

try this just to get it to a Datagridview first..
set a Filter if tha Data is to large

Imports System.Data.OleDb


'Dim SQL As String = "Select * From [Adressen.csv] Where AD_Land Like 'D%';"
'Dim SQL As String = "Select * From [Adressen.csv] Where AD_Name1 Like  '" & SearchBox & "%';"
'Dim SearchBox As String
'SearchBox = TextBox1.Text


Public Class Form5

 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim _tb As New DataTable

        Me.DataGridView1.DataSource = _tb

        Dim SrcDir As String = "C:\"
        Dim sConn As String = String.Join(";", New String() { _
           "Provider=Microsoft.Jet.OLEDB.4.0", _
           "Data Source=" & SrcDir, _
           "Extended Properties=""Text; HDR=Yes; FMT=Delimited"""})

      
        Dim SQL As String = "Select * From [Adressen.csv];"

        Using Cn As New OleDbConnection(sConn), ta As New OleDbDataAdapter(SQL, Cn)
            Cn.Open()
            ta.Fill(_tb)
        End Using
    End Sub

'Copy to new Table, I know this is Access you have to change to SQL DB
 Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim sSql As String
        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\NWind.mdb")
        'copy CSV to new Table in Access
        'the CSV/Location is = C:\Adressen.csv
        sSql = "Select * Into CSVImport From [Adressen.csv] IN 'c:\' 'Text;'"
        con.Open()
        ExecuteSQL(con, sSql)
        con.Close()
        con = Nothing
    End Sub
    Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                    ByVal sSQL As String, _
                                    Optional ByRef ErrMessage As String = Nothing, _
                                    Optional ByVal TransAction As  _
                                    OleDb.OleDbTransaction = Nothing) As Integer
        ErrMessage = Nothing
        Try
            Dim Result As Integer = 0
            Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                Result = Cmd.ExecuteNonQuery
            End Using
            Return Result
        Catch ex As Exception
            ErrMessage = ex.Message
            Return 0
        End Try
    End Function


End Class

regards
Chris