Sunday, August 06, 2006

Friday I had some need for using the Format files to load the data into the sql server using the BCP Utility.I was getting some weired errors.(EOF File Error,Data Truncation Error,I/O Error,etc).I googled to find out the reason for those Errors and I found few reasons.
So I thoght of writing about using the format files in BCP Utility.

Creating the Format Files:
The easiest way I found is to Create the Format file using the Existing Table.Then it's easy to Edit the format file as the requirement changes.

a.Creating the format file with char Dataset:
xp_cmdshell 'bcp Test.dbo.Formatfile format nul -c -f G:\FormatFile.fmt -T'

Sample Format File:
9.0
13
1 SQLCHAR 0 16 "\t" 1 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 3 "\t" 2 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 35 "\t" 3 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 3 "\t" 4 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 24 "\t" 5 EffectiveStartDate ""
6 SQLCHAR 0 50 "\t" 6 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 3 "\t" 7 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 3 "\t" 8 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 255 "\t" 9 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 3 "\t" 10 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 255 "\t" 11 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 30 "\t" 12 FeePerUnit ""
13 SQLCHAR 0 3 "\r\n" 13 CurrencyCode SQL_Latin1_General_CP1_CI_AS

b.Creating the format file for Unicode DataSet:
xp_cmdshell 'bcp Test.dbo.Formatfile format nul -w -f G:\FormatFile.fmt -T'

9.0
13
1 SQLNCHAR 0 32 "\t\0" 1 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 6 "\t\0" 2 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 70 "\t\0" 3 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 6 "\t\0" 4 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 48 "\t\0" 5 EffectiveStartDate ""
6 SQLNCHAR 0 100 "\t\0" 6 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 0 6 "\t\0" 7 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 6 "\t\0" 8 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 0 510 "\t\0" 9 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 6 "\t\0" 10 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 0 510 "\t\0" 11 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLNCHAR 0 60 "\t\0" 12 FeePerUnit ""
13 SQLNCHAR 0 6 "\r\0\n\0" 13 CurrencyCode SQL_Latin1_General_CP1_CI_AS
Here the data type SQLNCHAR is selected to support the UNICODE.This is done through the -w option while creating the format file.
c.Creating the Native Format Format Files:
xp_cmdshell 'bcp Test.dbo.Formatfile format nul -N -f G:\FormatFile.fmt -T'
9.0
13
1 SQLNCHAR 2 32 "" 1 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 2 6 "" 2 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 2 70 "" 3 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 2 6 "" 4 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLDATETIME 1 8 "" 5 EffectiveStartDate ""
6 SQLNCHAR 2 100 "" 6 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 2 6 "" 7 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 2 6 "" 8 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 2 510 "" 9 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 2 6 "" 10 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 2 510 "" 11 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLMONEY 1 8 "" 12 FeePerUnit ""
13 SQLNCHAR 2 6 "" 13 CurrencyCode SQL_Latin1_General_CP1_CI_AS

