James Cochrane
asked on
Unexpected EOF error when trying to run BCP
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_Gene ral_CP1_CI _AS"/>
<FIELD ID="2" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="50" COLLATION="SQL_Latin1_Gene ral_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.
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_Gene
<FIELD ID="2" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="50" COLLATION="SQL_Latin1_Gene
</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_
2 SQLCHAR 0 50 "\r\n" 2 address SQL_Latin1_General_CP1_CI_
Thanks for any help you can provide on this.
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
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
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
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
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. That's perfect!
ASKER
Mike,Ohio
John,Arizona