CODEWITHSUNDEEP
sql-serversql-server-2012bulkinsertbcp
Kaja
Kaja

Reputation: 3057

Some Errors while trying to BULK-INSERT

I have a text file, which contains these infos:

1,Blabla,Bla,Munich, Entwickler
2,artin,Rosner,Chemnitz,Entwickler
3,Sebastian, Leuoth, Berlin ,Manager
4,bizbiz, bazbaz, Kulalampur, Student

I would like to use BULK insert, with a format file. I have generetade my format file with bcp as follows:

C:\Windows\system32>bcp test.dbo.DimensioList format nul -Uuser1 -Puser1 -n
-f "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Listfor
mat.fmt"

If I run this code:

INSERT INTO DimensioList
SELECT * 
FROM
OPENROWSET(BULK 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\List.txt', FORMATFILE='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Listformat.fmt') as e

I am getting these errors:

   Msg 4866, Level 16, State 1, Line 2
    The bulk load failed. The column is too long in the data file for row 1, column 2. Verify that the field terminator and row terminator are specified correctly.  
    Msg 7399, Level 16, State 1, Line 2 
    The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error. 
    Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

by the way, my format file look like this:

11.0
5
1       SQLINT              0       4       ""   1     EID                  ""
2       SQLCHAR             2       20      ""   2     Vorname              Latin1_General_CI_AS
3       SQLNCHAR            2       40      ""   3     Nachname             Latin1_General_CI_AS
4       SQLCHAR             2       20      ""   4     Stadt                Latin1_General_CI_AS
5       SQLCHAR             2       20      ""   5     Beruf                Latin1_General_CI_AS

what do you mean, what should I do to solve this problem?

Upvotes: 0

Views: 286

Answers (1)

user4622594
user4622594

Reputation:

i prefer using XML-Format Files, you could try a format file like this: (this is for files where your fields have fixed length - you can easily adopt this to variable length with terminator...)

<?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="CharFixed" LENGTH="4"/>
    <FIELD ID="2" xsi:type="CharFixed" LENGTH="20"/>
    <FIELD ID="3" xsi:type="CharFixed" LENGTH="40"/>
    <FIELD ID="4" xsi:type="CharFixed" LENGTH="20"/>
    <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n"
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="EID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Vorname" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Nachname" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Stadt" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="5" NAME="Beruf" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Save this as Listformat.xml and you this instead in of Listformat.fmt

Upvotes: 1

Related Questions