Bulk insert and collations

1). I was trying to do bulk insert t temp tables, so because temdb has a
Latin1_General_CI_AS collation, an my database has a
SQL_Latin1_General_CP1_CI_AS one I was unable to do joins untill I created
special tables in my database to truncate before bulk inserts.
2). Another question: all fields in bulk insert file created not by me are
nullable varchars,but it is considers an error when there is no fields
terminators in the last row (it is a timestamp) and there is only only row
terminator .
Do I need to fix datafile or I can change something in bulk insert statement
format or settings?On Thu, 26 Jul 2007 07:56:04 -0700, UncleSam89
<UncleSam89@.discussions.microsoft.com> wrote:
>1). I was trying to do bulk insert t temp tables, so because temdb has a
>Latin1_General_CI_AS collation, an my database has a
>SQL_Latin1_General_CP1_CI_AS one I was unable to do joins untill I created
>special tables in my database to truncate before bulk inserts.
Sorry, I don't see what the question is. You can specify the
collation for any character column in a table, so there is no reason
to use tempdb just to establish a specific colation.
>2). Another question: all fields in bulk insert file created not by me are
>nullable varchars,but it is considers an error when there is no fields
>terminators in the last row (it is a timestamp) and there is only only row
>terminator .
>Do I need to fix datafile or I can change something in bulk insert statement
>format or settings?
I think you are saying that the last row of the table isn't a valid
data row. In the past I have had a quick-and-dirty command line
program written to fix the data file before loading it.
Roy Harvey
Beacon Falls, CT|||I don't using tempdb to force the particular collation, but to avoid creating
too much bulk crap in my own database. I saw in some of collation relared
threads here the use of collation in the comparisons, but the 'collate'
keyword in create statements didn't help.
Does your answer means that I am right now doing only possible stuff to
resolve my problems?
"Roy Harvey" wrote:
> On Thu, 26 Jul 2007 07:56:04 -0700, UncleSam89
> <UncleSam89@.discussions.microsoft.com> wrote:
> >1). I was trying to do bulk insert t temp tables, so because temdb has a
> >Latin1_General_CI_AS collation, an my database has a
> >SQL_Latin1_General_CP1_CI_AS one I was unable to do joins untill I created
> >special tables in my database to truncate before bulk inserts.
> Sorry, I don't see what the question is. You can specify the
> collation for any character column in a table, so there is no reason
> to use tempdb just to establish a specific colation.
> >2). Another question: all fields in bulk insert file created not by me are
> >nullable varchars,but it is considers an error when there is no fields
> >terminators in the last row (it is a timestamp) and there is only only row
> >terminator .
> >Do I need to fix datafile or I can change something in bulk insert statement
> >format or settings?
> I think you are saying that the last row of the table isn't a valid
> data row. In the past I have had a quick-and-dirty command line
> program written to fix the data file before loading it.
> Roy Harvey
> Beacon Falls, CT
>|||On Fri, 27 Jul 2007 06:02:01 -0700, UncleSam89
<UncleSam89@.discussions.microsoft.com> wrote:
>I don't using tempdb to force the particular collation, but to avoid creating
>too much bulk crap in my own database. I saw in some of collation relared
>threads here the use of collation in the comparisons, but the 'collate'
>keyword in create statements didn't help.
>Does your answer means that I am right now doing only possible stuff to
>resolve my problems?
When I need a few staging tables for imported data I generally put
them in the target database with an _Imported suffix on the name. When
I have a LOT of tables for importing data, or the import process
becomes too large or complex, I create a Staging database just for
those tables.
It might be worth mentioning that I never import data directly into
production tables, always into staging tables of some sort. In some
cases I even build history tables for the bulk loaded data and assign
batch identifiers so I can trace back any problems to their origin -
particularly useful when the data source is third party with a track
record of screwing things up.
I never explicitly create any objects on tempdb in production as
tempdb is reinitialized each time SQL Server starts and anything in
tempdb is lost.
Roy Harvey
Beacon Falls, CT

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";"valu e6";"value7";"value8";"value9";"value10";"value11" ;"value12";"value13"
13 value, with text qualifier double quote, in one row
and the format file:
9.0
13
1SQLCHAR00"\""1campo_0"SQL_Latin1_General_CP1_CI_AS"
2SQLCHAR00"\";\""2campo_1"SQL_Latin1_General_CP1_CI_AS"
3SQLCHAR00"\";\""3campo_2"SQL_Latin1_General_CP1_CI_AS"
4SQLCHAR00"\";\""4campo_3"SQL_Latin1_General_CP1_CI_AS"
5SQLTEXT00"\";\""5campo_4"SQL_Latin1_General_CP1_CI_AS"
6SQLCHAR00"\";\""6campo_5"SQL_Latin1_General_CP1_CI_AS"
7SQLCHAR00"\";\""7campo_6"SQL_Latin1_General_CP1_CI_AS"
8SQLCHAR00"\";\""8campo_7"SQL_Latin1_General_CP1_CI_AS"
9SQLTEXT00"\";\""9campo_8"SQL_Latin1_General_CP1_CI_AS"
10SQLCHAR00"\";\""10campo_9"SQL_Latin1_General_CP1_CI_AS"
11SQLCHAR00"\";\""11campo_10"SQL_Latin1_General_CP1_CI_AS"
12SQLCHAR00"\";\""12campo_11"SQL_Latin1_General_CP1_CI_AS"
13SQLCHAR00\""13campo_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.googlegr oups.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";"valu e6";"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";"valu e";"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:
[vbcol=seagreen]
> 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.googlegr oups.com...
|||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.googlegr oups.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";"valu e6";"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.googlegro ups.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";"valu e";"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:
>