BCP Format File

A format file provides a way to bulk copy data selectively from a data
file to an instance of SQL Server. This allows the transfer of data to
a table when there is a mismatch between fields in the data file and
columns in the table.
I take it this assumes the number of fields in the data file will
always be constant. What if it is not?
My table has two columns but my datafile may have 2 to 4 columns and I
want to always select only the first two. Is there a way to set up the
format file to accomplish that?Rathtap (amcniw@.yahoo.com) writes:
> A format file provides a way to bulk copy data selectively from a data
> file to an instance of SQL Server. This allows the transfer of data to
> a table when there is a mismatch between fields in the data file and
> columns in the table.
> I take it this assumes the number of fields in the data file will
> always be constant. What if it is not?
> My table has two columns but my datafile may have 2 to 4 columns and I
> want to always select only the first two. Is there a way to set up the
> format file to accomplish that?

Don't know off hand. Can you post a sample of the input file, and a CREATE
TABLE statement for your table?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||This is the table:
CREATE TABLE [dbo].[CT_RATE_TEMP] (
[CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CODERATE] [smallmoney] NULL ,
[CODERATE2] [smallmoney] NULL ,
[CODERATE3] [smallmoney] NULL ,
[CODERATE4] [smallmoney] NULL
) ON [PRIMARY]
GO

And here is the import file:
3641525.00
5275924.50
5276012.77
5277524.03
7428516.88
744367.84
800037.58
800048.00
800058.92
800068.94
800079.32
800089.66
800099.90
800109.90
8001110.08
8001210.29
Here I would like Col1 of the file to map to the Code field and Col2
to CodeRate. CODERATE2,CODERATE3 and CODERATE4 should be null.

On the other hand there may be the following file to import where all
fields match, then there is no problem:
5275924.5024.5024.50
5276012.7712.7712.77
5277524.0324.0324.03
7428516.8816.8816.88
744367.847.847.84
800037.587.587.58
800048.008.008.00
800058.928.928.92
800068.948.948.94
800079.329.329.32
800089.669.669.66
800099.909.909.90
800109.909.909.90
8001110.0810.0810.08
8001210.2910.2910.29
8001612.0512.0512.05
8001812.1412.1412.14
8001912.6112.6112.61
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93AEDF7A33AYazorman@.127.0.0.1>...
> Rathtap (amcniw@.yahoo.com) writes:
> > A format file provides a way to bulk copy data selectively from a data
> > file to an instance of SQL Server. This allows the transfer of data to
> > a table when there is a mismatch between fields in the data file and
> > columns in the table.
> > I take it this assumes the number of fields in the data file will
> > always be constant. What if it is not?
> > My table has two columns but my datafile may have 2 to 4 columns and I
> > want to always select only the first two. Is there a way to set up the
> > format file to accomplish that?
> Don't know off hand. Can you post a sample of the input file, and a CREATE
> TABLE statement for your table?|||try adding default values to the null fields