Post reply

Bulk Insert works but leave's spaces in data

  • April 23, 2012 at 2:42 am

    #254624

    I have read much data,tables

    This is a sample

    How can i get rid off spaces in the table during import?

    result: 'VALEO '

    Thanks for help

    eddy

    BULK INSERT [001] FROM 'E:\021\001.021'

    WITH (FORMATFILE = 'E:\001.fmt' ,TABLOCK)

    9.0

    7

    1 SQLCHAR 0 4 "" 1 EinspNr ""

    2 SQLCHAR 0 3 "" 2 SA ""

    3 SQLCHAR 0 4 "" 3 Version Latin1_General_CI_AS

    4 SQLCHAR 0 8 "" 4 Datum Latin1_General_CI_AS

    5 SQLCHAR 0 1 "" 5 KZVoll ""

    6 SQLCHAR 0 5 "" 6 HerNr Latin1_General_CI_AS

    7 SQLCHAR 0 20 "\r" 7 Marke ""

  • April 23, 2012 at 4:47 am

    #1477383

    Your solution is to either don't use Bulk Insert (use SSIS and trim the data on import) or import the data into a staging table, trim the spaces off, then insert it to its final destination.

    Bulk Insert is not a command that is meant for data fiddling. It just inserts the data in the file. That's it. So far as I know, not even a format file will assist you because it's just looking for columns of different datatypes, different column order, or different # of columns.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • April 23, 2012 at 4:51 am

    #1477386

    its a shame because it works very fast.

    thanks

  • April 23, 2012 at 5:16 am

    #1477407

    eddy-961438 (4/23/2012)

    its a shame because it works very fast.

    That's why the command works so fast, because it's not transforming data.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • April 23, 2012 at 5:58 am

    #1477429

    Try to drop and recreate destination table with SET ANSI_PADDING OFF, it's still supported in SQL 2012

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • April 23, 2012 at 6:10 am

    #1477436

    Eugene Elutin (4/23/2012)

    Try to drop and recreate destination table with SET ANSI_PADDING OFF, it's still supported in SQL 2012

    Note to OP: If you have data in this table and want to follow this advice, you need to archive it off somewhere so you won't lose it. Never drop a table without first double-checking that you can lose something without consequence.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

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

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