Solved

Unexpected EOF error when trying to run BCP

Posted on 2011-09-22
6
806 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

717 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