Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Splice up a file via DOS Batch

Posted on 2012-12-20
6
Medium Priority
?
470 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
  • 3
  • 2
6 Comments
 

Author Comment

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

Expert Comment

by:Bill Prew
ID: 38710386
0
 
LVL 71

Accepted Solution

by:
Qlemo earned 2000 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 71

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

YESTERDAY YESTERDAY.BAT is inspired by a previous article I wrote entitled: TOMORROW.BAT (http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/MS_DOS/A_4196-Advanced-Batch-File-Programming-TOMORROW-BAT.html). The crux of this batch f…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

916 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