Solved

Unexpected EOF error when trying to run BCP

Posted on 2011-09-22
6
798 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 39

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 39

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 39

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

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 …
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 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