Import csv file into either access or sql server database using vb 2008.net

call OPENRAWSET query of SQL Server into VB.Net

query should like:

SELECT * FROM  
OPENROWSET  ('MSDASQL', 'Driver={Microsoft Text  Driver (*.txt; *.csv)};DBQ=D:\mmc;', 'SELECT * from  test.csv');

for more help:

http://sqlserver2000.databases.aspfaq.com/how-do-i-load-text-or-csv-file-data-into-sql-server.html

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/5869d247-f0a0-4224-80b3-ff2e414be402

I would highly recommend investigating SSIS as a solution.  This is the sort of thing that it is designed for and it will handle the import much more quickly with much less effort.

since you have specifically asked for calling procedure from vb.net, I suggested above given way but I too agree with above response that SSIS could be better candidate for the task you are looking for. I have few tutorials of SSIS on my blog, in case you are interested. http://www.sqlhub.com/

The database is currently SQL Server 2000.  Was SSIS available for that version?  I believe we are upgrading later this year to SQL Server 2008, but I can't wait until then.  I know nothing about SSIS, so forgive me my questions...

Since it is SS2000, you would need to use DTS . . . which is sort of like SSIS (only different ;-).  
It shouldn't be a hard package (which is what both DTS and SSIS "programs" are called) to set up.  I'll have to play around a bit to set up a CSV file and then review DTS to set up a sample package before I can provide the steps to do it.
The thing is that either DTS or SSIS are designed for this sort of thing and they operate much faster than writing an application that, essentially does a Row By Agonizing Row processing of the data to do the inserts. ;-)

Thank you.  I would like to do this as efficiently as possible.  I do have emoreau's code working (Thank you emoreau!) The csv file is not very large (less than 1000 records and there is only 10 fields in each record) so this is definitely doable.  

But I would be interested in learning more about SSIS and DTS because this just the first of several programs I will be writing.  Trying to migrate most of our Access program to VB.NET with SQL Server as the backend.  

See vb.net example below.

Compile it to an ImportCSV.exe, then call it from command line...supplying the filename as an argument.
(ImportCSV.exe -f:C:\yourfile.csv)

Imports Microsoft.VisualBasic.FileIO
Imports System.Data.SqlClient
Module Module1

    Private SqlServerName As String = "yourservername"
    Private SqlDB As String = "yoursqldbname"
    Private SqlTable As String = "dbo.yoursqltablename"
    Private SqlUserName As String = "yourusername"
    Private SqlPass As String = "yourpass"
    Private FileNameArgSwitch As String = "-f:"
    Sub Main()

        Dim FileNameArg As String = My.Application.CommandLineArgs.ToString.Replace("-f:", "")

        Dim filename As String = FileNameArg
        Dim fields As String()
        Dim delimiter As String = ","
'Read the file
        Using parser As New TextFieldParser(filename)
            parser.SetDelimiters(delimiter)
            While Not parser.EndOfData
                ' Read in the fields for the current line
                fields = parser.ReadFields()


                SendToSQL(fields)

            End While
        End Using


    End Sub

    Public Sub SendToSQL(ByVal Fields As String())
        '* This all assumes that all the destination fields are Text, and that the input file has the same number of fields as the destination table

        'Convert all input fields into a single string
        Dim AllValues As String = "'"
        Dim i As Integer = 0
        For i = 0 To Fields.Length - 1

            AllValues = Fields.GetValue(i).ToString & "','"

        Next

        AllValues = AllValues & "'"


        'Create a connection to SQL
        Dim conn As New SqlConnection()
        conn.ConnectionString = "Data Source=" & SqlServerName & ";Initial Catalog=" & SqlDB & ";Persist Security Info=True;User ID=" & SqlUserName & ";Password=" & SqlPass

        'Prepare the INSERT statement
        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "INSERT INTO " & SqlTable & _
                            "VALUES (" & AllValues & ")"
        cmd.Connection = conn


        Try
            conn.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub
End Module

Select allOpen in new window

Sorry.....correction.

For i = 0 To Fields.Length - 1

            AllValues = AllValues & Fields.GetValue(i).ToString & "','"

        Next

Thank you -  I actually used emoreau's code for this and tested 8080 Driver for future needs.  Thank you!