Results 1 to 5 of 5

Thread: bcp import

  1. #1
    Join Date
    Feb 2004
    Posts
    492

    Unanswered: bcp import

    I'd like to bcp import a file that sometimes misses the last column/s. There's an EOL character instead. For some reason, bcp wraps around, ignoring the EOL character, and continues reading from the next row of the file. Instead, I'd like to replace the missing columns by null.

    I've tried using bcp, bulk insert and the DTS Wizard. So far, I've only been succesfull using the DTS Wizard. I also do some other bcp imports, so I'd like to stick with bcp.

    bcp table1 in myFile.csv -SServer1 -T -c -k -t\t -r\n

    myFile.csv looks like... (replaced tabs by ;)
    Col001;Col002;Col003;Col004
    Col001;Col002
    Col001;Col002;Col003
    Col001;Col002;Col003;Col004
    etc

    are imported as:
    Col001;Col002;Col003;Col004
    Col001;Col002;Col001;Col002
    Col001;Col002;Col003;Col001
    etc

    Any suggestions?

  2. #2
    Join Date
    Dec 2005
    Posts
    2
    if you want to insert null for column3 and column 4 then only specify the separator
    Col001;Col002;Col003;Col004
    Col001;Col002;;
    Col001;Col002;Col003
    Col001;Col002;Col003;Col004

  3. #3
    Join Date
    Feb 2004
    Posts
    492
    Not specifing the row terminator doesn't make a difference. bcp still regards the \n as just another character to import, at least, on my system.

  4. #4
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135
    ...it sounds like the terminator in your file is the problem...on what system was the input file generated? (unix?) This might cause new lines to be unreadable on Windows...

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    The file has lf as row-terminator, but I guess it wont be a problem changing that into cr/lf. The lf is readable for those rows that match the layout of the table. It's the rows that don't match that are wrapped. As far as I can see now, either the file will have to include empty values instead of nothing or I'll have to divide the proces in smaller processes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •