|
|
Posted by Erland Sommarskog on 07/26/06 21:50
Davy B (david.bridge@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|