Microsoft KB Archive/67658

= INFO: Extracting Month and Year from ASCII File with BCP =

Article ID: 67658

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition

-



This article was previously published under Q67658



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 &quot;mm/dd/yy&quot; 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    &quot;&quot;   1    f1 2    SYBCHAR   0    2    &quot;&quot;   2    f2 3    SYBCHAR   0    4    &quot;&quot;   0    Dummy 4   SYBCHAR   0    2    &quot;&quot;   3    f3 5    SYBCHAR   0    5    &quot;\r\n&quot;    4    f4

Keywords: kbinfo KB67658

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.