?
Solved

Unexpected EOF error when trying to run BCP

Posted on 2011-09-22
6
Medium Priority
?
819 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:James Cochrane
  • 3
  • 3
6 Comments
 
LVL 1

Author Comment

by:James Cochrane
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:James Cochrane
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
 
LVL 1

Author Closing Comment

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

864 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