INF: Extracting Month and Year from ASCII File with BCP

ID: Q67658

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.2

SUMMARY

The following information describes how to use BCP (bulk copy program) to extract just the month and the year from an ASCII file with a column that has a date of "mm/dd/yy" in it.

MORE INFORMATION

Using a format file, BCP can be instructed to skip information or entire fields. For example, suppose a table is defined as the following:


   create table bcpin
     ( f1 char(4),
       f2 char(2),
       f3 char(2),
       f4 char(5))

And you want to load in an ASCII file with the following information in it:


   test01/03/90hello
   test01/03/90hello
   test01/03/90hello
   test01/03/90hello
   test01/03/90hello

To do this, you need to edit a format file to include a field of the length that you want to skip, and then map this to column 0 (zero) on the server. For a more detailed explanation of this procedure, query on the following words in the Microsoft Knowledge Base:


   skip and bcp and identifies and prod(sqlndk)

The following is a sample format file:


4.0
5
1    SYBCHAR   0    4    ""   1    f1
2    SYBCHAR   0    2    ""   2    f2
3    SYBCHAR   0    4    ""   0    Dummy
4    SYBCHAR   0    2    ""   3    f3
5    SYBCHAR   0    5    "\r\n"    4    f4

Keywords : kbtool SSrvBCP
Version : 4.2
Platform : OS/2
Issue type :