Bulk insert problem when the last field in the last row is null

  • Thread starter Thread starter damezumari
  • Start date Start date
D

damezumari

I have a csv file I want to insert into an existing sql-server table
using a vb.net windows form. This is my code:

Try

Dim cn As New SqlConnection
cn.ConnectionString = _
"workstation id=""F2RS-D477"";packet size=4096;user
id=sa;data source=""F2RS-D477"";persist security info=False;initial
catalog=pubs"
cn.Open()

Dim cmd As New SqlCommand
Dim table As String = "pubs.dbo.eaccount"
Dim file As String = "C:\test2.csv"
cmd.CommandText = "BULK INSERT " & table & _
" FROM '" + file + "' WITH (FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n')"
cmd.Connection = cn
cmd.ExecuteNonQuery()

cn.Close()
cn = Nothing

Catch
MsgBox(Err.Description)
End Try

It does not work if the value of the last field in the last row is
empty. If it is " " it works.

How do I get the code to always work?
 
¤ I have a csv file I want to insert into an existing sql-server table
¤ using a vb.net windows form. This is my code:
¤
¤ Try
¤
¤ Dim cn As New SqlConnection
¤ cn.ConnectionString = _
¤ "workstation id=""F2RS-D477"";packet size=4096;user
¤ id=sa;data source=""F2RS-D477"";persist security info=False;initial
¤ catalog=pubs"
¤ cn.Open()
¤
¤ Dim cmd As New SqlCommand
¤ Dim table As String = "pubs.dbo.eaccount"
¤ Dim file As String = "C:\test2.csv"
¤ cmd.CommandText = "BULK INSERT " & table & _
¤ " FROM '" + file + "' WITH (FIELDTERMINATOR = ',',
¤ ROWTERMINATOR = '\n')"
¤ cmd.Connection = cn
¤ cmd.ExecuteNonQuery()
¤
¤ cn.Close()
¤ cn = Nothing
¤
¤ Catch
¤ MsgBox(Err.Description)
¤ End Try
¤
¤ It does not work if the value of the last field in the last row is
¤ empty. If it is " " it works.
¤
¤ How do I get the code to always work?

What happens? Do you get an error? Is the column skipped?


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top