• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2469
  • Last Modified:

How to fix this Bulk Insert using a csv file

I keep getting this error:
Msg 4863, Level 16, State 1, Procedure LoadALSTable, Line 9
Bulk load data conversion error (truncation) for row 2, column 30 (SCHED_NO).
Msg 4863, Level 16, State 1, Procedure LoadALSTable, Line 9
Bulk load data conversion error (truncation) for row 3, column 30 (SCHED_NO).
Msg 4863, Level 16, State 1, Procedure LoadALSTable, Line 9
Bulk load data conversion error (truncation) for row 4, column 30 (SCHED_NO).
Msg 4863, Level 16, State 1, Procedure LoadALSTable, Line 9
Bulk load data conversion error (truncation) for row 5, column 30 (SCHED_NO).
Msg 4863, Level 16, State 1, Procedure LoadALSTable, Line 9
Bulk load data conversion error (truncation) for row 6, column 30 (SCHED_NO).
Msg 4863, Level 16, State 1, Procedure LoadALSTable, Line 9
Bulk load data conversion error (truncation) for row 7, column 30 (SCHED_NO).
Msg 4863, Level 16, State 1, Procedure LoadALSTable, Line 9
Bulk load data conversion error (truncation) for row 8, column 30 (SCHED_NO).
Msg 4863, Level 16, State 1, Procedure LoadALSTable, Line 9
Bulk load data conversion error (truncation) for row 9, column 30 (SCHED_NO).
Msg 4863, Level 16, State 1, Procedure LoadALSTable, Line 9
Bulk load data conversion error (truncation) for row 10, column 30 (SCHED_NO).

Open in new window


Which appears that the data values from my .csv file are too long for the SCHED_NO field. However, I have it set to varchar(50) and the length of data is at 33 characters, so no idea what's causing this.

My steps are, 1) Create the table using this script:
USE [ETL]
GO
/****** Object:  Table [dbo].[ALSImportTable]    Script Date: 02/23/2011 10:10:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ALSImportTable](
	[Company Name] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Company Number] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Unit ID] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Unit Make] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Unit Model] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Unit Serial Number] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Unit Site] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Compartment Name] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Compartment Make] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Compartment Model] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Compartment Serial Number] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Compartment Type] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[UIN No] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Fluid Manufacturer] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Fluid Type] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Fluid Grade] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Laboratory Number] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SIF No] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Date Sampled] [varchar](19) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Date Received] [varchar](19) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Date Processed] [varchar](19) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Unit Age] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Comp Age] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Fluid Age] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Oil Changed Y/N] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Oil Added] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Overall Severity] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Problem Code] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Diagnostic Comments] [varchar](384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SCHED_NO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Fe] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Fe_Sev] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
	
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Open in new window



2) Create this stored procedure here:
USE [ETL]
GO
/****** Object:  StoredProcedure [dbo].[LoadALSTable]    Script Date: 02/23/2011 12:02:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[LoadALSTable]
AS

    SET NOCOUNT ON 

    TRUNCATE TABLE dbo.ALSImportTable

    BULK INSERT  dbo.ALSImportTable FROM 'C:\Sites\LT3-Example.csv'
    WITH (FIELDTERMINATOR = ',', FIRSTROW = 2)
         

    RETURN (@@ERROR)

Open in new window


3) Using this as the Example.csv file:
 LT3-Example.csv

When I execute the stored procedure, it gives me the error at the beginning, which for row 2, it is erroring on this value "Diesel Engine - LubeTrak - Yellow" , which is the value for the SCHED_NO and so on, with the same error on the remaining 8 rows of sample data.
0
zberg007
Asked:
zberg007
  • 2
1 Solution
 
Neil RussellTechnical Development LeadCommented:
Your fields dont match! Count the fields in your table design and count the fields in your spreadsheet example. they mismatch
0
 
Neil RussellTechnical Development LeadCommented:
You have an extra column in the .xls
FILTER AGE
0
 
zberg007Author Commented:
You're exactly right! Thank you, I can't believe I missed that one. Too long of nights, but thanks again!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now