INF: Extracting Month and Year from ASCII File with BCP

Last reviewed: April 25, 1997
Article ID: Q67658

The information in this article applies to:

  - Microsoft SQL Server version 4.2 for OS/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

Additional query words:
Keywords : kbtool SSrvBCP
Version : 4.2
Platform : OS/2

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 25, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.