2

When I am inserting bulk data to a table from a CSV file, it is not working, showing error lie :

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 9

Column 9 value in csv file is null..

How do I take care of this?

James
  • 2,136
  • 3
  • 23
  • 42
  • Provide your sample data and temporary table schema using in bulk insert than only it can trace. – commit Apr 25 '13 at 11:01

1 Answers1

2

From this amount of information I'd say the target table's particular field is defined as "NOT NULL". To workaround the issue you have to:

a) modify csv-->add value to field(s) where they have null

b) modify target table by setting affected fields 'nullable':ALTER TABLE [tblName] ALTER COLUMN [nulColName] [varType such as INT] NULL
In case you go for this solution and want to turn back table's status alter it again:
UPDATE [tblName] SET [nulColName]=-1000 WHERE [nulColName] IS NULL to avoid alter errors, then ALTER TABLE [tblName] ALTER COLUMN [nulColName] [varType such as INT] NOT NULL

c) pretty much like 'b' option but a bit more professional and faster: create a temp table based on target table but allowing nulls to any and all fields, then update temp table's null records after csv import with a "default value" and copy the data to the target table


If I'm right about the issue and there's no option to revise csv I'd go for option 'c'