Using a Format File to Selectively Copy Data

Using a bcp format file provides a way to selectively bulk copy data from a file to SQL Server. This allows the user to transfer data into a table when there is a mismatch between the data sent to the table and the columns available for the received data.

Sometimes a user's data file may not match the structure of the SQL Server table into which the data will go. For example, a table contains columns for an employee's ID, name, and department. The input file contains the employee's ID, name, department, and salary. By using a bcp format file, you can bulk copy only the necessary data, without having to delete the unnecessary data from the input file.

The following sections provide examples that demonstrate how to change the format file to simplify loading the data in the input file into the SQL Server table.

Using a Data File with Fewer Columns than the SQL Server Table

This example takes the data in the input file NEW_AUTH.DAT and loads it into the authors table in the pubs database. This is the data in the NEW_AUTH.DAT file:

777-77-7777,Chris,Smith,303 555-1213,Denver,CO,1
888-88-8888,John,Doe,206 555-1214,Seattle,WA,0
999-99-9999,Jane,Door,406 555-1234,Bozeman,MT,1

The structure of the input file differs from that of the authors table in three ways:

The first and last names are in reverse order from the order of the columns.
The input file does not contain an address field.
The input file does not contain a ZIP code field.

To enter this data into the SQL Server table, you must first create a format file for the authors table. To create the format file, type:

bcp pubs..authors out c:\authors.out /Sservername /Usa /Ppassword

The bcp utility displays a series of questions for each column of the authors table. Enter values for the prompts as shown below. Use a comma for the field terminator, and use a newline character (\n) for the field terminator on the contract column. Save this file as AUTHORS.FMT. You should see prompts similar to the following:

Enter the file storage type of field au_id [char]:
Enter prefix-length of field au_id [0]: 
Enter length of field au_id [11]:
Enter field terminator [none]: ,

Enter the file storage type of field au_lname [char]:
Enter prefix-length of field au_lname [0]: 
Enter length of field au_lname [40]:
Enter field terminator [none]: ,

Enter the file storage type of field au_fname [char]:
Enter prefix-length of field au_fname [0]: 
Enter length of field au_fname [20]:
Enter field terminator [none]: ,

Enter the file storage type of field phone [char]:
Enter prefix-length of field phone [0]: 
Enter length of field phone [12]:
Enter field terminator [none]: ,

Enter the file storage type of field address [char]:
Enter prefix-length of field address [0]: 
Enter length of field address [40]: 0
Enter field terminator [none]: 

Enter the file storage type of field city [char]:
Enter prefix-length of field city [0]: 
Enter length of field city [20]:
Enter field terminator [none]: ,

Enter the file storage type of field state [char]:
Enter prefix-length of field state [0]: 
Enter length of field state [2]:
Enter field terminator [none]: ,

Enter the file storage type of field zip [char]:
Enter prefix-length of field zip [0]: 
Enter length of field zip [5]: 0
Enter field terminator [none]: 

Enter the file storage type of field contract [bit]: char
Enter prefix-length of field contract [0]: 
Enter length of field contract [1]:
Enter field terminator [none]: \n

Do you want to save this format information in a file? [Y/n] y
Host filename [bcp.fmt]: authors.fmt

Starting copy...

23 rows copied. 
Network packet size (bytes): 4096
Clock time (ms.): total = 451   Avg = 19   (51.00 rows per sec.)

This creates the AUTHORS.FMT format file, which looks like this:

6.0
9

au_id
au_lname
au_fname
phone
address
city
state
zip
contract

For an explanation of each area in the format file, see Using the bcp Format File, earlier in this chapter.

The format file contains all the information necessary to copy the data from the input file into the SQL Server table. Note that by entering a field length of 0 and no field terminator for the address and zip fields, you have in effect told the format file that those columns do not exist in the input file. However, you still need to edit the format file to show that the au_lname and au_fname columns are reversed in the input file and that no data will be loaded into the address and zip columns of the table. To do this:

Use a text editor to reverse the server column numbers for the au_lname and au_fname columns.
Set the server column numbers for the address and zip columns to 0.
Save the modified format file under the same name (AUTHORS.FMT).

