Solving a Problem Importing Integers Using Format Files

  • Brandon Forest

    SSCommitted

    Points: 1764

    Comments posted to this topic are about the item Solving a Problem Importing Integers Using Format Files

  • csolervicens

    SSC Enthusiast

    Points: 184

    BCP has the -C option, it converts the non characters datatypes to character datatypes

  • Ash DBA

    SSC Enthusiast

    Points: 160

    Good to know all this time i was doing the same conversion you mentioned.

    extra work reduced 🙂

  • csolervicens

    SSC Enthusiast

    Points: 184

    BCP has the -C option, it converts the non characters datatypes to character datatypes

  • Caruncles

    Hall of Fame

    Points: 3239

    The -n option in BCP is supposed to import to it's native format. I haven't had the kind of problem you describe, but then again my business requirements may not be as critical. I can say that I have not had a problem importing integer data types. I seem to have more trouble with the Imp/Exp wizard.

    .. copied from somewhere-- can't remember-

    -n

    Performs the bulk copy operation using the native (database) data types of the data. This option does not prompt for each field; it uses the native values.

    -c

    Performs the bulk copy operation using a character data type. This option does not prompt for each field; it uses char as the storage type, no prefixes, \t (tab character) as the field separator, and (newline character) as the row terminator.

    -N

    Performs the bulk copy operation using the native (database) data types of the data for noncharacter data, and Unicode characters for character data. This option offers a higher performance alternative to the -w option, and is intended for transferring data from one SQL Server to another using a data file. It does not prompt for each field. Use this option when you are transferring data that contains ANSI extended characters and you want to take advantage of the performance of native mode. -N cannot be used with SQL Server 6.5 or earlier.

    Wallace Houston
    Sunnyland Farms, Inc.

    "We must endeavor to persevere."

  • Brandon Forest

    SSCommitted

    Points: 1764

    The -n (native format) is a great way to transfer data between SQL server tables, but that's not the case in this scenario. These are text files we receive from a Vendor. It is simple enough to import them as Char data types into the raw extract tables and implicitly converting them to Integer between the other SQL tables, and I was doing that initially. The business requirements changed however, and I was forced to import them as integers directly into the raw extract tables. This article describes how to import text data directly into integer fields.

    Cheers!

    Brandon Forest

  • wmjas.shaw

    Grasshopper

    Points: 15

    It isn't clear from your description what "unsatisfactory" means. Were the numbers simply wrong in some instances?

  • Brandon Forest

    SSCommitted

    Points: 1764

    The numbers were wrong in all instances.

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

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