build sql script: collate

Showing posts with label collate. Show all posts

Sunday, March 11, 2012

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.
>

BULK INSERT HELP!

Hello,
This is my table
CREATE TABLE [dbo].[TDE018](
[no_empl] [varchar](12) COLLATE SQL_Latin1_General_CP850_CI_AI NOT N
ULL,
[no_dexp] [varchar](12) COLLATE SQL_Latin1_General_CP850_CI_AI NOT N
ULL,
[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 N
ULL,
[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_C
I_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;R
EG;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_C
I_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.
>

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.
>

Saturday, February 25, 2012

BULK INSERT - error column too long

Hi,
I need to load a data file with bulk load.
I have these table:
CREATE TABLE [dbo].[test_db_import](
[campo_0] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_1] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_2] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_3] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_4] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_5] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_6] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_7] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_8] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[campo_9] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_10] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_11] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_12] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[tappo] [nvarchar](1) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
the input file (a file .txt) is like that:
"value1";"value2";"value3";"value4";"value5";"value6";"value7";"value8";"value9";"value10";"value11";"value12";"value13"
13 value, with text qualifier double quote, in one row
and the format file:
9.0
13
1 SQLCHAR 0 0 "\"" 1 campo_0 "SQL_Latin1_General_CP1_CI_AS"
2 SQLCHAR 0 0 "\";\"" 2 campo_1 "SQL_Latin1_General_CP1_CI_AS"
3 SQLCHAR 0 0 "\";\"" 3 campo_2 "SQL_Latin1_General_CP1_CI_AS"
4 SQLCHAR 0 0 "\";\"" 4 campo_3 "SQL_Latin1_General_CP1_CI_AS"
5 SQLTEXT 0 0 "\";\"" 5 campo_4 "SQL_Latin1_General_CP1_CI_AS"
6 SQLCHAR 0 0 "\";\"" 6 campo_5 "SQL_Latin1_General_CP1_CI_AS"
7 SQLCHAR 0 0 "\";\"" 7 campo_6 "SQL_Latin1_General_CP1_CI_AS"
8 SQLCHAR 0 0 "\";\"" 8 campo_7 "SQL_Latin1_General_CP1_CI_AS"
9 SQLTEXT 0 0 "\";\"" 9 campo_8 "SQL_Latin1_General_CP1_CI_AS"
10 SQLCHAR 0 0 "\";\"" 10 campo_9 "SQL_Latin1_General_CP1_CI_AS"
11 SQLCHAR 0 0 "\";\"" 11 campo_10 "SQL_Latin1_General_CP1_CI_AS"
12 SQLCHAR 0 0 "\";\"" 12 campo_11 "SQL_Latin1_General_CP1_CI_AS"
13 SQLCHAR 0 0 \"" 13 campo_12 "SQL_Latin1_General_CP1_CI_AS"
I have these error:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]
error bulk load. The data file contains a column too long for the row
1, column 13. Check the rowterminator and the field terminator.
(sorry, that is a translation from error message in other languages)
I have SQL Server 2005 St. edition, and I don't have direct access to
the db server.
Can you help me?
Thank's!
FrancI would guess that the data file is missing row terminators on some columns.
You may have to visually inspect and/or correct the data file.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Franc" <webmasterpoint@.gmail.com> wrote in message
news:1166518533.669439.310530@.t46g2000cwa.googlegroups.com...
> Hi,
> I need to load a data file with bulk load.
> I have these table:
> CREATE TABLE [dbo].[test_db_import](
> [campo_0] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_1] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_2] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_3] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_4] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_5] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_6] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_7] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_8] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
> [campo_9] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_10] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_11] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_12] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
> [tappo] [nvarchar](1) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> the input file (a file .txt) is like that:
> "value1";"value2";"value3";"value4";"value5";"value6";"value7";"value8";"value9";"value10";"value11";"value12";"value13"
> 13 value, with text qualifier double quote, in one row
> and the format file:
> 9.0
> 13
> 1 SQLCHAR 0 0 "\"" 1 campo_0 "SQL_Latin1_General_CP1_CI_AS"
> 2 SQLCHAR 0 0 "\";\"" 2 campo_1 "SQL_Latin1_General_CP1_CI_AS"
> 3 SQLCHAR 0 0 "\";\"" 3 campo_2 "SQL_Latin1_General_CP1_CI_AS"
> 4 SQLCHAR 0 0 "\";\"" 4 campo_3 "SQL_Latin1_General_CP1_CI_AS"
> 5 SQLTEXT 0 0 "\";\"" 5 campo_4 "SQL_Latin1_General_CP1_CI_AS"
> 6 SQLCHAR 0 0 "\";\"" 6 campo_5 "SQL_Latin1_General_CP1_CI_AS"
> 7 SQLCHAR 0 0 "\";\"" 7 campo_6 "SQL_Latin1_General_CP1_CI_AS"
> 8 SQLCHAR 0 0 "\";\"" 8 campo_7 "SQL_Latin1_General_CP1_CI_AS"
> 9 SQLTEXT 0 0 "\";\"" 9 campo_8 "SQL_Latin1_General_CP1_CI_AS"
> 10 SQLCHAR 0 0 "\";\"" 10 campo_9 "SQL_Latin1_General_CP1_CI_AS"
> 11 SQLCHAR 0 0 "\";\"" 11 campo_10 "SQL_Latin1_General_CP1_CI_AS"
> 12 SQLCHAR 0 0 "\";\"" 12 campo_11 "SQL_Latin1_General_CP1_CI_AS"
> 13 SQLCHAR 0 0 \"" 13 campo_12 "SQL_Latin1_General_CP1_CI_AS"
>
> I have these error:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> error bulk load. The data file contains a column too long for the row
> 1, column 13. Check the rowterminator and the field terminator.
> (sorry, that is a translation from error message in other languages)
> I have SQL Server 2005 St. edition, and I don't have direct access to
> the db server.
> Can you help me?
> Thank's!
> Franc
>|||Hi,
Thank's!
I try with only one row:
"10092itid2011";"Batterie Notebook e Computers Portatili HP,
COMPAQ";"125.04";"EUR";""Cod CBI0823A; Casa 2-Power; Descrizione
Batteria notebook 14.8v 4400mAh per HP Pavilion NX,
XT1-XT2-XT3-XT4-XT5, ZE4000, 5000, ZE4300-5185, HP e Compaq Business
Notebook NX9000-NX9005-NX9010, N1050V, Compaq Presario 1110,1115,1120,
2100, 2500
serie,";"value";"658";"Accumulatori";"value";"value";"value";"value";"HP,
COMPAQ"
and import correctly.
But I have a problem:
in the import skip the first column of table (field: 'campo_0')
and insert the last two field
"value";"HP, COMPAQ"
in 'campo_12' column
Thank's!
Franc
Arnie Rowland ha scritto:
> I would guess that the data file is missing row terminators on some columns.
> You may have to visually inspect and/or correct the data file.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "Franc" <webmasterpoint@.gmail.com> wrote in message
> news:1166518533.669439.310530@.t46g2000cwa.googlegroups.com...
> > Hi,
> > I need to load a data file with bulk load.
> >
> > I have these table:
> >
> > CREATE TABLE [dbo].[test_db_import](
> > [campo_0] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_1] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_2] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_3] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_4] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_5] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_6] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_7] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_8] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
> > [campo_9] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_10] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_11] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_12] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
> > [tappo] [nvarchar](1) COLLATE Latin1_General_CI_AS NULL
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> >
> > the input file (a file .txt) is like that:
> >
> > "value1";"value2";"value3";"value4";"value5";"value6";"value7";"value8";"value9";"value10";"value11";"value12";"value13"
> >
> > 13 value, with text qualifier double quote, in one row
> >
> > and the format file:
> >
> > 9.0
> > 13
> > 1 SQLCHAR 0 0 "\"" 1 campo_0 "SQL_Latin1_General_CP1_CI_AS"
> > 2 SQLCHAR 0 0 "\";\"" 2 campo_1 "SQL_Latin1_General_CP1_CI_AS"
> > 3 SQLCHAR 0 0 "\";\"" 3 campo_2 "SQL_Latin1_General_CP1_CI_AS"
> > 4 SQLCHAR 0 0 "\";\"" 4 campo_3 "SQL_Latin1_General_CP1_CI_AS"
> > 5 SQLTEXT 0 0 "\";\"" 5 campo_4 "SQL_Latin1_General_CP1_CI_AS"
> > 6 SQLCHAR 0 0 "\";\"" 6 campo_5 "SQL_Latin1_General_CP1_CI_AS"
> > 7 SQLCHAR 0 0 "\";\"" 7 campo_6 "SQL_Latin1_General_CP1_CI_AS"
> > 8 SQLCHAR 0 0 "\";\"" 8 campo_7 "SQL_Latin1_General_CP1_CI_AS"
> > 9 SQLTEXT 0 0 "\";\"" 9 campo_8 "SQL_Latin1_General_CP1_CI_AS"
> > 10 SQLCHAR 0 0 "\";\"" 10 campo_9 "SQL_Latin1_General_CP1_CI_AS"
> > 11 SQLCHAR 0 0 "\";\"" 11 campo_10 "SQL_Latin1_General_CP1_CI_AS"
> > 12 SQLCHAR 0 0 "\";\"" 12 campo_11 "SQL_Latin1_General_CP1_CI_AS"
> > 13 SQLCHAR 0 0 \"" 13 campo_12 "SQL_Latin1_General_CP1_CI_AS"
> >
> >
> > I have these error:
> >
> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> > [Microsoft][ODBC SQL Server Driver][SQL Server]
> > error bulk load. The data file contains a column too long for the row
> > 1, column 13. Check the rowterminator and the field terminator.
> > (sorry, that is a translation from error message in other languages)
> >
> > I have SQL Server 2005 St. edition, and I don't have direct access to
> > the db server.
> >
> > Can you help me?
> >
> > Thank's!
> > Franc
> >|||I would guess that the data file is missing row terminators on some columns.
You may have to visually inspect and/or correct the data file.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Franc" <webmasterpoint@.gmail.com> wrote in message
news:1166518533.669439.310530@.t46g2000cwa.googlegroups.com...
> Hi,
> I need to load a data file with bulk load.
> I have these table:
> CREATE TABLE [dbo].[test_db_import](
> [campo_0] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_1] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_2] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_3] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_4] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_5] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_6] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_7] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_8] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
> [campo_9] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_10] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_11] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_12] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
> [tappo] [nvarchar](1) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> the input file (a file .txt) is like that:
> "value1";"value2";"value3";"value4";"value5";"value6";"value7";"value8";"value9";"value10";"value11";"value12";"value13"
> 13 value, with text qualifier double quote, in one row
> and the format file:
> 9.0
> 13
> 1 SQLCHAR 0 0 "\"" 1 campo_0 "SQL_Latin1_General_CP1_CI_AS"
> 2 SQLCHAR 0 0 "\";\"" 2 campo_1 "SQL_Latin1_General_CP1_CI_AS"
> 3 SQLCHAR 0 0 "\";\"" 3 campo_2 "SQL_Latin1_General_CP1_CI_AS"
> 4 SQLCHAR 0 0 "\";\"" 4 campo_3 "SQL_Latin1_General_CP1_CI_AS"
> 5 SQLTEXT 0 0 "\";\"" 5 campo_4 "SQL_Latin1_General_CP1_CI_AS"
> 6 SQLCHAR 0 0 "\";\"" 6 campo_5 "SQL_Latin1_General_CP1_CI_AS"
> 7 SQLCHAR 0 0 "\";\"" 7 campo_6 "SQL_Latin1_General_CP1_CI_AS"
> 8 SQLCHAR 0 0 "\";\"" 8 campo_7 "SQL_Latin1_General_CP1_CI_AS"
> 9 SQLTEXT 0 0 "\";\"" 9 campo_8 "SQL_Latin1_General_CP1_CI_AS"
> 10 SQLCHAR 0 0 "\";\"" 10 campo_9 "SQL_Latin1_General_CP1_CI_AS"
> 11 SQLCHAR 0 0 "\";\"" 11 campo_10 "SQL_Latin1_General_CP1_CI_AS"
> 12 SQLCHAR 0 0 "\";\"" 12 campo_11 "SQL_Latin1_General_CP1_CI_AS"
> 13 SQLCHAR 0 0 \"" 13 campo_12 "SQL_Latin1_General_CP1_CI_AS"
>
> I have these error:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> error bulk load. The data file contains a column too long for the row
> 1, column 13. Check the rowterminator and the field terminator.
> (sorry, that is a translation from error message in other languages)
> I have SQL Server 2005 St. edition, and I don't have direct access to
> the db server.
> Can you help me?
> Thank's!
> Franc
>|||This looks 'odd' to me:
;""
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Franc" <webmasterpoint@.gmail.com> wrote in message
news:1166520934.333254.285360@.48g2000cwx.googlegroups.com...
> Hi,
> Thank's!
> I try with only one row:
> "10092itid2011";"Batterie Notebook e Computers Portatili HP,
> COMPAQ";"125.04";"EUR";""Cod CBI0823A; Casa 2-Power; Descrizione
> Batteria notebook 14.8v 4400mAh per HP Pavilion NX,
> XT1-XT2-XT3-XT4-XT5, ZE4000, 5000, ZE4300-5185, HP e Compaq Business
> Notebook NX9000-NX9005-NX9010, N1050V, Compaq Presario 1110,1115,1120,
> 2100, 2500
> serie,";"value";"658";"Accumulatori";"value";"value";"value";"value";"HP,
> COMPAQ"
> and import correctly.
> But I have a problem:
> in the import skip the first column of table (field: 'campo_0')
> and insert the last two field
> "value";"HP, COMPAQ"
> in 'campo_12' column
> Thank's!
> Franc
> Arnie Rowland ha scritto:
>> I would guess that the data file is missing row terminators on some
>> columns.
>> You may have to visually inspect and/or correct the data file.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>> You can't help someone get up a hill without getting a little closer to
>> the
>> top yourself.
>> - H. Norman Schwarzkopf
>>
>> "Franc" <webmasterpoint@.gmail.com> wrote in message
>> news:1166518533.669439.310530@.t46g2000cwa.googlegroups.com...
>> > Hi,
>> > I need to load a data file with bulk load.
>> >
>> > I have these table:
>> >
>> > CREATE TABLE [dbo].[test_db_import](
>> > [campo_0] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_1] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_2] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_3] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_4] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_5] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_6] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_7] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_8] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
>> > [campo_9] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_10] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_11] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_12] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
>> > [tappo] [nvarchar](1) COLLATE Latin1_General_CI_AS NULL
>> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>> >
>> > the input file (a file .txt) is like that:
>> >
>> > "value1";"value2";"value3";"value4";"value5";"value6";"value7";"value8";"value9";"value10";"value11";"value12";"value13"
>> >
>> > 13 value, with text qualifier double quote, in one row
>> >
>> > and the format file:
>> >
>> > 9.0
>> > 13
>> > 1 SQLCHAR 0 0 "\"" 1 campo_0 "SQL_Latin1_General_CP1_CI_AS"
>> > 2 SQLCHAR 0 0 "\";\"" 2 campo_1 "SQL_Latin1_General_CP1_CI_AS"
>> > 3 SQLCHAR 0 0 "\";\"" 3 campo_2 "SQL_Latin1_General_CP1_CI_AS"
>> > 4 SQLCHAR 0 0 "\";\"" 4 campo_3 "SQL_Latin1_General_CP1_CI_AS"
>> > 5 SQLTEXT 0 0 "\";\"" 5 campo_4 "SQL_Latin1_General_CP1_CI_AS"
>> > 6 SQLCHAR 0 0 "\";\"" 6 campo_5 "SQL_Latin1_General_CP1_CI_AS"
>> > 7 SQLCHAR 0 0 "\";\"" 7 campo_6 "SQL_Latin1_General_CP1_CI_AS"
>> > 8 SQLCHAR 0 0 "\";\"" 8 campo_7 "SQL_Latin1_General_CP1_CI_AS"
>> > 9 SQLTEXT 0 0 "\";\"" 9 campo_8 "SQL_Latin1_General_CP1_CI_AS"
>> > 10 SQLCHAR 0 0 "\";\"" 10 campo_9 "SQL_Latin1_General_CP1_CI_AS"
>> > 11 SQLCHAR 0 0 "\";\"" 11 campo_10 "SQL_Latin1_General_CP1_CI_AS"
>> > 12 SQLCHAR 0 0 "\";\"" 12 campo_11 "SQL_Latin1_General_CP1_CI_AS"
>> > 13 SQLCHAR 0 0 \"" 13 campo_12 "SQL_Latin1_General_CP1_CI_AS"
>> >
>> >
>> > I have these error:
>> >
>> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
>> > [Microsoft][ODBC SQL Server Driver][SQL Server]
>> > error bulk load. The data file contains a column too long for the row
>> > 1, column 13. Check the rowterminator and the field terminator.
>> > (sorry, that is a translation from error message in other languages)
>> >
>> > I have SQL Server 2005 St. edition, and I don't have direct access to
>> > the db server.
>> >
>> > Can you help me?
>> >
>> > Thank's!
>> > Franc
>> >
>

Sunday, March 11, 2012

BULK INSERT HELP!

BULK INSERT HELP!

BULK INSERT HELP!

Saturday, February 25, 2012

BULK INSERT - error column too long

BULK INSERT - error column too long

BULK INSERT - error column too long

build sql script

Blog Archive

About Me