Data conversion error using bulk insert with bcp generated format file

microsoft.public.sqlserver.tools

8 Nov 2005 4:43 PM

damezumari

I am using vb.net 2003 and windows 2000.

This is my format file created with the command prompt:
bcp pubs..eaccount format nul -fauthors.bcp -c -t"," -T -S

8.0
12
1       SQLCHAR       0       50      ","                       1
account            SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       50      ","                       2
service            SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       24      ","                       3
datef              ""
4       SQLCHAR       0       24      ","                       4
timef              ""
5       SQLCHAR       0       50      ","                       5
timebandtype       SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR       0       50      ","                       6
destination        SQL_Latin1_General_CP1_CI_AS
7       SQLCHAR       0       50      ","                       7
callednumber       SQL_Latin1_General_CP1_CI_AS
8       SQLCHAR       0       7       ","                       8
actualduration     ""
9       SQLCHAR       0       30      ","                       9
airtimecharge      ""
10      SQLCHAR       0       30      ","                       10
tollcharge         ""
11      SQLCHAR       0       30      ","                       11
amt                ""
12      SQLCHAR       0       50      "\r\n"                    12
remarks            SQL_Latin1_General_CP1_CI_AS

This is the table, pubs.eaccount, I want to copy data into:

CREATE TABLE [eaccount] (
    [account] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [service] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [datef] [smalldatetime] NULL ,
    [timef] [smalldatetime] NULL ,
    [timebandtype] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
    [destination] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
    [callednumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
    [actualduration] [smallint] NULL ,
    [airtimecharge] [smallmoney] NULL ,
    [tollcharge] [smallmoney] NULL ,
    [amt] [smallmoney] NULL ,
    [remarks] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

The data is sitting in a csv file. Here are the first few rows:

Account,Service,Date,Time,Time Band Type,Destination,Called
Number,Actual Duration,Airtime Charge,Toll Charge,Amt,Remarks,

"840002951400","945-1581","2005-Jul-2","02:22:14","WN","NEW
YORK","12123847180","13043","0.0","75.65","75.65",
"840002951400","945-1581","2005-Jul-2","06:00:08","WN","NEW
YORK","12123847180","12672","0.0","73.5","73.5",

This is my code:

        Try

            Dim openFileDialog1 As New OpenFileDialog
            openFileDialog1.InitialDirectory = _
              "C:\Documents and Settings\JNordgreen\My
Documents\eaccount vs equitrack\eaccount downloads\CALL DETAILS\"
            openFileDialog1.Filter = "csv files (*.csv)|*.csv"
            openFileDialog1.Title = "Select the file for a month and
click Open"

            If openFileDialog1.ShowDialog() = DialogResult.OK Then
                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 = openFileDialog1.FileName()
                Dim formatfile As String = "C:\Documents and
Settings\JNordgreen\My Documents\Visual Studio
Projects\equitrackandeaccount\eaccount.fmt"
                cmd.CommandText = "BULK INSERT " & table & _
                  " FROM '" & file & "' WITH (FIRSTROW = 3, FORMATFILE
='" & _
                  formatfile & "', FIELDTERMINATOR = ',', ROWTERMINATOR
= '\n')"
                cmd.Connection = cn
                cmd.ExecuteNonQuery()

                cn.Close()
                cn = Nothing
            End If
        Catch
            MsgBox(Err.Description)
        End Try

This is the error message:

Bulk insert data conversion error (type mismatch) for row 3, column 3
(datef).
Bulk insert data conversion error (type mismatch) for row 4, column 3
(datef).
....

What am I doing wrong?

Regards,

Jan Nordgreen