BCP Format File

[Rathtap]

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?

[Erland Sommarskog]

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, som...@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Rathtap]

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:
36415 25.00
52759 24.50
52760 12.77
52775 24.03
74285 16.88
74436 7.84
80003 7.58
80004 8.00
80005 8.92
80006 8.94
80007 9.32
80008 9.66
80009 9.90
80010 9.90
80011 10.08
80012 10.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:
52759 24.50 24.50 24.50
52760 12.77 12.77 12.77
52775 24.03 24.03 24.03
74285 16.88 16.88 16.88
74436 7.84 7.84 7.84
80003 7.58 7.58 7.58
80004 8.00 8.00 8.00
80005 8.92 8.92 8.92
80006 8.94 8.94 8.94
80007 9.32 9.32 9.32
80008 9.66 9.66 9.66
80009 9.90 9.90 9.90
80010 9.90 9.90 9.90
80011 10.08 10.08 10.08
80012 10.29 10.29 10.29
80016 12.05 12.05 12.05
80018 12.14 12.14 12.14
80019 12.61 12.61 12.61
Erland Sommarskog <som...@algonet.se> wrote in message news:<Xns93AEDF7A...@127.0.0.1>...

[Erland Sommarskog]

[posted and mailed, please reply in public]

Rathtap (amc...@yahoo.com) writes:
> 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.

Alright, there are two different file formats. I got the impression that
format varied within the same file.

It seems that you first need to analyse which sort of file you get, and
then use the appropriate format file.

The format file for two columns should be:

8.0
2
1 SQLCHAR 0 0 " " 1 code SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 0 "\r\n" 2 coderate SQL_Latin1_General_CP1_CI_AS

I am a little unclear if you get a four-column file if you want all
columns, or if you want to ignore the last two columns. To include the
data you would use this format file:

8.0
2
1 SQLCHAR 0 0 " " 1 code SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 0 " " 2 coderate SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 " " 3 coderate2 SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 0 "\r\n" 4 coderate3 SQL_Latin1_General_CP1_CI_AS

To exclude them, change 3 and 4 in the right column to 0, to indicate
that these columns are not to be imported.