In the native format,you can see the different data types from the char and widechar types.
Once done with creating the format files,the format files can be used to import the data into the existing tables.This provides the greater flexibility in importing the selective columns into the existing tables.
1.SameNumber of columns in the DataFile and the Table structure:
If the same number of columns exist between the data file and the table structure,its easy to create the one to one Relationship between the Data File Structure and the Table Structure.
The format file definies the relationship between the datafile and the table structure.
The first column in the above structure defines the Order of the columns in the DataFile.
We have to specify the all the Columns available in the DataFile.But we can skip specifying all the columns available in the Table.
I am using the Data File with 13 columns and the format file created using the -w option above.
Create the CharFormat Table with 13 Columns using the following script.
CREATE TABLE [dbo].[charFormat](
[PartNumber] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramName] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISOCountryCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EffectiveStartDate] [datetime] NULL,
[EffectiveEndDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramLevelCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FeePerUnit] [money] NULL,
[CurrencyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
After creating the table,Insert the data into the table:
FormatFile:
xp_cmdshell 'bcp Test.dbo.Formatfile format nul -w -f G:\FormatFile.fmt -T'
Loding Process:
xp_cmdshell 'bcp Test.dbo.CharFormat in G:\Feeschedule.txt -fG:\FormatFile.fmt -T '
Options: in -- For the Input File Name
f -- FormatFile..It should be avialble at the time of loading process
T --Trusted Connection
2.Loading into the Table which has different Structure than the available DataFile:
DataFile: 13 Fields
Table : 12 Fields
In this case ,we have to change the Format File,so we can direct the data we want into the Required Columns into the Table.
create the table with the following scripts:
CREATE TABLE [dbo].lessfields
([ProgramCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramName] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISOCountryCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EffectiveStartDate] [datetime] NULL,
[EffectiveEndDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramLevelCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FeePerUnit] [money] NULL,
[CurrencyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Change the Format File:

9.0
13
1 SQLNCHAR 0 32 "\t\0" 0 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 6 "\t\0" 1 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 70 "\t\0" 2 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 6 "\t\0" 3 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 48 "\t\0" 4 EffectiveStartDate ""
6 SQLNCHAR 0 100 "\t\0" 5 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 0 6 "\t\0" 6 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 6 "\t\0" 7 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 0 510 "\t\0" 8 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 6 "\t\0" 9 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 0 510 "\t\0" 10 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLNCHAR 0 60 "\t\0" 0 FeePerUnit ""
13 SQLNCHAR 0 6 "\r\0\n\0" 0 CurrencyCode SQL_Latin1_General_CP1_CI_AS
I have routed the first column avilable in the data file to 0 th column in the Table Structure.So we neglect this column in the datafile.
If you want to neglect the data in the FIle ,Put 0 in the sixth column in the format File.
First column corresponds to the Datafile Structure.Sixth Column corrosponds to the Table Column Order.So its easy to direct the data file to the required Column structure in the Table Structure.
Loading the Data:
xp_cmdshell 'bcp Test.dbo.lessfields in G:\Feeschedule.txt -fG:\FormatFile.fmt -T '
3.Working with the Table,where Table is having more columns than the Data File:
Table: 15 Columns
DataFile: 13 Columns
Create the Table with the FollowingScripts:

CREATE TABLE [dbo].MoreFields(
Field1 int Identity(1,1),
Field2 nvarchar(255),
[PartNumber] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramName] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISOCountryCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EffectiveStartDate] [datetime] NULL,
[EffectiveEndDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramLevelCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FeePerUnit] [money] NULL,
[CurrencyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Change the Format FIle.We only need all the fields avialable in the DataFile. It's not required to specify all the fields available in the Table.
Change the FormatFile:
9.0
13
1 SQLNCHAR 0 32 "\t\0" 3 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 6 "\t\0" 4 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 70 "\t\0" 5 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 6 "\t\0" 6 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 48 "\t\0" 7 EffectiveStartDate ""
6 SQLNCHAR 0 100 "\t\0" 8 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 0 6 "\t\0" 9 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 6 "\t\0" 10 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 0 510 "\t\0" 11 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 6 "\t\0" 12 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 0 510 "\t\0" 13 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLNCHAR 0 60 "\t\0" 0 FeePerUnit ""
13 SQLNCHAR 0 6 "\r\0\n\0" 0 CurrencyCode SQL_Latin1_General_CP1_CI_AS
First Column in the Data File corresponds to the 3 rd column in the Table. when you specify 0 in the 6 th column,We are going to Neglect the Data available in the file for those columns.
Load the Data into the Table:

xp_cmdshell 'bcp Test.dbo.MoreFields in G:\Feeschedule.txt -fG:\FormatFile.fmt -T '
Format files provide greater flexibility when interacting with different systems.
Posted by at