Results 1 to 11 of 11

Thread: BCP Import

  1. #1
    Join Date
    Sep 2004
    Posts
    15

    Smile Unanswered: BCP Import

    Hi All,

    I am using bcp utility to import text File data into SQL server table.I import about 50-60 such files. All other files except one file copies less no of rows to the database every time , than it has in the text file. All other files having either less or greater amount of data transfers it properly.I do not know why this happens to only one file.

    Colmn delimiter used is | and row delimiters used is \n.

    BCP is being run through a batch file.

    Any idea how to solve this?


    Thanks & regards
    Rohit

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Does BCP issue any error messages? Does BCP do anything at all, or just stop running without doing anything? Are there any messages in the SQL Server log file? Have you tried writing a script using Perl or something like that to parse the file on the client to ensure it is properly formatted?

    -PatP

  3. #3
    Join Date
    Sep 2004
    Posts
    15

    BCP Import

    No!actually i am not getting any error messages logged.BCP doesn't stop also.

    I am using a batch file having following BCP commands (one for each table) for
    importing text files to SQL server

    echo +--------------------------------------------------------------------
    echo Table1 is now Importing...
    echo +--------------------------------------------------------------------------+
    bcp "DBName..<tablename>" in "Filename.cdb" -c -ebi_cnmch_rel.err -Usa -S%1 -P%2 -t"|" -r"\n"
    pause

    echo +--------------------------------------------------------------------------+
    echo Table2 is now Importing...
    echo +--------------------------------------------------------------------------+
    bcp "DBName..<tablename>" in "Filename.cdb" -c -ebi_Filename.err -Usa -S%1 -P%2 -t"|" -r"\n"
    pause


    All files are getting imported properly when i run this batch file.
    Only one file <table1> imports less no of records than in the text file.


    These Files are being created using BCP only (SQL to text file.).

    bcp "cdb_batch..<Table1>" out "Table1.cdb" -c -e<Table1>.err -Usa -S%1 -P%2 -t"|" -r"\n"


    Why only one file should give problems while importing?

    Regards
    Rohit

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Does any of the data in the problem file contain either the | or a newline inside the column? Those rows would be rejected by BCP because they had too many columns, and possibly bad data types too.

    Can you rexport the file using native format instead of character format? That is MUCH safer in my experience.

    If not, use the BCP command's -e argument to trap the rows it can't process, then analyze them (using -o at the same time helps a lot).

    -PatP

  5. #5
    Join Date
    Jul 2004
    Posts
    52
    Why not use the -n (native) qualifier in BCP to export/import in native mode. Files will be smaller and no need for delimiters.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Row misalignment may result in successful import but with fewer or more number of records imported. I'd second Pat's suggestion of presence of | or \n in your source file, which often contributes to this type of situation.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by vaxman
    Why not use the -n (native) qualifier in BCP to export/import in native mode. Files will be smaller and no need for delimiters.
    Yeah, that's why was my first suggestion.

    -PatP

  8. #8
    Join Date
    Sep 2004
    Posts
    15

    BCP Import

    Thanks to all of u.

    I have tried -n options but it shows 0 rows copied.
    Error file traps the following error (using -e option thanks to Pat).

    #@ Row 399, Column 22: Invalid character value for cast specification @#
    0 CCF48B0FE27F11D5B2F300D009D4EDC9 Multi-Location 36 3 Tamil Nadu Small Industries Corporation Limited 1965 <NULL> Tansi 0 tansiedp@vsnl.com www.tansi.com 34 55 0 0 2001-11-26 18:52:32.000 2004-08-30 13:15:35.810 cdb_user12 cdb_user06 <NULL> ||

    If I check the specified Row - Column it is a GUID column.But this data type is there in all rows.
    Probably i is showing wrong location (Column) for the error.

    Rohit

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The column that the message refers to is the data column, not the character column. There aren't anywhere near 21 of the | delimiters in the sample that you posted, so the problem lies further "right" on the line somewhere.

    Do you have the option of using the -n (native) file format? It will make these problems go away.

    -PatP

  10. #10
    Join Date
    Sep 2004
    Posts
    15
    Yes! you are right pat.Actually the sample i have pasted is from Error file.
    Actual text file has all Column seperators "|". Column 22 is the last column after which there is no |.This column is blank in the sample data.
    It is a column with data type int length 4 Precision 10 Nullable.

    I tried -n option but it says 0 rows exported.


    Thanx
    Rohit

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm not sure why the export using -n produced zero rows. My first guess would be that the underlying data has changed somehow since the time you did the character export. This is still definitely my preferred solution to your problem, assuming we can get the data extracted!

    If you have to continue working on the character mode files, check the row in the original data file (as it was exported) to see if that row contains either the column separator character (|) or the newline character anywhere in the data that causes the error, or the row that was exported immediately before the row that caused the error.

    I can't emphasize enough that the native or -n file format would be my preference for this task. There are many reasons for choosing a character export, but moving to another SQL Server always works better for me when I'm using the native file format.

    -PatP

Posting Permissions

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