Bulk insert data conversion error (type mismatch)

Hi,

I'm trying to import data from a CSV file into an existing table but having problems. The current error I can't get past is "Bulk insert data conversion error (type mismatch)".

Here is the table format:

CREATE TABLE [dbo].[tblServiceContact] (
      [SuperRegion] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
      [Region] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
      [PostCode] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
      [Service] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
      [OpeningHours] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
      [Rank] [int] NOT NULL ,
      [LocalTelephone] [varchar] (25) COLLATE Latin1_General_CI_AS NOT NULL ,
      [InternationalTelephone] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
      [EmailAddress] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
      [IsService] [bit] NOT NULL ,
      [SegmentName] [varchar] (20) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

Here is my BULK INSERT script:

----------
BULK INSERT tblServiceContact
    FROM 'C:\SQLBackups\ServiceContacts.csv'
    WITH
    (
      FORMATFILE='C:\SQLBackups\ServiceContacts.fmt',
      FIRSTROW = 2,
      DATAFILETYPE = 'char'
    )
----------

Here is my format file:

----------
8.0
10
1       SQLCHAR       0       50      ","      1      SuperRegion                ""
2       SQLCHAR       0       50      ","      2      Region                     ""
3       SQLCHAR       0       8       ","      3      PostCode                   ""
4       SQLCHAR       0       50      ","      4      Service                    ""
5       SQLCHAR       0       50      ",\""      5      OpeningHours               ""
6       SQLCHAR       0       4       "\","      6      Rank                       ""
7       SQLCHAR       0       25      ","      7      LocalTelephone             ""
8       SQLCHAR       0       25      ","      8      InternationalTelephone     ""
9       SQLCHAR            0       1       ","      9      IsService                  ""
10      SQLCHAR       0       20      "\r\n"      10      SegmentName                ""
----------

Here is some sample data (ANSI):

----------
SuperRegion,Region,PostCode,Service,OpeningHours,Rank,LocalTelephone,InternationalTelephone,IsService,SegmentName
,East Anglia/North Home Counties,AL1,Complaints,"Mon-Fri, 9am - 5pm",2,0800 123 4567,,1,
,East Anglia/North Home Counties,AL10,Complaints,"Mon-Fri, 9am - 5pm",2,0800 123 4567,,1,
,East Anglia/North Home Counties,AL2,Complaints,"Mon-Fri, 9am - 5pm",2,0800 123 4567,,1,
,East Anglia/North Home Counties,AL3,Complaints,"Mon-Fri, 9am - 5pm",2,0800 123 4567,,1,
,East Anglia/North Home Counties,AL4,Complaints,"Mon-Fri, 9am - 5pm",2,0800 123 4567,,1,
,East Anglia/North Home Counties,AL5,Complaints,"Mon-Fri, 9am - 5pm",2,0800 123 4567,,1,
----------

I've tried using SQLINT and SQLBIT types in the format file, but have read that for a CSV import I should set everything to be SQLCHAR.

Restrictions:
- I can't use the bcp command line
- I can't change the table structure

Thanks for your help!Start Free Trial