bulk insert - crazy int values

Hi,

Im having some issues with BULK INSERT.
The table only has two columns, one INT and one VARCHAR column.

The table:

CREATE TABLE [dbo].[GA_status](
[GA_recno] [int] NOT NULL,
[GA_desc] [varchar](40) NULL)

The data file (UTF-8):
1|10848|
2|10849|
3|12611|
4|10102|
5|10137|
6|12693|
7|10149|
8|12712|

The format file:
8.0
2
1  SQLINT  0  4   "|"  1  GA_recno ""
2  SQLCHAR  0  40   "|"  2  GA_desc SQL_Latin1_General_CP1_CI_AS


The SQL:
BULK INSERT tmp_GA_status FROM 'C:\temp\TextDump\MSSQL\GA_status.dta'
WITH (FORMATFILE='C:\temp\TextDump\MSSQL\GA_status.fmt', DATAFILETYPE='char', ROWTERMINATOR='\n\r')


Okei so far so good. The sql runs and insert 8 rows, but the int values are different from the data file.
This is the result after insert:

GA_recno      GA_desc
834649071   10848
3279373      10849
3344909      12611
3410445      10102
3475981      10137
3541517      12693
3607053      10149
3672589      12712


Any ideas on how to fix it ??

the first value in your file is not stored as an int, its a char representation of an int

change your format file to:

8.0
2
1  SQLCHAR  0  4   "|"  1  GA_recno ""
2  SQLCHAR  0  40   "|"  2  GA_desc SQL_Latin1_General_CP1_CI_AS

Thanks, that almost worked!
It inserted the last seven rows, but not the first with the error;
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (GA_recno).


but what do I hafto do if I want to use SQLINT as datatype ?

AdrianJMartin wrote:

the first value in your file is not stored as an int, its a char representation of an int

change your format file to:

8.0
2
1  SQLCHAR  0  4   "|"  1  GA_recno ""
2  SQLCHAR  0  40   "|"  2  GA_desc SQL_Latin1_General_CP1_CI_AS

Matthew van Eerde wrote:



FatLaces wrote: row 1, column 1 (GA_recno).

Just a guess, but maybe there's a BOM on the file?  Try looking at it in a hex editor.

Or don't bother with the hex editor and just save it as ANSI in Notepad, if you're not going to be putting weird characters in there.  Without knowing much at all about what you're doing, your format string would imply that UTF-8 is invalid anyways (Latin-1 in your format string).

it needs to be unicode cause it will contain special characters like Æ Ø Å (Norwegian)


I converted it to Unicode without BOM and it worked!

So now I hafto figure out how to write a unicode text file without bom using c#..

Umm, if you need to support Unicode characters, shouldn't you be storing the text data in nvarchar instead of varchar fields anyway?

FatLaces wrote:

So now I hafto figure out how to write a unicode text file without bom using c#..

using( StreamWriter writer = new StreamWriter("file.txt", new UTF8Encoding(false)) )
{
   // write here.
}

The false parameter to the UTF8Encoding class tells it not to write a BOM.