Splitting a text file based upon another file

[sfournier]

Hello,

I've been trying to wrap my head around this basic scripting issue and have not been able to complete it. I haven't had to script in a while so I am a bit rusty to be honest.

What I'm trying to do is take a .TXT file that is created in our software and split that into 2 separate files based on a list I have in a different file.

the #'s in the second .TXT file are listed below.

I need to keep the original format of the file to be split in 2 as the machines it will be used on are very particular on formating.

Any help will be greatly appreciated.

Thank you,

Steve

000026
000920
000922
000925
000926
001116
001910
001915
001917
001920
001921
001923
001924
001929
001930
002402
002448
002450
002453
002496
002498
002499
002500
002628
002634
002635
002640
002659
002675
002678
002940
002941
002942
002943
002944
002945
002946
004757
005413
005512
005555
006421

[Endoro]

based on a list I have in a different file.

the #'s in the second .TXT file are listed below.

please explain more clearly.

[sfournier]

For sure.
So multiple times throughout a day we create extracts for orders to be packaged. They are generated in one software that are to be used to package on multiple other machines. The extract is in .txt in the format below

XXXX,XXXX XXXXX ER-1BE ER1BE-101-B ER00655310 ERH 000321 20130510 1700 1 E0333097 ER-1BE
XXXX,XXXX XXXXX ER-1BE ER1BE-101-B ER00655310 ERH 002442 20130510 1700 1 E0333098 ER-1BE
XXXX,XXXX XXXXX ER-1BE ER1BE-101-B ER00655310 ERH 005026 20130510 2200 1 E0333096 ER-1BE
XXXX,XXXX XXXXX ER-1BE ER1BE-101-B ER00655310 ERH 003300 20130510 2200 0.5 E0333102 ER-1BE
XXXX,XXXX XXXXX ER-1BE ER1BE-101-B ER00655310 ERH 003329 20130510 2200 1 E0333100 ER-1BE
XXXX,XXXX XXXXX ER-1BE ER1BE-101-B ER00655310 ERH 004146 20130510 2200 1 E0333105 ER-1BE
XXXX,XXXX XXXXX ER-1BE ER1BE-101-B ER00655310 ERH 000292 20130511 0800 1 E0333095 ER-1BE
XXXX,XXXX XXXXX ER-1BE ER1BE-101-B ER00655310 ERH 005026 20130511 0800 1 E0333096 ER-1BE
XXXX,XXXX XXXXX ER-1BE ER1BE-101-B ER00655310 ERH 002442 20130511 0800 1 E0333098 ER-1BE
XXXX,XXXX XXXXX ER-1BE ER1BE-101-B ER00655310 ERH 003329 20130511 0800 1 E0333100 ER-1BE
XXXX,XXXX XXXXX ER-1BE ER1BE-101-B ER00655310 ERH 003300 20130511 1200 0.25 E0333101 ER-1BE
XXXX,XXXX XXXXX ER-1BE ER1BE-101-B ER00655310 ERH 005026 20130511 1400 1 E0333096 ER-1BE

So you can see in Column 5 the 6 digit # that I am going to be using to filter it.

The list I gave in the previous post is an easier to maintain list vs the other list for the 2 files I need to create. Think of it as a list of ~40-50 items vs a list of ~3000.

It's been a long day so I may not be giving the information you need. If I am missing something else let me know.

[Squashman]

If your columns are breaking on spaces that looks like the 6th column.

[Endoro]

in your posted files is no match.
Split before or after a match?

this splits after:

Code: Select all

@echo off&setlocal
set "file=file.txt"
set "search=search.txt"
for /f "delims=:" %%i in ('findstr /ng:"%search%" "%file%"') do set "split=%%i"
(for /f "tokens=1*delims=:" %%i in ('findstr /n "^" "%file%"') do if %%i leq %split% echo(%%j)>"%file%.new1"
<"%file%">"%file%.new2" more +%split%
type "%file%.new?"

[sfournier]

Sorry,

I need after a match.
What I am trying to do is create a script that would compare the Original extract against the list of #'s. Anything matching the list would be split into FileA.txt and anything that was not in the list would be in FileB.txt.

I just realized the spacing did not carry over in my previous post about the columns, the column I was trying to point out was the one after ERH

[Aacini]

What I am trying to do is create a script that would compare the Original extract against the list of #'s. Anything matching the list would be split into FileA.txt and anything that was not in the list would be in FileB.txt.

I just realized the spacing did not carry over in my previous post about the columns, the column I was trying to point out was the one after ERH

The Batch file below do that:

Code: Select all

@echo off
setlocal EnableDelayedExpansion

set numberList=
for /F %%a in (numberList.txt) do set numberList=!numberList! %%a

del FileA.txt 2>NUL
del FileB.txt 2>NUL
for /F "tokens=1-7*" %%a in (original.txt) do (
   if "!numberList:%%g=!" neq "%numberList%" (
      echo %%a %%b %%c %%d %%e %%f %%g %%h>> FileA.txt
   ) else (
      echo %%a %%b %%c %%d %%e %%f %%g %%h>> FileB.txt
   )
)

Antonio

[Endoro]

very nice!

[sfournier]

Thank you both so much for the replies.

@Aacini - this is working almost perfectly, it is just losing the formating.

[Endoro]

and with "findstr":

Code: Select all

@echo off&setlocal
set "file=file.txt"
set "fsearch=search.txt"
del "%file%.new?" 2>NUL
for /f "usebackqtokens=1-7*" %%i in ("%file%") do findstr /l "%%o" "%fsearch%" >nul  && (
   echo %%i %%j %%k %%l %%m %%n %%o %%p>>"%file%.new1"
   ) || echo %%i %%j %%k %%l %%m %%n %%o %%p>>"%file%.new2"
type "%file%.new?"

[sfournier]

@Endoro - "findstr" is creating a 2 files, but one is the original file completely and the second is blank.

[Aacini]

@Aacini - this is working almost perfectly, it is just losing the formating.

My solution preserve the same format of your "Original extract" data above. Perhaps if you indicate what is the difference, I could fix it...

Antonio

[sfournier]

The version above lost the formatting from the original file, it deleted the extra spaces. I'll try to link it again

XXXXXX,XXXX XXXXX CG-BC CGBC-219-1 CG00270994 CGH 002204 20130520 1800 2 CG0713992 CG-BC1

[sfournier]

https://docs.google.com/file/d/0B451GmzfEgiheENFRGV2RzJSdkk/edit?usp=sharing

Lets try this

[Aacini]

Have the original file a fixed field format? If so, please indicate at which column start the key field (seventh field, after "CGH"). Please, note that the last data you posted HERE also have just one space between fields! Perhaps you may post it with "code" tag instead "quote".