BULK IMPORT Stress

[Davy B]

I am trying to import a data file, which is tab delimited, using BULK
INSERT. I have used BCP to create a format file, since the destination
table has around 20 columns, but the data file has only three.

Here's the problem: The columns I am trying to import comprise ID (an
int identity column), Name (a varchar(255) column and Status (a small
int column). The data file contains identity values for the first
column, so I am using the KEEPIDENTITY modifier. The Status column is
mandatory, so I have set all rows in the data file to zero for that
column. All of the other columns in the destination table either allow
NULL or have default values. When I BULK INSERT the file using the
format file the identity columns are NOT imported and the Status column
gets value 3376. The Name column is the only one that gets imported
correctly. Here's the format file:

8.0
3
1 SQLINT 0 4 "\t" 1 ID
""
2 SQLCHAR 0 0 "\t" 2 Name
SQL_Latin1_General_CP1_CI_AS
3 SQLSMALLINT 0 2 "\n" 4 Status
""
Sorry it's a bit messy.

Where is 3376 coming from, and why are my identity values for column ID
not being imported?

[Erland Sommarskog]

Davy B (da**********@itv.com) writes:

I am trying to import a data file, which is tab delimited, using BULK
INSERT. I have used BCP to create a format file, since the destination
table has around 20 columns, but the data file has only three.

Here's the problem: The columns I am trying to import comprise ID (an
int identity column), Name (a varchar(255) column and Status (a small
int column). The data file contains identity values for the first
column, so I am using the KEEPIDENTITY modifier. The Status column is
mandatory, so I have set all rows in the data file to zero for that
column. All of the other columns in the destination table either allow
NULL or have default values. When I BULK INSERT the file using the
format file the identity columns are NOT imported and the Status column
gets value 3376. The Name column is the only one that gets imported
correctly. Here's the format file:

8.0
3
1 SQLINT 0 4 "\t" 1 ID
""
2 SQLCHAR 0 0 "\t" 2 Name
SQL_Latin1_General_CP1_CI_AS
3 SQLSMALLINT 0 2 "\n" 4 Status
""

Is the file a text file? That format file is a for a binary file of some
sort. That is, the data type should be SQLCHAR, if it is a text file, as
that is the type for data in the file.

I would also recommend changing the lngths (4 and 2) to 0, and finally
changing \n to \r\n. (Unless the file comes from a Unix-type of system and
really has \n as the row terminator.)

Note: if the file is a Unicode file, the type should be SQLNCHAR and
the terminators should be "\t\0" and "\r\0\n\0" respectively.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Davy B]

Thanks for responding Erland.

The data file is a tab-delimited file created from an Excel "save as".
If I had been importing a unicode file I am sure the Name column would
have been messed up, but that's the only column that imported
correctly. I tried changing the SQLCHAR to SQLNCHAR, but that breaks.
I tried changing the "\t" delimiter to "\t\0" and that breaks too. I
also tried changing the sizes of cols 1 and 3 to 0 as you suggest, but
get exactly the same result.

I also tried using BCP to export data from a similar table, and the
result included wingdings-type characters for the numeric columns.

BUT SUCCESS!!!! I didn't notice in the BOL example, but the field
types for importing must all be SQLCHAR, since the numbers are, of
course, text. So why does BCP create format files that have the
database column type??? When I changed both SQLINT and SQLSMALLINT to
SQLCHAR, and set the column widths to 0 as you suggested it all worked
perfectly.

Kind regards,

Davy B


Erland Sommarskog wrote:

Davy B (da**********@itv.com) writes:

I am trying to import a data file, which is tab delimited, using BULK
INSERT. I have used BCP to create a format file, since the destination
table has around 20 columns, but the data file has only three.

Here's the problem: The columns I am trying to import comprise ID (an
int identity column), Name (a varchar(255) column and Status (a small
int column). The data file contains identity values for the first
column, so I am using the KEEPIDENTITY modifier. The Status column is
mandatory, so I have set all rows in the data file to zero for that
column. All of the other columns in the destination table either allow
NULL or have default values. When I BULK INSERT the file using the
format file the identity columns are NOT imported and the Status column
gets value 3376. The Name column is the only one that gets imported
correctly. Here's the format file:

8.0
3
1 SQLINT 0 4 "\t" 1 ID
""
2 SQLCHAR 0 0 "\t" 2 Name
SQL_Latin1_General_CP1_CI_AS
3 SQLSMALLINT 0 2 "\n" 4 Status
""

Is the file a text file? That format file is a for a binary file of some
sort. That is, the data type should be SQLCHAR, if it is a text file, as
that is the type for data in the file.

I would also recommend changing the lngths (4 and 2) to 0, and finally
changing \n to \r\n. (Unless the file comes from a Unix-type of system and
really has \n as the row terminator.)

Note: if the file is a Unicode file, the type should be SQLNCHAR and
the terminators should be "\t\0" and "\r\0\n\0" respectively.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Davy B (da**********@itv.com) writes:

The data file is a tab-delimited file created from an Excel "save as".
If I had been importing a unicode file I am sure the Name column would
have been messed up, but that's the only column that imported
correctly. I tried changing the SQLCHAR to SQLNCHAR, but that breaks.
I tried changing the "\t" delimiter to "\t\0" and that breaks too. I
also tried changing the sizes of cols 1 and 3 to 0 as you suggest, but
get exactly the same result.

SQLNCHAR is what you should use if you had a Unicode file. Since you
did not specify that part, I had to cover it in case.

So why does BCP create format files that have the database column
type???

Because it guessed that you wanted to create a native file? Did you tell it
that you had a text file?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx