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
Solved

Splice up a file via DOS Batch

Posted on 2012-12-20
6
446 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 53

Expert Comment

by:Bill Prew
ID: 38710386
0
 
LVL 69

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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

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 69

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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
You may have already been in the need to update a whole folder stucture using a script. Robocopy does it well and even provides a list of non-updated files in a log (if asked to). Generally those files that were locked by a user or a process by the …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

840 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