?
Solved

Unexpected EOF error when trying to run BCP

Posted on 2011-09-22
6
Medium Priority
?
810 Views
Last Modified: 2013-11-05
When I run BCP at the command line, and it prompts me for the various parameters (such as storage type, prefix-length, etc.) the command works and imports the data (it's a csv file).   I even wrote out the format file when asked both in xml and fmt formats.  But when I try to run the BCP command using the format file I get that was created, I get the following error:

Unexpected EOF encountered in BCP data-file.

Here is the command:
bcp MyDb.dbo.Test in upload.csv -T -S Servername

Here are the format files:
XML:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="2" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="name" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="address" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>


FMT:
10.0
2
1       SQLCHAR             0       50      ","      1     name               SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       50      "\r\n"   2     address            SQL_Latin1_General_CP1_CI_AS

Thanks for any help you can provide on this.

0
Comment
Question by:techhound
[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
  • 3
  • 3
6 Comments
 

Author Comment

by:techhound
ID: 36581183
The upload.csv has the following data:

Mike,Ohio
John,Arizona
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36581407
If you need to import CSV file exclusively please use the following example - you nac use a permanenet table instead:



BULK INSERT #temp
FROM 'c:\test.csv'
WITH
(
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT * FROM #temp WHERE [Cust ID]  <> 'Cust id'
UNION
SELECT * FROM #temp WHERE [Cust ID]  IS NULL
GO
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36581444
If you need to import from EXCEL as well is all documented with examples at links below:

How to import data from Excel to SQL Server
http://support.microsoft.com/kb/321686

How to use Excel with SQL Server linked servers and distributed queries
http://support.microsoft.com/kb/306397

openrowset excel 2007
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/23e7b80d-c7ec-4875-bd3a-aa137fd95dfb
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

Author Comment

by:techhound
ID: 36582054
Thanks for the response.  But that only works within the SQL Server query.  I need to be able to run it from the command line.
0
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 36582233
You can use SQLCMD to run a command like:

BULK INSERT table_name
FROM 'c:\test.csv'
WITH
(
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n'


Just look for more detail in: "Using the sqlcmd Utility"
http://msdn.microsoft.com/en-us/library/ms180944.aspx
0
 

Author Closing Comment

by:techhound
ID: 36582600
Thanks.  That's perfect!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

752 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