jeudi 30 avril 2015

Bulk Insert CSV File Using Format File

I am trying to insert a CSV file into a SQL database using Bulk Insert and a Format File but I am having some trouble. I have been using the following tutorial.

http://ift.tt/1KwB6hk

When I run the script I get this error I think it has something to do with the format of the Format File.

Msg 4863, Level 16, State 1, Line 3
Bulk load data conversion error (truncation) for row 2, column 3 (Ref).
Msg 7399, Level 16, State 1, Line 3
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 3
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Table Format

[Id]       INT           NULL,
[Name]     NVARCHAR (50) NULL,
[Townland] NVARCHAR (100) NULL,
[Ref]      INT           NULL,
[Lat]      FLOAT (53)    NULL,
[Lng]      FLOAT (53)    NULL

TSQL

use HOM
go
bulk insert HouseInfo
from 'c:\HOM\Houses_of_Mayo_02_02_2015.txt'
with
(
firstrow = 2,
fieldterminator = ',',
FORMATFILE = 'c:\HOM\Format_File.fmt'
)
select *
from HouseInfo

Format File

10.0
6
1   SQLINT      0   50  ","    2    Id          ""  
2   SQLNCHAR    0   50  ","    3    Name        ""
3   SQLNCHAR    0   100 ","    4    Townland    ""
4   SQLINT      0   50  ","    5    Ref         ""
5   SQLFLT8     0   53  ","    6    Lat         ""
6   SQLFLT8     0   53  "\n"   7    Lng         ""

CSV File

Id,Name,Townland,Ref,Lat,Lng
A1,Aasleagh Lodge,Srahatloe,1,53.613873,-9.668301
A2,Aasleagh Rectory,Srahatloe,2,53.612478,-9.668033
A3,Abbey Lodge,Carrowkeel (ED Croaghpatrick),3,53.785667,-9.638768
A4,Abbeytown House,Abbeytown,4,54.107701,-9.318194

Aucun commentaire:

Enregistrer un commentaire