Importing csv file to SQL Server Using VB.Net

[ninel]

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:

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

 

[vis781]

I always do it line by line. So this would be one option. Some will say that repeated round trips to the database is bad practice, and they are right but it always works for me.

Dim FSysObj as object
Dim FileToRead as Object
Dim LineOfFile as String
Dim MySqlCom As SqlCommand
Dim StrSql As String
 
 
YourConnection.Open()
 
FSysObj= CreateObject("Scripting.FileSystemObject")
FileToRead = FSysObj.OpenTextFile('Your File To Read')
 
Do While Not FileToRead.AtEndOfStream
LineOfFile = FSysObj.readline
 
StrSql= "INSERT INTO YourTableName (YourColumnName) VALUES ('" & LineOfFile & "')"
MySQLCom= New SqlCommand(StrSQL, YourConnection)
Try
MySQLCom.ExecuteNonQuery()
Catch ex as exception
End Try
End If
Loop

However your problem would appear to be with sLeadFile. What is it?