Bulk load: An unexpected end of file was encountered in the data file.

Hi,

In the second example, why have you got
FIELDTERMINATOR = '"",""',

My first guess would have been
FIELDTERMINATOR = '","',
if you needed the quotes. Do you need the quotes in field terminator string?

Cheers
  David

I was following instructions that I received on line, which said to use FIELDTERMINATOR = '"",""',

I've tried is with FIELDTERMINATOR = '","',
but it gives me this error when I do it that way
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (ID).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 1 (ID).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 1 (ID).

Hi,

The error that you now have is an indication that the bulk insert is now working.
Try the code below.

Once the data is inserted, it is easy to then insert/update from the staging table to a permenant table, and convert the chars to ints and dates - using isnumber and isdate to check first.

Cheers
  David

DROP TABLE CSVTest
GO
GO
CREATE TABLE CSVTest
(ID varchar( 10 ),
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate varchar( 20 )
)
GO
 
BULK INSERT CSVTest
FROM 'c:\csvtest2.txt'
WITH
(
FIRSTROW = 2, 
FIELDTERMINATOR = '"",""',
ROWTERMINATOR = '\n'
)
GO
 
SELECT *
FROM CSVTest
GO

Select allOpen in new window

If I use your code, I get:
Bulk load: An unexpected end of file was encountered in the data file.

If I take off a set of quote in FIELDTERMINATOR = '","',
Then i get:
"2      Meggie      Smith      19790122"
"3      Robert      Smith      20071101"
"4      Alex      Smith      20040202"

Which is not correct either

I got the type mismatch when I used FIELDTERMINATOR = '","',   (which is not correct)

When I did/do that I get this result.

"2      Meggie      Smith      19790122"
"3      Robert      Smith      20071101"
"4      Alex      Smith      20040202"

David,

I appreciate your help, but I think we're headed down two different roads.  I have found a convertor program that will massage any type of mixed CSV file that I can come up with into a TAB separated file, which imports in just fine, so I'm going to abandon trying to get this problem solved. Send me one final response and I'll attach the points to it for all of your help.

I should have mentioned that the results shown above are four separate columns, with the first colum containing a quote before the number and the last column containing a quote after the date.  Just out of curiousity, are you running this identical code on your side with success, because I keep getting errors.  That being said, I ran your code and these were the results.

With the file c:\csvtest2.csv
"ID","First","Last","DOB"
"1","James","Smith","19750101"
"2","Meggie","Smith","19790122"
"3","Robert","Smith","20071101"
"4","Alex","Smith","20040202"

and the code below, I get the error:
(4 row(s) affected)

(4 row(s) affected)
Msg 4145, Level 15, State 1, Line 11
An expression of non-boolean type specified in a context where a condition is expected, near 'then'.

DROP TABLE CSVTest
GO
GO
CREATE TABLE CSVTest
(ID Nchar( 10 ),
FirstName nCHAR(40),
LastName nCHAR(40),
BirthDate nchar( 20 )
)
GO
 
BULK INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIRSTROW = 2, 
FIELDTERMINATOR = '","',
ROWTERMINATOR = '\n'
)
GO
 
SELECT *
FROM CSVTest
GO
 
IF EXISTS 
	(
  	SELECT *
	FROM sys.tables
	JOIN sys.schemas
		ON sys.tables.schema_id = sys.schemas.schema_id
   	WHERE 
		sys.schemas.name = N'dbo'
	 	AND sys.tables.name = N'CSVTestPermenant'
	)
  	DROP TABLE dbo.CSVTestPermenant
GO
 
CREATE TABLE dbo.CSVTestPermenant
	(
	ID int,
	FirstName VARCHAR(40),
	LastName VARCHAR(40),
	BirthDate datetime
	)
GO
 
insert dbo.CSVTestPermenant
	(
	ID
	, FirstName
	, LastName
	, BirthDate
	)
	select
		case
			when isnumeric( c.ID ) then convert( int, c.ID )
			else 0
		end as ID
		, c.FirstName
		, c.LastName
		, case
			when isdate( c.BirthDate ) then convert( datetime, c.BirthDate, 112 )
			else null
		end as BirthDate
	from dbo.CSVTest c
 
select 
	ID
	, FirstName
	, LastName
	, BirthDate
from dbo.CSVTestPermenant cp

Select allOpen in new window

If I remove the Case statements from the code above and just leave the convert statements insert dbo.CSVTestPermenant, then I get the error:

(4 row(s) affected)

(4 row(s) affected)
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the nvarchar value '"1        ' to data type int.

Anyway, I don't want you to work on the problem any more, just wanted to give you the results of the last coding suggestion that you sent me.

Thanks again!!
Gary

insert dbo.CSVTestPermenant
	(
	ID
	, FirstName
	, LastName
	, BirthDate
	)
	select
		convert( int, c.ID )
		, c.FirstName
		, c.LastName
		,convert( datetime, c.BirthDate, 112 )
	from dbo.CSVTest c

Select allOpen in new window

Sorry typo above. Should read:

If I remove the Case statements from the code above and just leave the convert statements, then I get the error:

Hi,

Sorry, the select should when isdate( c.Birthdate ) = 1 then ...

Cheers
  David

	select
		case
			when isnumeric( c.ID ) = 1 then convert( int, c.ID )
			else 0
		end as ID
		, c.FirstName
		, c.LastName
		, case
			when isdate( c.BirthDate ) = 1 then convert( datetime, c.BirthDate, 112 )
			else null
		end as BirthDate

Select allOpen in new window

See attached picture of results.   Still not there, but I appreciate all of your time and effort.

Thanks, Gary.
results.bmp

You had a typo on the second part.  I had to change len(c.ID) to len(C.Birthdate) to get the date working, but I cannot get the ID to work to save my life, it just keeps coming up 0.  

If I do the following query

select
ID,
len (ID),
right (ID, 2),
right (ID, 1)
from CSVTest

then i get results in pic below
results2.bmp

If I query:

select
ID,
len (ID),
right (ID,2),
right (ID,1),
left (ID,2),
left (ID,1)
from CSVTest

then I get
results3.bmp

David - You are the man!

That did it.

Thanks for helping out a beginner!
Gary

Hi Gary,

You're welcome.

Cheers
  David