SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


bcpin


bcpin

Author
Message
faizal.h
faizal.h
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 5
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
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9655 Visits: 9759
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.


faizal.h
faizal.h
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 5
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
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9655 Visits: 9759
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.


faizal.h
faizal.h
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 5
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....
---------------------------------
year month misref template costcode edom rdom country branch base gfcid subprod misccy actline eop avg rev pool
2008 2 21938 99999 GB0017 GB 3 990000 190990 USD 43338 0 0 -1 0
2008 2 21938 99999 GB0168 GB 3 990000 170610 USD 43338 0 0 -1 0
2008 2 21938 99999 GB0235 GB 3 990000 190990 USD 43338 0 0 -606 0
2008 2 21938 99999 GB0271 GB 3 990000 170120 USD 43338 0 0 -2 0
2008 2 21938 99999 GB0285 GB 3 990000 198526 USD 43338 0 0 -57 0
2008 2 21938 99999 GB0411 GB 3 990000 200250 USD 43338 0 0 -274467 0
2008 2 21938 99999 GB0414 GB 3 990000 203089 USD 43338 0 0 -74 0
2008 2 21938 99999 GB0421 GB 3 990000 203545 USD 43338 0 0 -92 0
2008 2 21938 99999 GB0431 GB 3 990000 203037 USD 43338 0 0 -1054 0
2008 2 21938 99999 GB0432 GB 3 990000 203036 USD 43338 0 0 -1297 0
---------------------------------------------------------------------

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
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9655 Visits: 9759
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.


faizal.h
faizal.h
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 5
David,
Thank you very much . Its working fine .

Regards,
Faizal Ahmed.H
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search