Post reply

Bulk Insert not working

  • December 17, 2015 at 2:09 pm

    #323398

    Hello!

    Please excuse the "newbie" question, but I have an issue I haven't been able to work out. Trying to do a bulk insert of a .csv into a SQL 2014 table, and the last 2 columns are blank. No matter what I've tried, I cannot get it to pick up any data (keep getting 0 rows affected).

    File is attached (I've saved it as a .xlsx so I can upload it here, but it's normally a csv), as is the DDL for the table. Have tried with and without using a format file, as well as numerous versions of BULK INSERT, including using , and \t as field terminators and and \r as row terminators. Any and all help is very much appreciated, this has got me totally stuck.

    Have tried using just the format file, and also spelling everything out (numerous variations).

    Thanks in advance!!

  • December 28, 2015 at 1:47 pm

    #1848351

    I just ran into this myself. I found 2 different approaches: The first was to use a format file. That seemed to work most of the time, but it had its problems. The other way was to use a view on the table with the same columns as you are loading (do not include the 2 columns).

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • December 28, 2015 at 2:30 pm

    #1848360

    Is your csv separated by tabs? If not, you should correct the format file accordingly.

    Are the files (csv and format file) located in the server? You're using local paths which refer to the server and not your computer.

    To avoid any more blind guesses, could you post the error that you get?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • December 28, 2015 at 4:48 pm

    #1848372

    regrahc (12/17/2015)

    Hello!

    Please excuse the "newbie" question, but I have an issue I haven't been able to work out. Trying to do a bulk insert of a .csv into a SQL 2014 table, and the last 2 columns are blank. No matter what I've tried, I cannot get it to pick up any data (keep getting 0 rows affected).

    File is attached (I've saved it as a .xlsx so I can upload it here, but it's normally a csv), as is the DDL for the table. Have tried with and without using a format file, as well as numerous versions of BULK INSERT, including using , and \t as field terminators and and \r as row terminators. Any and all help is very much appreciated, this has got me totally stuck.

    Have tried using just the format file, and also spelling everything out (numerous variations).

    Thanks in advance!!

    Please save the CSV file as a TXT file instead of any type of Excel file so that we can pick up on hidden delimiters and EOL characters.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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