July 16, 2007 at 7:05 am
the problem for that. If I open the file in excel.
EMPLSTS lile 00, 0F make the program confused.
| EMPLSTS |
| 0F |
| 0F |
| 00 |
| 00 |
| 06 |
| 0F |
| 20 |
| 00 |
| 0F |
| 0P |
table defination:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[License_LR_Only ]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[License_LR_Only ]
GO
CREATE TABLE [dbo].[License_LR_Only ] (
[DEPTID] [nvarchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[DEPTID1] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[EMPLID] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[NAME] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[EMPLSTS] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[JOBCODE] [nvarchar] (55) COLLATE Latin1_General_CI_AS NULL ,
[DESCR] [nvarchar] (55) COLLATE Latin1_General_CI_AS NULL ,
[SSN] [nvarchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[HIRE_DT] [nvarchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[REHIRE_DT] [nvarchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[TERMINATION_DT] [nvarchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[LICENSE_CERTIF_CD] [nvarchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[LICENSE_NBR] [nvarchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[DT_ISSUED] [nvarchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[EXPIRATN_DT] [nvarchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[INVALIDATION_DT] [nvarchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[ISSUED_BY] [nvarchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[LICENSE_VERIFIED] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[RENEWAL] [nvarchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[STATE] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[SUSPENDED] [nvarchar] (25) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
BULK INSERT License_LR_Only
FROM 'C:\Working Folder\Maria\License_LR_Only.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
error:
Bulk insert data conversion error (truncation) for row 1, column 5 (EMPLSTS).
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 2, column 5 (EMPLSTS).
Server: Msg 4865, Level 16, State 1, Line 1
Could not bulk insert because the maximum number of errors (10) was exceeded.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
below is first two row of the cvs file open in note pad.
DEPTID,DEPTID,EMPLID,NAME,EMPLSTS,JOBCODE,DESCR,SSN,HIRE_DT,REHIRE_DT,TERMINATION_DT,LICENSE_CERTIF_CD,LICENSE_NBR,DT_ISSUED,EXPIRATN_DT,INVALIDATION_DT,ISSUED_BY,LICENSE_VERIFIED,RENEWAL,STATE,SUSPENDED
109090,Corp Pastoral Care,059519,"EDE,Phyllis A",0F,470,Chaplain,122228079,08/02/2004,,,,,,,,,,,,
326001,LTC - L,050849,"ABC,Karen H",0F,0739,"Dir., Nursing Lyette",201111115,01/31/1994,,,RN,RN3EE,10/14/1993,10/31/2008,,PA DOS,Y,N,PA,N
326001,LTC - L,050989,"EFCn,Margaret",00,1166,"Supervisor, House/LTC",201478887,04/26/1994,,,RN,RNEE,,10/31/2007,,PA DOS,Y,N,PA,N
326001,LTC - L,101624,"SEDE,Lisa",00,1166,"Supervisor, House/LTC",178888990,01/30/2006,,,RN,RNEL,08/17/1994,10/31/2008,,,Y,N,PA,N
July 16, 2007 at 8:58 am
You have problem in 11 and 12 rows. Can you show this rows?
July 16, 2007 at 9:10 am
The problem is that there are commas in the NAME column - it is splitting it into two columns and in the first row cannot insert PHYLISS A into the EMPLSTS column.
You might be able to resolve this using a format file or you do this using a Transform Data Task in DTS (if this is applicable).
J
July 17, 2007 at 12:00 am
In row 1 you are trying to insert value 'EMPLSTS' into varchar(5) field.
You need to use FIRSTROW = 2.
_____________
Code for TallyGenerator
July 17, 2007 at 5:32 am
I changed to
BULK INSERT License_LR_Only
FROM 'C:\Working Folder\Maria\License_LR_Only.csv'
WITH
( FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
still the same error. Thx.
July 17, 2007 at 6:22 am
I still think it is failing on the comma in the NAME column.
Any chance you can do this in DTS using a Transform Data Task?
J
July 17, 2007 at 6:36 am
Tried Transform Data Task before. I do not remeber what reason I do not like it. It seems right now. DTS work fine. Thx.
July 17, 2007 at 6:39 am
The Transform Data Task is slower than bulk insert - I think it does row by row processing rather than set processing. If the input file is small (a few thousand rows) then there is not a huge performance overhead. If the input file is millions of rows then there will be a performance headache and we may need to reconsider.
J
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply