Professional Web Applications Themes

format file and bcp/bulk insert - Microsoft SQL / MS SQL Server

I'm trying to use a format file for importing in a data file that has fewer columns than my table I'm importing to. I've gone through BOL, and it looked really simply to set one up to do this, but I keep encountering problems. Here are the steps I'm using... 1. Export file from Excel to Tab delimeted text file. 2. Execute bulk insert... bulk insert fte_import from 'd:\path_to_file\file.txt' with ( firstrow =2, --start at row 2 and eliminate header formatfile='d:\path_to_format_file\import.fmt' ) 3. I then receive the error 'Could not bulk insert. Prefix length, field length, or terminator required for ...

  1. #1

    Default format file and bcp/bulk insert

    I'm trying to use a format file for importing in a data
    file that has fewer columns than my table I'm importing
    to. I've gone through BOL, and it looked really simply to
    set one up to do this, but I keep encountering problems.

    Here are the steps I'm using...
    1. Export file from Excel to Tab delimeted text file.
    2. Execute bulk insert...

    bulk insert fte_import from 'd:\path_to_file\file.txt'
    with
    (
    firstrow =2, --start at row 2 and eliminate
    header
    formatfile='d:\path_to_format_file\import.fmt'
    )

    3. I then receive the error 'Could not bulk insert.
    Prefix length, field length, or terminator required for
    source column 9 in format
    file 'd:\path_to_format_file\import.fmt'.'


    However, column 9 is the column that does not exist in my
    import file (it is an identity datatype). I thought the
    way to have a format file tell the import process to skip
    a column was to "A prefix length of 0, field length of 0,
    and no field terminator (BOL)" for the column you want to
    ignore.

    What am I not understanding and/or doing wrong?

    Thanks in advance for any and all help with this.

    Robert



    Format file below...
    8.0
    9
    1 SQLNCHAR 0
    510 "\t" 1
    LastName SQL_Latin1_General_CP1_CI_AS
    2 SQLNCHAR 0
    510 "\t" 2
    FirstName SQL_Latin1_General_CP1_CI_AS
    3 SQLNCHAR 0
    510 "\t" 3
    BranchID SQL_Latin1_General_CP1_CI_AS
    4 SQLNCHAR 0
    510 "\t" 4
    BranchName SQL_Latin1_General_CP1_CI_AS
    5 SQLNCHAR 0
    0 "\t" 5
    PhoneNumber SQL_Latin1_General_CP1_CI_AS
    6 SQLNCHAR 0
    510 "\t" 6
    Location SQL_Latin1_General_CP1_CI_AS
    7 SQLNCHAR 0
    0 "\n" 9
    TimeZone SQL_Latin1_General_CP1_CI_AS
    8 SQLNCHAR 0
    510 "\t" 7
    emailAddress SQL_Latin1_General_CP1_CI_AS
    9 SQLNCHAR 0
    0 "" 8
    ID SQL_Latin1_General_CP1_CI_AS

    Robert Taylor Guest

  2. #2

    Default format file and bcp/bulk insert

    I'm sorry. SQL 2000.


    Thanks,

    Robert

    >-----Original Message-----
    >I'm trying to use a format file for importing in a data
    >file that has fewer columns than my table I'm importing
    >to. I've gone through BOL, and it looked really simply
    to
    >set one up to do this, but I keep encountering problems.
    >
    >Here are the steps I'm using...
    >1. Export file from Excel to Tab delimeted text file.
    >2. Execute bulk insert...
    >
    >bulk insert fte_import from 'd:\path_to_file\file.txt'
    > with
    > (
    > firstrow =2, --start at row 2 and eliminate
    >header
    > formatfile='d:\path_to_format_file\import.fmt'
    >)
    >
    >3. I then receive the error 'Could not bulk insert.
    >Prefix length, field length, or terminator required for
    >source column 9 in format
    >file 'd:\path_to_format_file\import.fmt'.'
    >
    >
    >However, column 9 is the column that does not exist in my
    >import file (it is an identity datatype). I thought the
    >way to have a format file tell the import process to skip
    >a column was to "A prefix length of 0, field length of 0,
    >and no field terminator (BOL)" for the column you want to
    >ignore.
    >
    >What am I not understanding and/or doing wrong?
    >
    >Thanks in advance for any and all help with this.
    >
    >Robert
    >
    >
    >
    >Format file below...
    >8.0
    >9
    >1 SQLNCHAR 0
    >510 "\t" 1
    >LastName SQL_Latin1_General_CP1_CI_AS
    >2 SQLNCHAR 0
    >510 "\t" 2
    >FirstName SQL_Latin1_General_CP1_CI_AS
    >3 SQLNCHAR 0
    >510 "\t" 3
    >BranchID SQL_Latin1_General_CP1_CI_AS
    >4 SQLNCHAR 0
    >510 "\t" 4
    >BranchName SQL_Latin1_General_CP1_CI_AS
    >5 SQLNCHAR 0
    >0 "\t" 5
    >PhoneNumber SQL_Latin1_General_CP1_CI_AS
    >6 SQLNCHAR 0
    >510 "\t" 6
    >Location SQL_Latin1_General_CP1_CI_AS
    >7 SQLNCHAR 0
    >0 "\n" 9
    >TimeZone SQL_Latin1_General_CP1_CI_AS
    >8 SQLNCHAR 0
    >510 "\t" 7
    >emailAddress SQL_Latin1_General_CP1_CI_AS
    >9 SQLNCHAR 0
    >0 "" 8
    >ID SQL_Latin1_General_CP1_CI_AS
    >
    >.
    >
    Robert Taylor Guest

  3. #3

    Default Re: format file and bcp/bulk insert

    Robert Taylor (roberttrtnetworks.com) writes:
    > However, column 9 is the column that does not exist in my
    > import file (it is an identity datatype). I thought the
    > way to have a format file tell the import process to skip
    > a column was to "A prefix length of 0, field length of 0,
    > and no field terminator (BOL)" for the column you want to
    > ignore.
    I would leave the column entirely from the format file.

    There is another thing which is funky with your format file:

    > 7 SQLNCHAR 0
    > 0 "\n" 9
    > TimeZone SQL_Latin1_General_CP1_CI_AS
    > 8 SQLNCHAR 0
    > 510 "\t" 7
    > emailAddress SQL_Latin1_General_CP1_CI_AS
    You have a newline after field 7, and then comes emailAddress on
    the next line, with a tab closing that field. Haven't you swapped
    the fields or the terminators here?

    --
    Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]

    Books Online for SQL Server SP3 at
    [url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]
    Erland Sommarskog Guest

  4. #4

    Default Re: format file and bcp/bulk insert

    Erland,

    Thanks. I had missed that after changing it around so
    many times. That did fix it so that the data appears to
    come in. The problem now seems to be the encoding. I get
    unrecognizable characters in my db now. The Excel file
    starts off being in ANSI format. Should I be
    using 'SQL_Latin1_General_CP1_CI_AS' as my encoding, or
    something else?

    Many, many thanks. I have avoided using format files in
    the past because of problems like this, but I have to
    understand them for this project and I appreciate your
    time and help.

    Robert

    >-----Original Message-----
    >Robert Taylor (roberttrtnetworks.com) writes:
    >> However, column 9 is the column that does not exist in
    my
    >> import file (it is an identity datatype). I thought
    the
    >> way to have a format file tell the import process to
    skip
    >> a column was to "A prefix length of 0, field length of
    0,
    >> and no field terminator (BOL)" for the column you want
    to
    >> ignore.
    >
    >I would leave the column entirely from the format file.
    >
    >There is another thing which is funky with your format
    file:
    >
    >
    >> 7 SQLNCHAR 0
    >> 0 "\n" 9
    >> TimeZone SQL_Latin1_General_CP1_CI_AS
    >> 8 SQLNCHAR 0
    >> 510 "\t" 7
    >> emailAddress SQL_Latin1_General_CP1_CI_AS
    >
    >You have a newline after field 7, and then comes
    emailAddress on
    >the next line, with a tab closing that field. Haven't you
    swapped
    >the fields or the terminators here?
    >
    >--
    >Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]
    >
    >Books Online for SQL Server SP3 at
    >[url]http://www.microsoft.com/sql/techinfo/productdoc/2000/book[/url]
    s.asp
    >.
    >
    Robert Taylor Guest

  5. #5

    Default Re: format file and bcp/bulk insert

    Robert Taylor (roberttrtnetworks.com) writes:
    > Thanks. I had missed that after changing it around so
    > many times. That did fix it so that the data appears to
    > come in. The problem now seems to be the encoding. I get
    > unrecognizable characters in my db now. The Excel file
    > starts off being in ANSI format. Should I be
    > using 'SQL_Latin1_General_CP1_CI_AS' as my encoding, or
    > something else?
    Right now, you are saying that the input file is Unicode. Now you say
    ANSI format. If the file has 8-bit chars, you should use SQLCHAR and
    not SQLNCHAR.

    As for the collation column, you should probably use the collation of
    the target column in the database.


    --
    Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]

    Books Online for SQL Server SP3 at
    [url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]
    Erland Sommarskog Guest

  6. #6

    Default Re: format file and bcp/bulk insert

    Erland,

    'If the file has 8-bit chars, you should use SQLCHAR and
    not SQLNCHAR.'

    Thanks, that did it. Unfortunately I had built the format
    file using bcp, from an export of the same table. But, I
    hadn't really thought about the file being in ASCII not,
    Unicode. Thanks for your time, help and patience. This
    is going to save me a lot of time.

    Robert

    >-----Original Message-----
    >Robert Taylor (roberttrtnetworks.com) writes:
    >> Thanks. I had missed that after changing it around so
    >> many times. That did fix it so that the data appears
    to
    >> come in. The problem now seems to be the encoding. I
    get
    >> unrecognizable characters in my db now. The Excel file
    >> starts off being in ANSI format. Should I be
    >> using 'SQL_Latin1_General_CP1_CI_AS' as my encoding, or
    >> something else?
    >
    >Right now, you are saying that the input file is Unicode.
    Now you say
    >ANSI format. If the file has 8-bit chars, you should use
    SQLCHAR and
    >not SQLNCHAR.
    >
    >As for the collation column, you should probably use the
    collation of
    >the target column in the database.
    >
    >
    >--
    >Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]
    >
    >Books Online for SQL Server SP3 at
    >[url]http://www.microsoft.com/sql/techinfo/productdoc/2000/book[/url]
    s.asp
    >.
    >
    Robert Taylor Guest

Similar Threads

  1. Date Format Problem - SQL Server Insert From Web Application
    By Steve in forum ASP.NET Web Services
    Replies: 8
    Last Post: May 1st, 02:37 PM
  2. Replies: 1
    Last Post: June 30th, 08:47 PM
  3. File Viewer / Bloated file sizes / What is the best file format?
    By Martin_Folley@adobeforums.com in forum Adobe Illustrator Windows
    Replies: 1
    Last Post: June 27th, 05:24 PM
  4. BULK INSERT exercises from exam 70-229 training kit don't work
    By gene in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 14th, 12:00 PM
  5. Eliminate logging while doing Bulk Insert
    By RS in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 10th, 03:12 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139