BULK INSERT HELP!

Hello,
This is my table
CREATE TABLE [dbo].[TDE018](
[no_empl] [varchar](12) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL,
[no_dexp] [varchar](12) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL,
[no_doss_ir] [int] NOT NULL,
[no_even_ir] [smallint] NOT NULL,
[dat_even_orig_ir] [datetime] NULL,
[dat_even_ir] [datetime] NULL,
[cod_loi_repa] [varchar](2) COLLATE SQL_Latin1_General_CP850_CI_AI NULL,
[dat_inscr_decis_r] [datetime] NULL,
[cod_decis_admis_r] [varchar](3) COLLATE SQL_Latin1_General_CP850_CI_AI
NULL,
[cod_motif_deci_adm] [varchar](3) COLLATE SQL_Latin1_General_CP850_CI_AI
NULL,
[dat_presc_drt] [datetime] NULL,
[dat_cess_1er_emplo] [datetime] NULL,
[dat_consol] [datetime] NULL,
[dat_rtr_1er_emploi] [datetime] NULL,
[text_desc_diagn] [varchar](80) COLLATE SQL_Latin1_General_CP850_CI_AI
NULL,
[taux_pe_enga_ap] [float] NULL,
[dat_enrg_oper] [datetime] NULL
) ON [PRIMARY]
This is the command that I use
BULK INSERT TDE018 FROM 'C:\TDE018.csv' WITH (FORMATFILE = 'C:\TDE018.fmt',
KEEPNULLS, CODEPAGE ='1252')
This is my format file
8.0
17
1 SQLCHAR 2 12 ";" 1
no_empl SQL_Latin1_General_CP850_CI_AI
2 SQLCHAR 2 12 ";" 2
no_dexp SQL_Latin1_General_CP850_CI_AI
3 SQLINT 0 9 ";"
3 no_doss_ir ""
4 SQLSMALLINT 0 4 ";" 4
no_even_ir ""
5 SQLDATETIME 1 10 ";" 5
dat_even_orig_ir ""
6 SQLDATETIME 1 10 ";" 6
dat_even_ir ""
7 SQLCHAR 2 2 ";" 7
cod_loi_repa SQL_Latin1_General_CP850_CI_AI
8 SQLDATETIME 1 10 ";" 8
dat_inscr_decis_r ""
9 SQLCHAR 2 3 ";" 9
cod_decis_admis_r SQL_Latin1_General_CP850_CI_AI
10 SQLCHAR 2 3 ";" 10
cod_motif_deci_adm SQL_Latin1_General_CP850_CI_AI
11 SQLDATETIME 1 10 ";" 11
dat_presc_drt ""
12 SQLDATETIME 1 10 ";" 12
dat_cess_1er_emplo ""
13 SQLDATETIME 1 10 ";" 13
dat_consol ""
14 SQLDATETIME 1 10 ";\"" 14
dat_rtr_1er_emploi ""
15 SQLCHAR 2 70 "\";" 15
text_desc_diagn SQL_Latin1_General_CP850_CI_AI
16 SQLFLT8 1 6 ";" 16
taux_pe_enga_ap ""
17 SQLDATETIME 1 10 "\n" 17
dat_enrg_oper ""
This is an exemple of row in my data file
ENL83946781;73655112;110235140;900;1997-04-03;1997-04-03;LP;1997-04-25;ACC;REG;1998-04-03;1997-04-03;1997-04-22;1997-04-22;"a
laceration to the left hand";1.2;2001-01-01
I have this error and I don't know where to look because it is the first
time I use BULK INSERT.
This is the error
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.
The statement has been terminated.
Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1.
Make sure the field terminator and row terminator are specified correctly.
I need help please
Thank you
Marc R.
> This is an exemple of row in my data file
> ENL83946781;73655112;110235140;900;1997-04-03;1997-04-03;LP;1997-04-25;ACC;REG;1998-04-03;1997-04-03;1997-04-22;1997-04-22;"a
> laceration to the left hand";1.2;2001-01-01
This record is in character format and has no length prefixes. The format
file below describes the source file according this sample data. I also
increased the maximum field lengths to accommodate the target SQL data types
in case other records have longer values.
8.0
17
1 SQLCHAR 0 12 ";" 1 no_empl SQL_Latin1_General_CP850_CI_AI
2 SQLCHAR 0 12 ";" 2 no_dexp SQL_Latin1_General_CP850_CI_AI
3 SQLCHAR 0 9 ";" 3 no_doss_ir ""
4 SQLCHAR 0 4 ";" 4 no_even_ir ""
5 SQLCHAR 0 10 ";" 5 dat_even_orig_ir ""
6 SQLCHAR 0 10 ";" 6 dat_even_ir ""
7 SQLCHAR 0 2 ";" 7 cod_loi_repa SQL_Latin1_General_CP850_CI_AI
8 SQLCHAR 0 10 ";" 8 dat_inscr_decis_r ""
9 SQLCHAR 0 3 ";" 9 cod_decis_admis_r
SQL_Latin1_General_CP850_CI_AI
10 SQLCHAR 0 3 ";" 10 cod_motif_deci_adm
SQL_Latin1_General_CP850_CI_AI
11 SQLCHAR 0 10 ";" 11 dat_presc_drt ""
12 SQLCHAR 0 10 ";" 12 dat_cess_1er_emplo ""
13 SQLCHAR 0 10 ";" 13 dat_consol ""
14 SQLCHAR 0 10 ";\"" 14 dat_rtr_1er_emploi ""
15 SQLCHAR 0 70 "\";" 15 text_desc_diagn
SQL_Latin1_General_CP850_CI_AI
16 SQLCHAR 0 38 ";" 16 taux_pe_enga_ap ""
17 SQLCHAR 0 10 "\n" 17 dat_enrg_oper ""
Hope this helps.
Dan Guzman
SQL Server MVP
"Marc Robitaille" <marc.marie AT globetrotter.net.del> wrote in message
news:e18Mq8lUHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Hello,
> This is my table
> CREATE TABLE [dbo].[TDE018](
> [no_empl] [varchar](12) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL,
> [no_dexp] [varchar](12) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL,
> [no_doss_ir] [int] NOT NULL,
> [no_even_ir] [smallint] NOT NULL,
> [dat_even_orig_ir] [datetime] NULL,
> [dat_even_ir] [datetime] NULL,
> [cod_loi_repa] [varchar](2) COLLATE SQL_Latin1_General_CP850_CI_AI NULL,
> [dat_inscr_decis_r] [datetime] NULL,
> [cod_decis_admis_r] [varchar](3) COLLATE SQL_Latin1_General_CP850_CI_AI
> NULL,
> [cod_motif_deci_adm] [varchar](3) COLLATE SQL_Latin1_General_CP850_CI_AI
> NULL,
> [dat_presc_drt] [datetime] NULL,
> [dat_cess_1er_emplo] [datetime] NULL,
> [dat_consol] [datetime] NULL,
> [dat_rtr_1er_emploi] [datetime] NULL,
> [text_desc_diagn] [varchar](80) COLLATE SQL_Latin1_General_CP850_CI_AI
> NULL,
> [taux_pe_enga_ap] [float] NULL,
> [dat_enrg_oper] [datetime] NULL
> ) ON [PRIMARY]
> This is the command that I use
> BULK INSERT TDE018 FROM 'C:\TDE018.csv' WITH (FORMATFILE =
> 'C:\TDE018.fmt', KEEPNULLS, CODEPAGE ='1252')
> This is my format file
> 8.0
> 17
> 1 SQLCHAR 2 12 ";" 1
> no_empl SQL_Latin1_General_CP850_CI_AI
> 2 SQLCHAR 2 12 ";"
> 2 no_dexp SQL_Latin1_General_CP850_CI_AI
> 3 SQLINT 0 9 ";" 3 no_doss_ir
> ""
> 4 SQLSMALLINT 0 4 ";" 4
> no_even_ir ""
> 5 SQLDATETIME 1 10 ";" 5
> dat_even_orig_ir ""
> 6 SQLDATETIME 1 10 ";" 6
> dat_even_ir ""
> 7 SQLCHAR 2 2 ";" 7
> cod_loi_repa SQL_Latin1_General_CP850_CI_AI
> 8 SQLDATETIME 1 10 ";" 8
> dat_inscr_decis_r ""
> 9 SQLCHAR 2 3 ";" 9
> cod_decis_admis_r SQL_Latin1_General_CP850_CI_AI
> 10 SQLCHAR 2 3 ";" 10
> cod_motif_deci_adm SQL_Latin1_General_CP850_CI_AI
> 11 SQLDATETIME 1 10 ";" 11
> dat_presc_drt ""
> 12 SQLDATETIME 1 10 ";" 12
> dat_cess_1er_emplo ""
> 13 SQLDATETIME 1 10 ";" 13
> dat_consol ""
> 14 SQLDATETIME 1 10 ";\"" 14
> dat_rtr_1er_emploi ""
> 15 SQLCHAR 2 70 "\";" 15
> text_desc_diagn SQL_Latin1_General_CP850_CI_AI
> 16 SQLFLT8 1 6 ";" 16
> taux_pe_enga_ap ""
> 17 SQLDATETIME 1 10 "\n" 17
> dat_enrg_oper ""
> This is an exemple of row in my data file
> ENL83946781;73655112;110235140;900;1997-04-03;1997-04-03;LP;1997-04-25;ACC;REG;1998-04-03;1997-04-03;1997-04-22;1997-04-22;"a
> laceration to the left hand";1.2;2001-01-01
> I have this error and I don't know where to look because it is the first
> time I use BULK INSERT.
> This is the error
> 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.
> The statement has been terminated.
> Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1, column
> 1. Make sure the field terminator and row terminator are specified
> correctly.
> I need help please
> Thank you
> Marc R.
>