locked
Example of importing CSV file into SQLServer using SQLDataAdapter. RRS feed

  • Question

  • Does anyone know of a way to use SQLDataAdapter to import the contents of a CSV (comma delimited file) into an SQLServer table?

     

    Does anyone know of a way to do this with a XLS (Excel 7.0 or 8.0 or 9.0) file using SQLDataAdapter?

     

    Any simple examples would be greatly appreciated.

     

    Thank you.

    Friday, May 9, 2008 4:42 AM

Answers

  • HI Jlroper,

    You need to use ODBC data provider to read the csv file. Or you can directly use the StreamReader and StreamWriter classes to read the value one by one.

    Imports System.Data.Odbc

    Public Class Form1

        Dim obj_oledb_da As System.Data.Odbc.OdbcDataAdapter

        Public Function ConnectCSV(ByVal filetable As String) As DataSet

            Dim dataSet As New DataSet

            Try

                Dim selectConnection As New OdbcConnection(("Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & "c:\" & ";Extensions=asc,csv,tab,txt;Persist Security Info=False").Trim)

                selectConnection.Open()

                Dim selectCommandText As String = "select * from [" & filetable & "]"

                Me.obj_oledb_da = New OdbcDataAdapter(selectCommandText, selectConnection)

                Me.obj_oledb_da.Fill(dataSet, "Stocks")

                selectConnection.Close()

            Catch exception As Exception

                MessageBox.Show(exception.Message)

            End Try

            Return dataSet

        End Function

        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

            Me.DataGridView1.DataSource = Me.ConnectCSV("Book1.csv")

            Me.DataGridView1.DataMember = "Stocks"

        End Sub

    End Class


    If you need to import the data from Excel worksheet, you can use OleDb provider to implement this.

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

            Dim _filename As String = "C:\new.xls"

            Dim _conn As String

            _conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _filename & ";" & "Extended Properties=Excel 8.0;"

            Dim _connection As OleDbConnection = New OleDbConnection(_conn)

            Dim da As OleDbDataAdapter = New OleDbDataAdapter()

            Dim _command As OleDbCommand = New OleDbCommand()

            _command.Connection = _connection

            _command.CommandText = "SELECT * FROM [Sheet1$]"

            da.SelectCommand = _command

            Try

                da.Fill(ds1, "sheet1")

                MessageBox.Show("The import is complete!")

                Me.DataGridView1.DataSource = ds1

                Me.DataGridView1.DataMember = "sheet1"

            Catch e1 As Exception

                MessageBox.Show("Import Failed, correct Column name in the sheet!")

            End Try

    End Sub


    Best regards,
    Riquel
    Tuesday, May 13, 2008 11:00 AM
    Moderator