Bulk insert with SQLINT changes int vlaues

  • Hi

    Using a format file to Bulk insert into a table from a CSV file.

    It is doing something funny to my int values. It seems to insert the ASCII value i.e. 1 => 49

    CREATE TABLE [dbo].[tBCP](

    [f1] [int] NULL,

    [f2] [varchar](10) NULL

    )

    The format file, fmt.fmt is

    10.0

    2

    1 SQLINT 0 4 ",\"" 1 f1 ""

    2 SQLCHAR 0 10 "\"\r" 2 f2 Latin1_General_CI_AS

    The CSV file tbcp.csv is

    1,"one"

    2,"two"

    BULK INSERT tBCP FROM 'C:\temp\tbcp.csv'

    WITH (FORMATFILE='C:\temp\fmt.fmt')

    The data I get is

    49one

    50two

    IF I change the format file from SQLINT --> SQLChAR it works. The real world data I'm using has bigger int values and I it turns them into even larger numbers, leading to overflow.

    What should I be doing here?

  • Quick question, does 7 get translated to 55? Looks like it is reading the character code values as the actual values, change it to SQLCHAR and then convert to int.

    😎

  • Yes 7 does get converted to 55 its ASCII value.

    If I try and import 21 it gets converted to 12594 (2=ASCII 50 and 1=ASCII 49) so how it gets to 12594?

    If I change the format file so instead of SQLINT I use SQLCHAR it imports fine i.e. 7 gets imported as 7.

    However I guess this might be working by luck, implicit datatype conversions going on.

    Should I always use SQLCHAR, what about when I want to do dates?

  • BTW the field terminator for the 2nd column is "\"\r" when I post it removes the

  • again it has removed the backslash n

  • You might want to add -c (character format) as it looks like it is using native (-n) format.

    The 21 -> 50 49 -> 12594 is because it translates the character value of 2(50) to the upper byte of an two byte int and 1(49) to the lower byte. This translates to binary 00110001 00110010 or 0x3132 or decimal 12594 (remember Intel uses small endian byte order)

    😎

  • Thanks

    Where do I put the -c I tried

    BULK INSERT tBCP FROM 'C:\temp\tbcp.csv'

    WITH (FORMATFILE='C:\temp\fmt.fmt', DATAFILETYPE='char')

    It doesn't work

  • terry999 (7/31/2014)


    Thanks

    Where do I put the -c I tried

    BULK INSERT tBCP FROM 'C:\temp\tbcp.csv'

    WITH (FORMATFILE='C:\temp\fmt.fmt', DATAFILETYPE='char')

    It doesn't work

    Ooops, my bad, I was thinking in bcp:-P

    Should be DATAFILETYPE = 'char'

    😎

  • No prob thanks for helping.

    I got it to work with SQLCHAR in format file, Just worried I've got it to work by chance and storing up potential problems.

    BTW. (Loved the little endian part)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply