September 13, 2016 at 2:56 am
HI , I want to insert rows in a table from csv file,
this is my csv file:
"I_CODE",LEGAL_NAME,"TRADING_NAME","COUNTRY","CURRENCY","LANGUAGE","STATUS","BANK_NAME","IRRS","BANK_GUARANTEE_DATE","BANK_GUARANTEE_AMOUNT"
"1001",TV,"Sony","ALLEMANGNE","USD","ENG","9","BANKNAME1","98","19-jan-78","98USD"
and this is my format file :
10.0
12
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 COUNTRYSQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 3 "\",\"" 5 CURRENCYSQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 3 "\",\"" 6 LANGUAGESQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 1 "\",\"" 7 STATUSSQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 64 "\",\"" 8 BANK_NAMESQL_Latin1_General_CP1_CI_AS
10 SQLINT 0 4 "\",\"" 9 IRRS""
11 SQLDATE 0 4 "\",\"" 10 BANK_GUARANTEE_DATE""
12 SQLCHAR 0 50 "\r" 11 BANK_GUARANTEE_AMOUNT SQL_Latin1_General_CP1_CI_AS
SQLDATE NOT WORKING,
my input date = 19-jan-78 but I want it 1978-01-19 is there a way to do it ?
ps : When I put just SQLINT (and remove the date), my value "98" become "14393" in the table
this is my code :
BULK INSERT [StagingBG]
FROM '...\TEST\test1.csv'
WITH
(
FIRSTROW = 2,
FORMATFILE='...\TEST\test1.fmt'
);
what am I missing in my configuration ?
help!
September 13, 2016 at 5:30 am
I don't think SQLDATE is a valid type for BULK INSERT. See https://msdn.microsoft.com/en-us/library/ms187833.aspx.
The way I use BULK INSERT is to first import the data into a staging table. From there, I perform whatever validation (and cleaning) that needs to be done and then insert into my real table. I'm not sure where you are in the world, but your date format might cause you problems. Try changing the date format in the text file to YYYY-MM-DD.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply