Programming4us
         
 
 
SQL Server

SQL Server Integration Services : Using bcp (part 5)

11/30/2010 3:13:46 PM
Field Terminators

If you are not making use of fixed-width fields or length prefixes, you must use a field terminator to indicate the character(s) that separates fields; for the last field in the data row, you must also indicate which character(s) ends the line.

bcp recognizes the indicators for special characters shown in Table 5.

Table 5. bcp Indicators for Special Characters
TerminatorEscape Code
Tab\t
Backslash\\
Null terminator\0
Newline\n
Carriage return\r

You cannot use spaces as terminators, but you can use any other printable characters. You should choose field and row terminators that make sense for your data. Obviously, you should not use any character you are trying to load. You must combine the \r and \n characters to get your data into an ASCII data file with each row on its own line.

Tip

By specifying the –t and –r switches, you can override the defaults that appear for the prompts during interactive bcp.


Note

You can specify terminators for data copied in native format. You should be careful if you decide to go this route; the accepted approach is to use lengthy prefixes.


The prefix length, field length, and terminator values interact with one another. In the following examples, T indicates the terminator character(s), P indicates the prefix length, and S indicates space padding.

For data of type char, the data file has the following repeating pattern:

 Prefix Length=0Prefix Length=1, 2, 4
No TerminatorstringSstringSPstringSPstringS
TerminatorstringSTstringSTPstringSTPstringST

For data of other types converted to char, the data file has the following repeating pattern:

 Prefix Length=0Prefix Length=1, 2, 4
No terminatorstringSstringSPstringPstring
TerminatorstringTstringTPstringTPstringT

The next few sections examine how to load data into tables when there are differences in column number and layout.

Different Numbers of Columns in a File and Table

If you want to load data into tables when you have fewer fields in the data file than in the table, you have to “dummy up” an extra line in your format file.

Let’s suppose you want to load a data file that is missing most of the address information for each customer (into a customer table of some kind that has full address columns in it). To do this, you create a format file for this table by using the format option with bcp. With this format file, you can still load this abbreviated data easily. Suppose that the data file looks like this:

WELLI    Wellington Importadora    Jane Graham    Sales (14)555-8122
(14)555-8111
WHITC White Clover Markets Donald Bertucci Owner (206)555-4112
(206)555-4113

To introduce a dummy value for the missing ones, in the format file, you need to make the prefix and data lengths 0 and set the field terminator to nothing (""). The modified format file should look like this:

10.0
11
1 SQLCHAR 0 10 "\t" 1 CustomerID SQL_Latin1_General_
CP1_CI_AS
2 SQLCHAR 0 80 "\t" 2 CompanyName SQL_Latin1_General_
CP1_CI_AS
3 SQLCHAR 0 60 "\t" 3 ContactName SQL_Latin1_General_
CP1_CI_AS
4 SQLCHAR 0 60 "\t" 4 ContactTitle SQL_Latin1_General_
CP1_CI_AS
5 SQLCHAR 0 0 "" 5 Address SQL_Latin1_General_
CP1_CI_AS
6 SQLCHAR 0 0 "" 6 City SQL_Latin1_General_
CP1_CI_AS
7 SQLCHAR 0 0 "" 7 Region SQL_Latin1_General_
CP1_CI_AS
8 SQLCHAR 0 0 "" 8 PostalCode SQL_Latin1_General_
CP1_CI_AS
9 SQLCHAR 0 0 "" 9 Country SQL_Latin1_General_
CP1_CI_AS
10 SQLCHAR 0 48 "\t" 10 Phone SQL_Latin1_General_
CP1_CI_AS
11 SQLCHAR 0 48 "\r\n" 11 Fax SQL_Latin1_General_
CP1_CI_AS


Now bcp can load the data file by using this new format file, with the Address, City, Region, PostalCode, and Country columns containing NULL values for the new rows.

For data files that have more fields than the table has columns, you change the format file to add additional lines of information. Suppose that your customer data file contains an additional CreditStatus value at the end (shown here in bold italic):

WELLI    Wellington Importadora    Martin Sommer  Sales Manager Rua do Mercado,
12 Resende SP 08737-363 Uraguay (14) 555-8122 NULL 1
WELP Well Drilling P Thierry Gerardin Sales Manager Rue de Vaugirard,
997 Paris FR 08737-363 France (11) 555-8122 NULL 1
WF WF Enterprises Yves Moison Sales Manager Rue de Sevres,
4123 Paris FR 08737-363 France (14) 555-8122 NULL 1
WGZR Wellsley Granite Jack McElreath Sales Manager Hillsboro,
131 Hillsboro MA 08737-363 USA (781) 555-8122 NULL 1
WHITC White Clover Markets Scott Smith Owner 305 - 14th Ave. S.
Suite 3B Boston MA 98128 USA (508) 555-4112 (508) 555-4115 2



You need to modify a format file in two important areas: you change the second line to reflect the actual number of values, and you add new lines for the extra column in the file that is not in the table (from 11 to 12 entries). Notice that the column position has a value of 0 to indicate the absence of a column in the table. The result is that your source data file will import all data into the table, except the extra field (that is, the CreditStatus field).

Thus, the modified format file looks like this (where the bold italic indicates the changes made):

