InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 01:10:27 GMT20http://www.sqlservercentral.com/Forums/Topic667155-1437-1.aspxDavid,Thank you very much . Its working fine .Regards,Faizal Ahmed.HWed, 04 Mar 2009 04:54:11 GMTfaizal.hhttp://www.sqlservercentral.com/Forums/Topic667155-1437-1.aspxBased on your data your format file should be this8.0181 SQLCHAR 0 4 "\t" 1 mis_year SQL_Latin1_General_CP1_CI_AS2 SQLCHAR 0 2 "\t" 2 mis_month SQL_Latin1_General_CP1_CI_AS3 SQLCHAR 0 5 "\t" 3 misref SQL_Latin1_General_CP1_CI_AS4 SQLCHAR 0 5 "\t" 4 template SQL_Latin1_General_CP1_CI_AS5 SQLCHAR 0 6 "\t" 5 costcode SQL_Latin1_General_CP1_CI_AS6 SQLCHAR 0 0 "\t" 0 edom SQL_Latin1_General_CP1_CI_AS7 SQLCHAR 0 0 "\t" 0 rdom SQL_Latin1_General_CP1_CI_AS8 SQLCHAR 0 2 "\t" 6 country SQL_Latin1_General_CP1_CI_AS9 SQLCHAR 0 1 "\t" 7 branch SQL_Latin1_General_CP1_CI_AS10 SQLCHAR 0 6 "\t" 8 base SQL_Latin1_General_CP1_CI_AS11 SQLCHAR 0 0 "\t" 0 gfcid SQL_Latin1_General_CP1_CI_AS12 SQLCHAR 0 6 "\t" 9 subprod SQL_Latin1_General_CP1_CI_AS13 SQLCHAR 0 3 "\t" 10 misccy SQL_Latin1_General_CP1_CI_AS14 SQLCHAR 0 5 "\t" 11 actline SQL_Latin1_General_CP1_CI_AS15 SQLCHAR 0 10 "\t" 12 eop SQL_Latin1_General_CP1_CI_AS16 SQLCHAR 0 10 "\t" 13 average SQL_Latin1_General_CP1_CI_AS17 SQLCHAR 0 9 "\t" 14 rev SQL_Latin1_General_CP1_CI_AS18 SQLCHAR 0 1 "\r" 15 pool SQL_Latin1_General_CP1_CI_ASand this in conjunction with the -F2 parameter to bcpWed, 04 Mar 2009 04:36:13 GMTDavid Burrowshttp://www.sqlservercentral.com/Forums/Topic667155-1437-1.aspxMany Thanks for the reply again ...fmt structure is as follows..-------------------------8.0151 SQLCHAR 0 4 "\t" 1 mis_year SQL_Latin1_General_CP1_CI_AS2 SQLCHAR 0 2 "\t" 2 mis_month SQL_Latin1_General_CP1_CI_AS3 SQLCHAR 0 5 "\t" 3 misref SQL_Latin1_General_CP1_CI_AS4 SQLCHAR 0 5 "\t" 4 template SQL_Latin1_General_CP1_CI_AS5 SQLCHAR 0 6 "\t\t\t" 5 costcode SQL_Latin1_General_CP1_CI_AS6 SQLCHAR 0 2 "\t" 6 country SQL_Latin1_General_CP1_CI_AS7 SQLCHAR 0 1 "\t" 7 branch SQL_Latin1_General_CP1_CI_AS8 SQLCHAR 0 6 "\t\t" 8 base SQL_Latin1_General_CP1_CI_AS9 SQLCHAR 0 6 "\t" 9 subprod SQL_Latin1_General_CP1_CI_AS10 SQLCHAR 0 3 "\t" 10 misccy SQL_Latin1_General_CP1_CI_AS11 SQLCHAR 0 5 "\t" 11 actline SQL_Latin1_General_CP1_CI_AS12 SQLCHAR 0 10 "\t" 12 eop SQL_Latin1_General_CP1_CI_AS13 SQLCHAR 0 10 "\t" 13 average SQL_Latin1_General_CP1_CI_AS14 SQLCHAR 0 9 "\t" 14 rev SQL_Latin1_General_CP1_CI_AS15 SQLCHAR 0 1 "\r" 15 pool SQL_Latin1_General_CP1_CI_ASData file structure is as follows....---------------------------------year month misref template costcode edom rdom country branch base gfcid subprod misccy actline eop avg rev pool2008 2 21938 99999 GB0017 GB 3 990000 190990 USD 43338 0 0 -1 02008 2 21938 99999 GB0168 GB 3 990000 170610 USD 43338 0 0 -1 02008 2 21938 99999 GB0235 GB 3 990000 190990 USD 43338 0 0 -606 02008 2 21938 99999 GB0271 GB 3 990000 170120 USD 43338 0 0 -2 02008 2 21938 99999 GB0285 GB 3 990000 198526 USD 43338 0 0 -57 02008 2 21938 99999 GB0411 GB 3 990000 200250 USD 43338 0 0 -274467 02008 2 21938 99999 GB0414 GB 3 990000 203089 USD 43338 0 0 -74 02008 2 21938 99999 GB0421 GB 3 990000 203545 USD 43338 0 0 -92 02008 2 21938 99999 GB0431 GB 3 990000 203037 USD 43338 0 0 -1054 02008 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,FaizalWed, 04 Mar 2009 03:00:58 GMTfaizal.hhttp://www.sqlservercentral.com/Forums/Topic667155-1437-1.aspxCurious :ermm:I have used those parameters for such a purpose without any problems.Can you post your data structure, sample data and the format fileWed, 04 Mar 2009 01:58:54 GMTDavid Burrowshttp://www.sqlservercentral.com/Forums/Topic667155-1437-1.aspxMany Thanks for ur replyAll,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 advanceTue, 03 Mar 2009 21:52:13 GMTfaizal.hhttp://www.sqlservercentral.com/Forums/Topic667155-1437-1.aspxuse [-F firstrow] of bcp ie -F2or FIRSTROW if using BULK INSERTTue, 03 Mar 2009 06:55:12 GMTDavid Burrowshttp://www.sqlservercentral.com/Forums/Topic667155-1437-1.aspxWhile 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 AhmedTue, 03 Mar 2009 04:05:06 GMTfaizal.h