INFO: Extracting Month and Year from ASCII File with BCP (67658)

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