Solved

Unexpected EOF error when trying to run BCP

Posted on 2011-09-22
6
801 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
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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 500 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

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…

679 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