bcpin

  • While bulk copying datas from the notepad file to the database using "bcp in", i want to remove the first row (i.e, header part of the data file ). Please provide suggestions to write the format file such that header should not copied to the database.

    Thanks in Advance,

    Faizal Ahmed

  • use [-F firstrow] of bcp ie -F2

    or FIRSTROW if using BULK INSERT

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Many Thanks for ur reply

    All,

    I have tried using bcp command -F 2 , but this command is ignoring header part along with first data row also.Provide me solution such that only header part should be ignored but not first data row.

    Thanks in advance

  • Curious :ermm:

    I have used those parameters for such a purpose without any problems.

    Can you post your data structure, sample data and the format file

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Many Thanks for the reply again ...

    fmt structure is as follows..

    -------------------------

    8.0

    15

    1 SQLCHAR 0 4 "\t" 1 mis_year SQL_Latin1_General_CP1_CI_AS

    2 SQLCHAR 0 2 "\t" 2 mis_month SQL_Latin1_General_CP1_CI_AS

    3 SQLCHAR 0 5 "\t" 3 misref SQL_Latin1_General_CP1_CI_AS

    4 SQLCHAR 0 5 "\t" 4 template SQL_Latin1_General_CP1_CI_AS

    5 SQLCHAR 0 6 "\t\t\t" 5 costcode SQL_Latin1_General_CP1_CI_AS

    6 SQLCHAR 0 2 "\t" 6 country SQL_Latin1_General_CP1_CI_AS

    7 SQLCHAR 0 1 "\t" 7 branch SQL_Latin1_General_CP1_CI_AS

    8 SQLCHAR 0 6 "\t\t" 8 base SQL_Latin1_General_CP1_CI_AS

    9 SQLCHAR 0 6 "\t" 9 subprod SQL_Latin1_General_CP1_CI_AS

    10 SQLCHAR 0 3 "\t" 10 misccy SQL_Latin1_General_CP1_CI_AS

    11 SQLCHAR 0 5 "\t" 11 actline SQL_Latin1_General_CP1_CI_AS

    12 SQLCHAR 0 10 "\t" 12 eop SQL_Latin1_General_CP1_CI_AS

    13 SQLCHAR 0 10 "\t" 13 average SQL_Latin1_General_CP1_CI_AS

    14 SQLCHAR 0 9 "\t" 14 rev SQL_Latin1_General_CP1_CI_AS

    15 SQLCHAR 0 1 "\r" 15 pool SQL_Latin1_General_CP1_CI_AS

    Data file structure is as follows....

    ---------------------------------

    yearmonthmisreftemplatecostcodeedomrdomcountrybranchbasegfcidsubprodmisccyactlineeopavgrevpool

    200822193899999GB0017GB3990000190990USD4333800-10

    200822193899999GB0168GB3990000170610USD4333800-10

    200822193899999GB0235GB3990000190990USD4333800-6060

    200822193899999GB0271GB3990000170120USD4333800-20

    200822193899999GB0285GB3990000198526USD4333800-570

    200822193899999GB0411GB3990000200250USD4333800-2744670

    200822193899999GB0414GB3990000203089USD4333800-740

    200822193899999GB0421GB3990000203545USD4333800-920

    200822193899999GB0431GB3990000203037USD4333800-10540

    200822193899999GB0432GB3990000203036USD4333800-12970

    ---------------------------------------------------------------------

    In my Data file column fields like edom,rdom and gfcid dont have any values and the format file is expecting three tabs and two tabs whenever the field name occurs ... I have found out the cause of the problem also . Please help me in changing the format file such that only header part should be skipped without a loss in the data .

    Thanks in advance,

    Faizal

  • Based on your data your format file should be this

    8.0

    18

    1 SQLCHAR 0 4 "\t" 1 mis_year SQL_Latin1_General_CP1_CI_AS

    2 SQLCHAR 0 2 "\t" 2 mis_month SQL_Latin1_General_CP1_CI_AS

    3 SQLCHAR 0 5 "\t" 3 misref SQL_Latin1_General_CP1_CI_AS

    4 SQLCHAR 0 5 "\t" 4 template SQL_Latin1_General_CP1_CI_AS

    5 SQLCHAR 0 6 "\t" 5 costcode SQL_Latin1_General_CP1_CI_AS

    6 SQLCHAR 0 0 "\t" 0 edom SQL_Latin1_General_CP1_CI_AS

    7 SQLCHAR 0 0 "\t" 0 rdom SQL_Latin1_General_CP1_CI_AS

    8 SQLCHAR 0 2 "\t" 6 country SQL_Latin1_General_CP1_CI_AS

    9 SQLCHAR 0 1 "\t" 7 branch SQL_Latin1_General_CP1_CI_AS

    10 SQLCHAR 0 6 "\t" 8 base SQL_Latin1_General_CP1_CI_AS

    11 SQLCHAR 0 0 "\t" 0 gfcid SQL_Latin1_General_CP1_CI_AS

    12 SQLCHAR 0 6 "\t" 9 subprod SQL_Latin1_General_CP1_CI_AS

    13 SQLCHAR 0 3 "\t" 10 misccy SQL_Latin1_General_CP1_CI_AS

    14 SQLCHAR 0 5 "\t" 11 actline SQL_Latin1_General_CP1_CI_AS

    15 SQLCHAR 0 10 "\t" 12 eop SQL_Latin1_General_CP1_CI_AS

    16 SQLCHAR 0 10 "\t" 13 average SQL_Latin1_General_CP1_CI_AS

    17 SQLCHAR 0 9 "\t" 14 rev SQL_Latin1_General_CP1_CI_AS

    18 SQLCHAR 0 1 "\r" 15 pool SQL_Latin1_General_CP1_CI_AS

    and this in conjunction with the -F2 parameter to bcp

    Far away is close at hand in the images of elsewhere.
    Anon.

  • David,

    Thank you very much . Its working fine .

    Regards,

    Faizal Ahmed.H

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply