CODEWITHSUNDEEP
sqlsql-server
user2119912
user2119912

Reputation: 101

SQL Server 2005 - Bulk Insert failing

I have a txt file that contains 1600 rows and 82 columns of comma delineated data that I am trying to import into a table. I get the following error on every row on the very last field:

Msg 4864, Level 16, State 1, Line 1 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 81 (DB252D20C8).

The import statement is

BULK
INSERT  [ENERGY].[dbo].[READINGS1]
       from 'c:\readings2.txt'
with
(
DATAFILETYPE='widechar',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

The table structure is as follows, the top and bottom of the script:

USE [ENERGY]
GO
/****** Object:  Table [dbo].[READINGS1]    Script Date: 05/13/2013 20:00:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[READINGS1](
[DateAndTime] [datetime] NOT NULL,
[DB240D4C7] [float] NULL,
[DB240D8C7] [float] NULL,
[DB240D12C7] [float] NULL,
[DB240D16C7] [float] NULL, 



[DB252D12C8] [float] NULL,
[DB252D16C8] [float] NULL,
[DB252D20C8] [float] NULL,
 CONSTRAINT [READINGS1DataTimeStamp] PRIMARY KEY CLUSTERED 
(
[DateAndTime] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

The text file is as follows:

2013-02-19 00:00:00.000,6,945,1886,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,22,2040,6299,0,0,6,567,1248,0,0,251,8859,8655,0,0,10,316,1786,0,0,7,180,1206,0,0,1,16,56,0,0,368,18953,36949,0,0,NULL,NULL
2013-02-19 01:00:00.000,6,147,1886,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,22,1516,6299,0,0,3,115,1248,0,0,250,5077,8655,0,0,9,219,1786,0,0,5,147,1206,0,0,1,15,56,0,0,362,8907,36949,0,0,NULL,NULL

Upvotes: 3

Views: 1574

Answers (2)

user2119912
user2119912

Reputation: 101

It turns out that there were too many fields in the input text file for the table.

Upvotes: 0

Zane
Zane

Reputation: 4169

Alright so what you need to do is alter your statement so that after the end of the file you use KEEPNULLS. This informs SQL server that you wish to keep your null values. Currently it's trying to convert NULL as a string into your FLOAT COLUMN. Alter your statment to look like this.

BULK
INSERT  [ENERGY].[dbo].[READINGS1]
       from 'c:\readings2.txt'
with
(
DATAFILETYPE='widechar',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
KEEPNULLS
)
GO

There is an article on BOL about this. .

Otherwise you can always build a Integration Services package to handle this. That is an easy fast way to import information from flat file sources.

Upvotes: 1

Related Questions