Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 471
  • Last Modified:

Splice up a file via DOS Batch

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
elwayisgod
Asked:
elwayisgod
  • 3
  • 2
1 Solution
 
elwayisgodAuthor Commented:
Here's sample files with only a few rows data.
Load.zip
0
 
QlemoC++ DeveloperCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
elwayisgodAuthor Commented:
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
 
QlemoC++ DeveloperCommented:
Using a (.NET) Regex should work in VBS solution, too.
BTW, is PowerShell an option? (ducking)
0
 
elwayisgodAuthor Commented:
Perfect.  Both the DOS and VB results are identical.  Thanks much!!!!
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now