September 12, 2016 at 2:34 am
Hi I want to insert csv file in my Sql Server table, without quotes
I m using this command
BULK
INSERT [GFA_BG].[dbo].[StagingBG]
FROM '...\AIMS_20160709.csv'
WITH
(
FIELDTERMINATOR ='","',
ROWTERMINATOR ='',
FirstRow=2,
DATAFILETYPE = 'char',
FORMATFILE = 'AIMS_20160709.fmt',
ERRORFILE = '...\errorlog.log'
)
GO
this is the formatfile :
10.0
25
1 SQLCHAR 0 0 "\"" 0 FIRST_QUOTE SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 12 "\",\"" 1 I_CODE SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 64 "\",\"" 2 LEGAL_NAME SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 64 "\",\"" 3 TRADING_NAME SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 64 "\",\"" 4 COUNTRY SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 3 "\",\"" 5 CURRENCY SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 3 "\",\"" 6 LANGUAGE SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 1 "\",\"" 7 STATUS SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 64 "\",\"" 8 BANK_NAME SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 16 "\",\"" 9 BANK_GUARANTEE_AMOUNT SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 3 "\",\"" 10 BANK_GUARANTEE_CURRENCY SQL_Latin1_General_CP1_CI_AS
12 SQLDATE 0 3 "\",\"" 11 BANK_GUARANTEE_EXPIRY_DATE ""
13 SQLDATE 0 3 "\",\"" 12 ACCREDITATION_DATE ""
14 SQLCHAR 0 1 "\",\"" 13 CLASS_PAX_OR_CGO SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 2 "\",\"" 14 LOCATION_TYPE SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 12 "\",\"" 15 XREF SQL_Latin1_General_CP1_CI_AS
17 SQLINT 0 4 "\",\"" 16 IRRS ""
18 SQLCHAR 0 16 "\",\"" 17 TAX_CODE SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 2 "\",\"" 18 COUNTRY_CODE SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 64 "\",\"" 19 CITY SQL_Latin1_General_CP1_CI_AS
21 SQLINT 0 4 "\",\"" 20 DEF ""
22 SQLCHAR 0 1 "\",\"" 21 OWN_SHARE_CHANGE SQL_Latin1_General_CP1_CI_AS
23 SQLDATE 0 3 "\",\"" 22 OWN_SHARE_LAST_DATE ""
24 SQLCHAR 0 50 "\",\"" 23 CHO_CHI SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 0 50 "\"\r" 24 DEF_NONPAYMENT SQL_Latin1_General_CP1_CI_AS
and this is the .csv file
"I_CODE","LEGAL_NAME","TRADING_NAME","COUNTRY","CURRENCY","LANGUAGE","STATUS","BANK_NAME","BANK_GUARANTEE_AMOUNT","BANK_GUARANTEE_CURRENCY","BANK_GUARANTEE_EXPIRY_DATE","ACCREDITATION_DATE","CLASS_PAX_OR_CGO","LOCATION_TYPE","XREF","IRRS","TAX_CODE","CITY","ISO_CTRY_CODE","DEF","OWN/SHARE CHANGE","OWN/SHARE LAST DATE","CHO_CHI","DEF_NONPAYMENT"
"97500023","CARIBBEAN WORLD ","GOING PLACES","ANTIGUA AND BARBUDA","XCD","ENG",9,"",,"","","19-OCT-50","P","BR","98500010","0","","ST. JOHN'S","AG","0","","","",""
I have these message errors :
Msg 4863, Level 16, State 1, Line 2
Bulk load data conversion error (truncation) for row 2, column 7 (LANGUAGE).
Msg 4832, Level 16, State 1, Line 2
Bulk load: An unexpected end of file was encountered in the data file.
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)".
and the log file :
Row 2 File Offset 351 ErrorFile Offset 0 - HRESULT 0x80004005
please help!
September 12, 2016 at 7:16 am
Your format and data do not match
The position of CITY and COUNTRY_CODE is different
The last entry in the format file needs have \r in the terminator not \r
All the data needs to " delimited (even empty columns)
The following format will load the data you supplied
Note all the columns are SQLCHAR, do data conversion post load especially dates.
10.0
25
1 SQLCHAR 0 0 "\"" 0 FIRST_QUOTE SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 12 "\",\"" 1 I_CODE SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 64 "\",\"" 2 LEGAL_NAME SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 64 "\",\"" 3 TRADING_NAME SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 64 "\",\"" 4 COUNTRY SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 3 "\",\"" 5 CURRENCY SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 3 "\",\"" 6 LANGUAGE SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 1 "\",\"" 7 STATUS SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 64 "\",\"" 8 BANK_NAME SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 16 "\",\"" 9 BANK_GUARANTEE_AMOUNT SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 3 "\",\"" 10 BANK_GUARANTEE_CURRENCY SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 3 "\",\"" 11 BANK_GUARANTEE_EXPIRY_DATE ""
13 SQLCHAR 0 10 "\",\"" 12 ACCREDITATION_DATE ""
14 SQLCHAR 0 1 "\",\"" 13 CLASS_PAX_OR_CGO SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 2 "\",\"" 14 LOCATION_TYPE SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 12 "\",\"" 15 XREF SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 4 "\",\"" 16 IRRS ""
18 SQLCHAR 0 16 "\",\"" 17 TAX_CODE SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 64 "\",\"" 19 CITY SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 2 "\",\"" 18 COUNTRY_CODE SQL_Latin1_General_CP1_CI_AS
21 SQLCHAR 0 4 "\",\"" 20 DEF ""
22 SQLCHAR 0 1 "\",\"" 21 OWN_SHARE_CHANGE SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 0 3 "\",\"" 22 OWN_SHARE_LAST_DATE ""
24 SQLCHAR 0 50 "\",\"" 23 CHO_CHI SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 0 50 "\"\r" 24 DEF_NONPAYMENT SQL_Latin1_General_CP1_CI_AS
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply