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