I have a 50MB text file that has about 150,000 records in
it. Each field is ended with a "|". I ran this command:

BULK INSERT db.myfile FROM 'C:\textfile.txt'
WITH (
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)


I received this error:

Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row
8015, column 36 (Year).

Here is line 8015 of the text file:

Line 8015 - 543|"Ronald"|"McDonald"|"2 State
Rd"||"Someplace"|"CA"|"90210"||"somewhere"|"(999 ) 999-
9999"|"(999) 999-
9999"|"TEST"|"TEST2"|"XX"|1/1/2004|"QQ"|"X"|2/28/2002|2/28
/2002||2/28/2002|||0.75|"P"|"NTEW00"|"Water
Class "||"D"|"234"|4/17/2005|"Water Workshop"|"Test
User"|"My company"|"2003"


Here is the file structure:

CREATE TABLE [dbo].[mytable] (
[Student] [varchar] (255) NULL ,
[First_Name] [varchar] (255) NULL ,
[Last_Name] [varchar] (255) NULL ,
[Address1] [varchar] (255) NULL ,
[Address2] [varchar] (255) NULL ,
[City] [varchar] (255) NULL ,
[State] [varchar] (255) NULL ,
[Zip] [varchar] (255) NULL ,
[Home_Ph] [varchar] (255) NULL ,
[Employer] [varchar] (255) NULL ,
[Bus_Ph] [varchar] (255) NULL ,
[FAX] [varchar] (255) NULL ,
[PIN] [varchar] (255) NULL ,
[Course_Code] [varchar] (255) NULL ,
[Course_Type] [varchar] (255) NULL ,
[Date] [varchar] (255) NULL ,
[Session] [varchar] (255) NULL ,
[Paid] [varchar] (255) NULL ,
[Start_Date] [varchar] (255) NULL ,
[Sched_End_Date] [varchar] (255) NULL ,
[Extension_Date] [varchar] (255) NULL ,
[Final_Date] [varchar] (255) NULL ,
[Final_Grade] [varchar] (255) NULL ,
[Course_Grade] [varchar] (255) NULL ,
[Contact_Hrs] [varchar] (255) NULL ,
[Attend_status] [varchar] (255) NULL ,
[Provider] [varchar] (255) NULL ,
[Session_Description] [varchar] (255) NULL ,
[Date_Range] [varchar] (255) NULL ,
[OEPACertType] [varchar] (255) NULL ,
[OEPA_Crs] [varchar] (255) NULL ,
[OEPA_Exp_Dt] [varchar] (255) NULL ,
[Course_Description] [varchar] (255) NULL ,
[Contact] [varchar] (255) NULL ,
[Provider_Name] [varchar] (255) NULL ,
[Year] [varchar] (255) NULL
) ON [PRIMARY]

A couple of questions. First, how do you tell from the
text file what the end-of-line character is? Second, how
can you eliminate the double quotes from the textfile?
Finally, can you see why I am receiving the error message
from above?

Thank you for any help or insight you provide.

SJ

1. Get a hex editor
2. Double quotes can be eliminated using a format file.
http://tinyurl.com/24oqg
3. Not sure why the error happens on this row. Your definistion looks
good. If the text file is good then there shouldn't be a problem.

Can you try importing the file using a DataPump and see if that fixes things
?


--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"SJ" <anonymous (AT) discussions (DOT) microsoft.com> wrote