Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009
    Posts
    1

    Unanswered: import txt file with null value in the file

    i have a txt file about 500M

    create table tab (c1 char(3),c2 int,c3 int,c4 int)

    the data in the file is like below

    abc,null,null,6
    bcd,null,6,4
    .......

    the comman i used is:
    bcp test.dbo.table in c:\a.csv -c -t , -S "127.0.0.1" -U "sa" -P ""
    but i can't import the file into the database
    but when i replace the null in the file ,it successed
    abc,,,6
    bcd,,6,4

    how can i imort the data successfully without replacing then null in the file.
    the file is too big,

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Options:
    1) Get the file to write out correctly in the first place. I assume your answer will be "I can't - not my process" etc.
    2) Create a staging table with bugger all constraints. VARCHAR for all data types. BCP into there. Replace "null" with NULL and any other data cleansing you need to do. Then insert into the final destination table.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    would by nice if MS/DOS had UNIX sed command, could replace easily at command line.

    sed 's/NULL//g' filename > filename.new

    I think you can download a sed.exe to do this, if you want to take this path, or write a command script in DOS, will be a bit more clumsy though.

    Look at

    http://www.dostips.com/DtTipsStringManipulation.php

Posting Permissions

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