10.0
12
1 SQLCHAR 0 10 "\t" 1 CustomerID SQL_Latin1_General_
CP1_CI_AS
2 SQLCHAR 0 80 "\t" 2 CompanyName SQL_Latin1_General_
CP1_CI_AS
3 SQLCHAR 0 60 "\t" 3 ContactName SQL_Latin1_General_
CP1_CI_AS
4 SQLCHAR 0 60 "\t" 4 ContactTitle SQL_Latin1_General_
CP1_CI_AS
5 SQLCHAR 0 120 "\t" 5 Address SQL_Latin1_General_
CP1_CI_AS
6 SQLCHAR 0 30 "\t" 6 City SQL_Latin1_General_
CP1_CI_AS
7 SQLCHAR 0 30 "\t" 7 Region SQL_Latin1_General_
CP1_CI_AS
8 SQLCHAR 0 20 "\t" 8 PostalCode SQL_Latin1_General_
CP1_CI_AS
9 SQLCHAR 0 30 "\t" 9 Country SQL_Latin1_General_
CP1_CI_AS
10 SQLCHAR 0 48 "\t" 10 Phone SQL_Latin1_General_
CP1_CI_AS
11 SQLCHAR 0 48 "\t" 11 Fax SQL_Latin1_General_
CP1_CI_AS
12 SQLCHAR 0 1 "\r\n" 0 CreditStatus SQL_Latin1_General_
CP1_CI_AS


These two examples show you the possibilities that the format file offers for customizing the loading and unloading of data.
Renumbering Columns

Using the techniques described in the section “Different Numbers of Columns in a File and Table,” you can also handle data file fields that are in different orders than the target tables. All you need to do is change the column order number to reflect the desired sequence of the columns in the table. The fields are then automatically mapped to the corresponding columns in the table.

For example, suppose that a customer data file you got from another source system came with the fields in this order:

  1. Address

  2. City

  3. Country

  4. PostalCode

  5. Region

  6. CompanyName

  7. ContactName

  8. ContactTitle

  9. Fax

  10. Phone

  11. CustomerID

The SQL Server table has columns in a different order. To load your data file into this table, you modify the format file to look like this (where the bold italic indicates the changes made):

10.0
11
1 SQLCHAR 0 10 "\t" 11 CustomerID SQL_Latin1_General_
CP1_CI_AS
2 SQLCHAR 0 80 "\t" 6 CompanyName SQL_Latin1_General_
CP1_CI_AS
3 SQLCHAR 0 60 "\t" 7 ContactName SQL_Latin1_General_
CP1_CI_AS
4 SQLCHAR 0 60 "\t" 8 ContactTitle SQL_Latin1_General_
CP1_CI_AS
5 SQLCHAR 0 120 "\t" 1 Address SQL_Latin1_General_
CP1_CI_AS
6 SQLCHAR 0 30 "\t" 2 City SQL_Latin1_General_
CP1_CI_AS
7 SQLCHAR 0 30 "\t" 5 Region SQL_Latin1_General_
CP1_CI_AS
8 SQLCHAR 0 20 "\t" 4 PostalCode SQL_Latin1_General_
CP1_CI_AS
9 SQLCHAR 0 30 "\t" 3 Country SQL_Latin1_General_
CP1_CI_AS
10 SQLCHAR 0 48 "\t" 10 Phone SQL_Latin1_General_
CP1_CI_AS
11 SQLCHAR 0 48 "\r\n" 9 Fax SQL_Latin1_General_
CP1_CI_AS


The principal point to remember with the format file is that all but the last three columns deal with the data file. The last three columns deal with the database table.

Using Views

bcp can use views to export data from a database. This means an export of data can be a result set of data from multiple tables (and with distributed queries, even multiple servers).

You can also use a view with bcp to load data back into tables. However, as is the case with normal T-SQL inserts, you can load into only one of the underlying tables at a time.

Other -----------------
- SQL Server Integration Services : Connection Projects in Visual Studio
- SQL Server Integration Services : The Package Execution Utility (part 3) - The dtutil Utility
- SQL Server Integration Services : The Package Execution Utility (part 2) - Running Packages
- SQL Server Integration Services : The Package Execution Utility (part 1)
- SQL Server Integration Services : The SSIS Designer
- SQL Server Integration Services : Running the SSIS Wizard
- SQL Server Integration Services : A Data Transformation Requirement
- SQL Server 2008 : SSIS Tools and Utilities
- SQL Server 2008 : SSIS Architecture and Concepts
- SQL Server 2008 : SQL Server Integration Services - SSIS Basics
- Defensive Error Handling : Using Transactions and XACT_ABORT to Handle Errors
- Managing Security Within the Database Engine : Securables
- Managing Security Within the Database Engine : Database Security
- Managing Security Within the Database Engine : Creating SQL Server Principals
- SQL Server 2008 : Performance Tuning - Locks, Blocking, and Deadlocks
- SQL Server 2008 : Performance Tuning - Tracing
- SQL Server 2008 : Implementing Error Handling - Managing and Raising User-Defined Errors
- SQL Server 2008 : Implementing Error Handling - Understanding Errors
- Implementing SQL Server Objects Using Managed Code (part 2)
- Implementing SQL Server Objects Using Managed Code (part 1)
 
 
Most View
- iPad SDK : Preparing Dudel for a New Tool (part 5) - Rendering Multiple Styles
- SOA with .NET and Windows Azure : WCF Discovery (part 1) - Discovery Modes
- jQuery 1.3 : Developing plugins - Method parameters
- Windows Phone 7 : Pinning Favorites to Start
- Sharepoint 2010 : Content Management - Importing a Term Set
- SharePoint 2010 : Create a Personal Site
- Windows 7 : Connecting to the Remote Desktop (part 1) - Making a Basic Connection
- Security in Cloud Computing (part 2) - Identity and Access Management
- Windows 7 : Working with Users and Groups from the Command Line
- What's New in SharePoint 2013 (part 1) - THE PROGRAMMING MODEL
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS