Exclude First record and last record in BCP

[ramesh ram]

Hi friends,

please help me

as per requirement i have text file like belwo

00|yyyymmdd
01|vin1|actnumber
01|.........
..
...
..
99|YYYYMMDD|record_count

here 00 is header record and 99 is footer record
i want to load data without header and footer in a table using BCP
and extract record_count from file to another text file

Thanks,
Ramesh

[Herbert Kleebauer]

If you only want the number of lines not beginning with 00 or 99
and the name of the file is a.txt:

set n=0
for /f %%i in ('findstr /v "\<00 \<99" a.txt') do set /a n+=1
echo there are %n% lines

[ramesh ram]

Hi Herbert,

Thanks for your response,As per my requirement i was trying that
1.find the last line record because it have the record count with
eg:
99|20140114|10
i want to substring of the above record from next date to last
for above one 10 into a variable as well as it need to generate file using that variable like below
set record_count=10
same 10 into one text file

let me if i miss any thing

Thanks,
Ramesh

[Herbert Kleebauer]

On 14.01.2014 14:21, ramesh ram wrote:
> Hi Herbert,
>
> Thanks for your response,As per my requirement i was trying that
> 1.find the last line record because it have the record count with
> eg:
> 99|20140114|10
> i want to substring of the above record from next date to last
> for above one 10 into a variable as well as it need to generate file using that variable like below
> set record_count=10
> same 10 into one text file

I don't understand what exactly you want to do. If you
just want to extract the information stored in the line
starting with 99 in a file a.txt, try this:

for /f "tokens=1-3 delims=^|" %%i in ('findstr " \<99" a.txt') do (
set a=%%i
set b=%%j
set c=%%k)

echo footer token: %a%
echo date: %b%
echo record_count: %c%

[Frank P. Westlake]

I think this is one for Todd to deal with.

Frank

[JJ]

That means to remove the first and last line, right?

@echo off
setlocal enabledelayedexpansion
set prev=
set prev2=
type nul>output.txt
for /f "delims=^| tokens=1,*" %%A in (input.txt) do (
if not "!prev!" == "" if not "!prev!" == "00" (
echo !prev!^|!prev2!>>output.txt
)
set prev=%%A
set prev2=%%B
)
type output.txt

[Todd Vargo]

On 1/14/2014 10:42 AM, Frank P. Westlake wrote:
> I think this is one for Todd to deal with.

What is BCP?

Based on the information provided, the following should create the
desired output file. OP is on his own with loading into BCP and
extracting the record count.

findstr /B /V "00" input.txt|findstr /B /V "99" >output.txt

--
Todd Vargo
(Post questions to group only. Remove "z" to email personal messages)

[ramesh ram]

Hi All,

thanks for your response,
in the above file have .xx1 format like below
00|20140115
01|1234|A|5009|NewYork
01|12345|B|300|Dallas
01|12345|B|300|Texas
99|20140115|2

to load the above file into a table i have used below BCP command
%BCP% Test.dbo.test1 IN test.xx1 -S servername -T - F 2 -t^| -c
it load the data from 3rd record to 4th record

01|12345|B|300|Dallas
01|12345|B|300|Texas

it exclude Header as well as one more record 01|1234|A|5009|NewYork and it automatically exclude the last record.

my requirement is load without header record and footer record into a table

Thanks,
Ramesh

[Todd Vargo]

Did you try using the command that I provided?

Input.txt is your original file. Output.txt is same file with lines
beginning with 00 and 99 removed.