|
|
Posted by Davy B on 07/27/06 06:26
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 (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]
|