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
format file and bcp/bulk insert
I'm sorry. SQL 2000.
Thanks,
Robert
Quote:
>-----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 Quote:
>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
>
>.
>
Re: format file and bcp/bulk insert
Robert Taylor (roberttrtnetworks.com) writes: Quote:
> 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:
Quote:
> 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]
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
Quote:
>-----Original Message-----
>Robert Taylor (roberttrtnetworks.com) writes:
Quote:
>> However, column 9 is the column that does not exist in
my Quote:
Quote:
>> import file (it is an identity datatype). I thought
the Quote:
Quote:
>> way to have a format file tell the import process to
skip Quote:
Quote:
>> a column was to "A prefix length of 0, field length of
0, Quote:
Quote:
>> and no field terminator (BOL)" for the column you want
to Quote:
>
>I would leave the column entirely from the format file.
>
>There is another thing which is funky with your format
file: Quote:
>
>
Quote:
>> 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 Quote:
>the next line, with a tab closing that field. Haven't you
swapped Quote:
>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
Re: format file and bcp/bulk insert
Robert Taylor (roberttrtnetworks.com) writes: Quote:
> 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]
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
Quote:
>-----Original Message-----
>Robert Taylor (roberttrtnetworks.com) writes:
Quote:
>> Thanks. I had missed that after changing it around so
>> many times. That did fix it so that the data appears
to Quote:
Quote:
>> come in. The problem now seems to be the encoding. I
get Quote:
Quote:
>> 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 Quote:
>ANSI format. If the file has 8-bit chars, you should use
SQLCHAR and Quote:
>not SQLNCHAR.
>
>As for the collation column, you should probably use the
collation of Quote:
>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
