Solved

Splice up a file via DOS Batch

Posted on 2012-12-20
6
455 Views
Last Modified: 2012-12-20
I have 21 files that are pipe delimited and contain a header row.  Each file contains a Year column.  However the Year column could be in a different column for each file.  I need to splice out, into new files, all the rows where the Year is 2012.  New files need to contain the same header row and be named the same except with a prefix of '52WK_' and only contain the rows from original file where the Year was 2012.  Other columns in the files might contain 2012 but it could be Units or other data and not in Years column.  Thus important we only get where the Years column is 2012.  The remaining rows of data that are not Years of 2012 can be prefixed with 'Non_2012_' and same original filename after that.  Attached is my file names to look in and which column is they Years.

Want this in DOS Batch if possible.  I have another VB one too as I don't know if client prefers DOS or VB so thought I would do both.
filenames-and-years-column.xlsx
0
Comment
Question by:elwayisgod
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 

Author Comment

by:elwayisgod
ID: 38710342
Here's sample files with only a few rows data.
Load.zip
0
 
LVL 54

Expert Comment

by:Bill Prew
ID: 38710386
0
 
LVL 70

Accepted Solution

by:
Qlemo earned 500 total points
ID: 38710394
Put your file list and the number of year column into a file "filelist.txt", pipe-separated without header, like:
actfisc.txt|2
actppln.txt|2

Open in new window

Then use the following batch to "split" the file contents.
@echo off
setlocal EnableDelayedExpansion

REM get a file name and the YEAR column
for /F "tokens=1,2 delims=|" %%A in (filelist.txt) do call :Extract %%A %%B

exit /b

:Extract FileName YearColNo
set filename=%1
set colno=%2

@echo Processing file %filename%

REM Get the header line
set head=
for /F "usebackq delims=" %%F in ("%filename%") do if not defined head set head=%%F

REM Construct RegEx for matching the correct year column
set pat=
for /L %%L in (2,1,%colno%) do set "pat=!pat![^^|]*|"
set "pat=^^!pat!2012^|"

> 52WK_%filename%     echo,!head!
>>52WK_%filename%     findstr /R "%pat%" %filename%
REM Head will be added automatically 
> Non_2012_%filename% findstr /V /R "%pat%" %filename%

exit /b

Open in new window

Restriction are that the year column may not be the last one, and lines do not start with a pipe.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:elwayisgod
ID: 38710398
Yes.  But client doesn't use VB so might need in Windows Batch.  But yes the requirements are exactly the same.   Top priority is probably the VB since it's 99% done.  Sorry, very picky client when it comes to these types of things.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 38710412
Using a (.NET) Regex should work in VBS solution, too.
BTW, is PowerShell an option? (ducking)
0
 

Author Closing Comment

by:elwayisgod
ID: 38710776
Perfect.  Both the DOS and VB results are identical.  Thanks much!!!!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you receive another warning that your shared drive is almost full and you have asked your users to clean out old files again and again, here is a single command that may help. This command will place all the files that have not been used rec…
How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question