Solved

Splice up a file via DOS Batch

Posted on 2012-12-20
6
412 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
Comment Utility
Here's sample files with only a few rows data.
Load.zip
0
 
LVL 51

Expert Comment

by:Bill Prew
Comment Utility
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
Comment Utility
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 your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:elwayisgod
Comment Utility
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 68

Expert Comment

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

Author Closing Comment

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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction: Recently, I got a requirement to zip all files individually with batch file script in Windows OS. I don't know much about scripting, but I searched Google and found a lot of examples and websites to complete my task. Finally, I was ab…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now