INF: Extracting Month and Year from ASCII File with BCP

ID: Q67658

The information in this article applies to:

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        :

Last Reviewed: March 10, 1999