The modified format file should look like this:

6.0
9

au_id
au_lname
au_fname
phone
address
city
state
zip
contract

The bcp utility can now use this new format file to copy the data in the input file into the authors table using the following command:

bcp pubs..authors in c:\new_auth.dat /fc:\authors.fmt 
/Sservername /Usa /Ppassword

Note Since the address and zip columns are not present in the input file, those columns contain NULL in the SQL Server table. Therefore, the authors table must allow null values in those columns.

Skipping Over Columns in the Data File

In this example, the data in the input file is slightly different from the first example because it contains all the necessary data for the authors table, including the address and zip fields. The following is the data in the NEW_AUTH.DAT file for this example:

777-77-7777,Chris,Smith,303 555-1213,27 College Ave,Denver,CO,80220,1
888-88-8888,John,Doe,206 555-1214,123 Maple Street,Seattle,WA,95099,0
999-99-9999,Jane,Door,406 555-1234,45 East Main,Bozeman,MT,59715,1

Assume that, although the address and zip fields are present in the input file, they will not be included in the SQL Server table. In addition, the order of the data needs to be reversed for the au_fname and au_lname columns. Perform the steps shown in the previous example to get the format file for the authors table. However, since the input file contains data for the address and zip fields, use a comma for the field terminator for each of them. The format file should look like this:

6.0
9

au_id
au_lname
au_fname
phone
address
city
state
zip
contract

Edit the format file to reverse the order of the au_fname and au_lname columns. In addition, set the server column order numbers for the address and zip columns to 0. The modified format file should look like this:

6.0
9

au_id
au_lname
au_fname
phone
address
city
state
zip
contract

This format file can now be used to copy in the data in the NEW_AUTH.DAT data file using the same bcp command as the previous example:

bcp pubs..authors in c:\new_auth.dat /fc:\authors.fmt 
/Sservername /Usa /Ppassword

Again, since the address and zip fields will not be copied into the authors table, those columns will contain NULL in the SQL Server table.

Using a Data File with More Columns Than the SQL Server Table

In this example, the data in the input file is the same as that in the previous example, except that the input file contains two additional fields: age and salutation. Since the authors table does not have columns for this data to go into, the format file must be changed to allow bcp to read the additional fields from the input file, but not to copy the data into the SQL Server table. This is the data in the input file:

777-77-7777,Chris,Smith,303 555-1213,27 College Ave,Denver,CO,80220,1,28,Ms.
888-88-8888,John,Doe,206 555-1214,123 Maple Street,Seattle,WA,95099,0,35,Mr.
999-99-9999,Jane,Door,406 555-1234,45 East Main,Bozeman,MT,59715,1,33,Mrs.

The same format file from the end of the previous example can be used:

6.0
9

au_id
au_lname
au_fname
phone
address
city
state
zip
contract

The format file must be edited to reflect the addition of the new columns. The second line of the format file, which specifies the number of columns, must be changed to 11, since there are 11 fields now in the input file. The field terminator for the contract column must be changed from "\r\n" to ",", since it is no longer the last field in the file. Finally, two new rows are added to the end of the format file to provide the information for the additional fields. The final format file should look like this:

6.0
11

au_id
au_lname
au_fname
phone
address
city
state
zip
contract
age
salutation

This format file can now be used to copy in the necessary data from the input file, with bcp ignoring the last two fields in the input file, using the same bcp command as the previous examples:

bcp pubs..authors in c:\new_auth.dat /fc:\authors.fmt 
/Sservername /Usa /Ppassword

1	SQLCHAR	11	","	1	au_id
2	SQLCHAR	40	","	2	au_lname
3	SQLCHAR	20	","	3	au_fname
4	SQLCHAR	12	","	4	phone
5	SQLCHAR	0	""	5	address
6	SQLCHAR	20	","	6	city
7	SQLCHAR	2	","	7	state
8	SQLCHAR	0	""	8	zip
9	SQLCHAR	1	"\r\n"	9	contract