January 28, 2011 at 1:20 pm
I'm having some problem with using the BULK INSERT command on a fixed width file. The fixed width file is well formatted, but I can't figure out the issue. I've been searching the interwebs for a solution and have tried different things, but without luck. I feel like I'm missing something pretty simple.
Table definition:
CREATE TABLE [dbo].[MY_TABLE](
[CTM_NBR] [varchar](12) NOT NULL,
[STE_CDE] [varchar](3) NULL,
[CUN_TYP] [varchar](3) NULL,
[ZIP_CDE] [varchar](9) NULL,
[REC_STA] [varchar](1) NULL,
[DTE_ADD] [datetime] NULL,
[UPD_DTE] [datetime] NULL,
[UPD_USR] [varchar](6) NULL,
[SHP_VIAR] [varchar](3) NULL,
[SEP_SHP] [varchar](1) NULL,
[BKO_FLG] [varchar](1) NULL,
[BKO_XPD] [smallint] NULL,
[PO_REQD] [varchar](1) NULL,
[NBR_CPY] [smallint] NULL,
[WRH_NBR] [varchar](3) NULL,
[BAL_HOW] [varchar](1) NULL,
[STM_FRQ] [tinyint] NULL,
[CRX_MAX] [decimal](24, 6) NULL,
[CRX_USR] [varchar](6) NULL,
[CRX_DTE] [datetime] NULL,
[SHP_EXCL] [varchar](1) NULL,
[FIN_CHG] [varchar](1) NULL,
[COL_PPD] [varchar](1) NULL,
[BAS_EXM] [varchar](1) NULL,
[EXM_FLG] [varchar](1) NULL,
[EXM_RSN] [varchar](6) NULL,
[EXM_IDN] [varchar](25) NULL,
[SLM_NBR] [varchar](3) NULL,
[SPC_ORD] [varchar](1) NULL,
[SPC_INV] [varchar](1) NULL,
[SOC_SEC] [varchar](9) NULL,
[CTM_TYP] [varchar](8) NULL,
[RLT_TYP] [varchar](1) NULL,
[SIC_CDE] [varchar](8) NULL,
[CLS_TRD] [varchar](3) NULL,
[PROMO] [varchar](1) NULL,
[TRM_IDN] [varchar](1) NULL,
[SPL_MSG] [varchar](30) NULL,
[DKT_NBR] [varchar](12) NULL,
[WEB_ACC] [varchar](1) NULL,
[DMO_DTA] [varchar](32) NULL,
[DMO_DTA2] [varchar](32) NULL,
[DMO_DTA3] [varchar](32) NULL,
[CTM_ADR] [varchar](8) NULL,
[SEX_CDE] [varchar](1) NULL,
[PIN_CODE] [varchar](4) NULL,
[CTM_SRC] [varchar](8) NULL,
[BIL_CUR] [varchar](4) NULL,
[CTM_PRCT] [varchar](8) NULL,
[CTM_DSCT] [varchar](8) NULL,
[DISC_PCT] [decimal](6, 3) NULL,
[LIST_ID] [varchar](8) NULL,
[NBR_PMO] [int] NULL,
[RSP_DTE] [datetime] NULL,
[CTM_CRE] [varchar](12) NULL,
[PRV_UPD] [datetime] NULL,
[CNS_SHP] [varchar](1) NULL,
[CNS_DAY] [varchar](3) NULL,
[CNS_QTY] [varchar](4) NULL,
[PP_XPD] [smallint] NULL,
[SAL_LVL] [varchar](1) NULL,
[ACP_ID] [varchar](10) NULL,
[BKO_FIL] [varchar](1) NULL,
[PROF_ID] [varchar](12) NULL,
[RND_CSE] [varchar](1) NULL,
[LANG_CDE] [varchar](5) NULL,
[DUP_POC] [varchar](1) NULL,
[CTM_URL] [varchar](120) NULL,
[RING_ID] [varchar](10) NULL,
[BKO_WDAY] [smallint] NULL,
[BIR_DTE] [datetime] NULL,
[PTL_BKO] [varchar](1) NULL,
[CBLK_FLG] [varchar](1) NULL,
[CBLK_NBR] [varchar](8) NULL,
[UPG_TAG] [varchar](1) NULL,
[FIL_002] [varchar](2) NULL
)
Format file:
10.0
76
1 SQLCHAR 0 12 "" 1 CTM_NBR ""
2 SQLCHAR 0 3 "" 2 STE_CDE ""
3 SQLCHAR 0 3 "" 3 CUN_TYP ""
4 SQLCHAR 0 9 "" 4 ZIP_CDE ""
5 SQLCHAR 0 1 "" 5 REC_STA ""
6 SQLCHAR 0 8 "" 6 DTE_ADD ""
7 SQLCHAR 0 8 "" 7 UPD_DTE ""
8 SQLCHAR 0 6 "" 8 UPD_USR ""
9 SQLCHAR 0 3 "" 9 SHP_VIAR ""
10 SQLCHAR 0 1 "" 10 SEP_SHP ""
11 SQLCHAR 0 1 "" 11 BKO_FLG ""
12 SQLCHAR 0 2 "" 12 BKO_XPD ""
13 SQLCHAR 0 1 "" 13 PO_REQD ""
14 SQLCHAR 0 2 "" 14 NBR_CPY ""
15 SQLCHAR 0 3 "" 15 WRH_NBR ""
16 SQLCHAR 0 1 "" 16 BAL_HOW ""
17 SQLCHAR 0 1 "" 17 STM_FRQ ""
18 SQLCHAR 0 24 "" 18 CRX_MAX ""
19 SQLCHAR 0 6 "" 19 CRX_USR ""
20 SQLCHAR 0 8 "" 20 CRX_DTE ""
21 SQLCHAR 0 1 "" 21 SHP_EXCL ""
22 SQLCHAR 0 1 "" 22 FIN_CHG ""
23 SQLCHAR 0 1 "" 23 COL_PPD ""
24 SQLCHAR 0 1 "" 24 BAS_EXM ""
25 SQLCHAR 0 1 "" 25 EXM_FLG ""
26 SQLCHAR 0 6 "" 26 EXM_RSN ""
27 SQLCHAR 0 25 "" 27 EXM_IDN ""
28 SQLCHAR 0 3 "" 28 SLM_NBR ""
29 SQLCHAR 0 1 "" 29 SPC_ORD ""
30 SQLCHAR 0 1 "" 30 SPC_INV ""
31 SQLCHAR 0 9 "" 31 SOC_SEC ""
32 SQLCHAR 0 8 "" 32 CTM_TYP ""
33 SQLCHAR 0 1 "" 33 RLT_TYP ""
34 SQLCHAR 0 8 "" 34 SIC_CDE ""
35 SQLCHAR 0 3 "" 35 CLS_TRD ""
36 SQLCHAR 0 1 "" 36 PROMO ""
37 SQLCHAR 0 1 "" 37 TRM_IDN ""
38 SQLCHAR 0 30 "" 38 SPL_MSG ""
39 SQLCHAR 0 12 "" 39 DKT_NBR ""
40 SQLCHAR 0 1 "" 40 WEB_ACC ""
41 SQLCHAR 0 32 "" 41 DMO_DTA ""
42 SQLCHAR 0 32 "" 42 DMO_DTA2 ""
43 SQLCHAR 0 32 "" 43 DMO_DTA3 ""
44 SQLCHAR 0 8 "" 44 CTM_ADR ""
45 SQLCHAR 0 1 "" 45 SEX_CDE ""
46 SQLCHAR 0 4 "" 46 PIN_CODE ""
47 SQLCHAR 0 8 "" 47 CTM_SRC ""
48 SQLCHAR 0 4 "" 48 BIL_CUR ""
49 SQLCHAR 0 8 "" 49 CTM_PRCT ""
50 SQLCHAR 0 8 "" 50 CTM_DSCT ""
51 SQLCHAR 0 6 "" 51 DISC_PCT ""
52 SQLCHAR 0 8 "" 52 LIST_ID ""
53 SQLCHAR 0 6 "" 53 NBR_PMO ""
54 SQLCHAR 0 8 "" 54 RSP_DTE ""
55 SQLCHAR 0 12 "" 55 CTM_CRE ""
56 SQLCHAR 0 8 "" 56 PRV_UPD ""
57 SQLCHAR 0 1 "" 57 CNS_SHP ""
58 SQLCHAR 0 3 "" 58 CNS_DAY ""
59 SQLCHAR 0 4 "" 59 CNS_QTY ""
60 SQLCHAR 0 2 "" 60 PP_XPD ""
61 SQLCHAR 0 1 "" 61 SAL_LVL ""
62 SQLCHAR 0 10 "" 62 ACP_ID ""
63 SQLCHAR 0 1 "" 63 BKO_FIL ""
64 SQLCHAR 0 12 "" 64 PROF_ID ""
65 SQLCHAR 0 1 "" 65 RND_CSE ""
66 SQLCHAR 0 5 "" 66 LANG_CDE ""
67 SQLCHAR 0 1 "" 67 DUP_POC ""
68 SQLCHAR 0 120 "" 68 CTM_URL ""
69 SQLCHAR 0 10 "" 69 RING_ID ""
70 SQLCHAR 0 3 "" 70 BKO_WDAY ""
71 SQLCHAR 0 8 "" 71 BIR_DTE ""
72 SQLCHAR 0 1 "" 72 PTL_BKO ""
73 SQLCHAR 0 1 "" 73 CBLK_FLG ""
74 SQLCHAR 0 8 "" 74 CBLK_NBR ""
75 SQLCHAR 0 1 "" 75 UPG_TAG ""
76 SQLCHAR 0 2 "\r" 76 FIL_002 ""
Error Message:
Msg 4863, Level 16, State 1, Line 8
Bulk load data conversion error (truncation) for row 1, column 76 (FIL_002).
Thanks in advance!
--MM
February 3, 2011 at 7:05 am
Your formatfile specifies \r as rowterminator for the last column.
Make sure that the datafile and formatfile uses the same terminator.
You could try to change the formatfile to "\ n" or "\r\ n" and see if it works better...
( "\ n" should be without the space between, but I couldn't figure out any other way than putting a space in between
in order to not have the form strip it out as a controlchar when written together...)
/Kenneth
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply