Solved

How to fix this Bulk Insert using a csv file

Posted on 2011-02-23
3
2,308 Views
Last Modified: 2012-05-11
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
Comment
Question by:zberg007
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 37

Expert Comment

by:Neil Russell
ID: 34963658
Your fields dont match! Count the fields in your table design and count the fields in your spreadsheet example. they mismatch
0
 
LVL 37

Accepted Solution

by:
Neil Russell earned 500 total points
ID: 34963671
You have an extra column in the .xls
FILTER AGE
0
 

Author Comment

by:zberg007
ID: 34963877
You're exactly right! Thank you, I can't believe I missed that one. Too long of nights, but thanks again!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question