Importing csv file to SQL Server Using VB.Net

I have a csv file that is selected by a user. The csv file contains one column (phone number). I need to import this file into a table on sql server using vb.net.

I've tried the following code:

Code:

---

Dim objConn As nsSqlClient.SqlConnection
Dim ds As New DataSet
Dim m_strConnection As String = "server=NINEL-D246655F1;Initial Catalog=TimeControl;user id=timeuser;password=timeuser;"

objConn = New nsSqlClient.SqlConnection
objConn.ConnectionString = m_strConnection
objConn.Open()

' Make sure the .CSV file exists:
If File.Exists(sLeadFile) Then
    Try
      ' ------ Load the data from the .CSV file: --------
      Dim strSQL As String
      strSQL = "SELECT F1 " & _
      "INTO " & projectfile & ".dbo.[List_staging] " & _
      "FROM [Text;HDR=NO;DATABASE=" & sLeadFile & "]"

    Dim objCommand As nsSqlClient.SqlCommand
    objCommand = New nsSqlClient.SqlCommand(strSQL, objConn)

    objCommand.CommandText = strSQL
    objCommand.ExecuteNonQuery()
    objConn.Close()
    Catch ex As Exception
          sResultText = sResultText & "<BR>" & ex.Message
    End Try
End If

---

I'm getting an error: "Invalid object name 'C:\VoicenetSQL\project\tampa\Politic\JH2468\at1008.CSV'."

This is the file the user selected.
What am I doing wrong?

Any help would be greatly appreciated,
Ninel

Is that the path on the client or the server?

Keep in mind, when you tell the server to import something fom a location; it checks itself for that path. It doesn't assume you mean C:\ on the client.

I am doing this on my local machine.

I got it working with the following code with one exception:

Code:

---

Private Function ImportLeadFile(ByVal projectfile As String, ByVal sLeadFile As String, ByVal DATABASE As String) As Boolean
        Dim objConn As nsSqlClient.SqlConnection
        Dim ds As New DataSet
        Dim m_strConnection As String = "server=NINEL-D246655F1;Initial Catalog=TimeControl;user id=timeuser;password=timeuser;"

        objConn = New nsSqlClient.SqlConnection
        objConn.ConnectionString = m_strConnection
        objConn.Open()

        ' Make sure the .CSV file exists:
        If File.Exists(sLeadFile) Then
            Try
                ' ------ Load the data from the .CSV file: ----------
                Dim strSQL As String
                strSQL = "Select * " & _
                      " INTO " & DATABASE & ".dbo.[List_staging] " & _
                      "FROM  OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)}; DEFAULTDIR=C:\VoicenetSQL\project\tampa\Politic\" & projectfile & "; Extensions=CSV; HDR=No;','SELECT * FROM at1008.csv') "

                Dim objCommand As nsSqlClient.SqlCommand
                objCommand = New nsSqlClient.SqlCommand(strSQL, objConn)

                objCommand.CommandText = strSQL
                objCommand.ExecuteNonQuery()
                objConn.Close()
            Catch ex As Exception
                sResultText = sResultText & "<BR>" & ex.Message
            End Try
        End If
End Function

---

The csv file contains one column of phone numbers with no heading. When the file gets imported into a table the first phone number record is created as a column name. How can I get around this?

Can anyone help me with this?

How about doing a bulk insert on a text file that has the following datad:
"7187893456"
"8139987654"

Will this work?

Code:

---

BULK INSERT dbo.list_staging FROM '" & sLeadFile & "' " & _
"WITH ( FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n' )"

---