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

7 Nov 2005 5:09 PM

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?

7 Nov 2005 7:36 PM

Paul Clement

On 7 Nov 2005 09:09:11 -0800, "damezumari" <jannordgr***@gmail.com> wrote:

¤ 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)

7 Nov 2005 8:03 PM

damezumari

Sorry for not mentioning it.

I get an unexpected end of file error.