Solved

Windows batch script

Posted on 2014-03-24
17
531 Views
Last Modified: 2014-03-26
Hi ,

I have a folder named "Folder1" which contains say 10 txt files.

What i am looking for is a batch script which will parse all 10 txt files and make a csv file with the folder name, so in this example its Folder1.csv

Each line in a csv file represents a txt file , so this csv file should have 10 rows.

The csv has 2 columns only .

Column A -> Holds 1 line of the txt file
Column B -> Holds 2nd line to end of the line in the txt file.

After that in column A of the csv file remove a word "AuthorName" .

Is it feasible via batch script.

Thanks
0
Comment
Question by:magento
  • 7
  • 6
  • 3
  • +1
17 Comments
 
LVL 69

Expert Comment

by:Qlemo
ID: 39950989
Am I correct that the CSV file will have line 1 in column A and line 2 in column B?
The way you described it, I reckon you mean
  Column B -> Holds 2nd line to end of file in the text file
but that would be problematic, because of the carriage returns. You can do that, but the CSV file looks garbled (though correct).

I'll show the first meaning here in PowerShell:
$folder = 'C:\Temp\Folder1'

get-childitem $folder -include *.txt | % {
  $lines = (get-content -ReadCount 2 $_)[0,1]
  New-Object PsObject -Property @{
    ColA = $lines[0] -replace 'AuthorName', ''
    ColB = $lines[1]
  }
} | export-csv "c:\Temp\$(Split-Path -leaf $folder).csv" -NoType

Open in new window

0
 
LVL 5

Author Comment

by:magento
ID: 39951053
Hi Qlemo,

I saved the code in a file parse.ps1 and go to command prompt .

and run ./parse.ps1 nothing happened but created an 0 byte csv file

Please advice.

PS C:\EE> Get-ExecutionPolicy
Restricted
PS C:\EE>
PS C:\EE> Set-ExecutionPolicy unrestricted

Execution Policy Change
The execution policy helps protect you from scripts that you do not trust.
Changing the execution policy might expose you to the security risks described
in the about_Execution_Policies help topic. Do you want to change the execution
 policy?
[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"):
PS C:\EE> Get-ExecutionPolicy
Unrestricted
PS C:\EE> ./parse.ps1
PS C:\EE>

Thanks
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 39951086
That is correct. The default action for PowerShell scripts is to open them in Notepad, for security reasons (you cannot accidentally execute malicious PS code that way).
Start powershell.exe, and type the path and name to the file in its prompt. Or use
  powershell -file .\parse.ps1
from cmd.exe .
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 5

Author Comment

by:magento
ID: 39951119
Hi,

I can able to execute the file now. But the output created by the script is empty.

No data been found in that csv file.

Please advice.

Thanks
0
 
LVL 19

Assisted Solution

by:Raheman M. Abdul
Raheman M. Abdul earned 100 total points
ID: 39951571
Try this : (Modified to Qlemo code)

$folder = "C:\Temp\a"
get-childitem $folder -Filter *.txt | % {
$content=Get-Content $_.FullName
  $first = $content | select -first 1
  $last = $content | Select -Skip 1
 
  New-Object PsObject -Property @{ ColumnA= $first -replace 'AuthorName', ''
  ColumnB = "$last"  }
} | export-csv "c:\Temp\$(Split-Path -leaf $folder).csv" -NoTypeInformation
0
 
LVL 53

Expert Comment

by:Bill Prew
ID: 39952111
Just as a different approach, here's a BAT file that should get the job done.

@echo off
setlocal EnableDelayedExpansion

REM Define source and dest folders
set BaseDir=c:\Folder1
set DestDir=c:\temp

REM Get just the folder name from the source folder to create output CSV file path
for %%A in ("%BaseDir%") do set OutFile=%DestDir%\%%~nA.csv

REM Send all output tto the CSV file
(
  REM Process all files in the folder
  for %%A in ("%BaseDir%\*.*") do (
    REM Reset first and last line holders
    set FirstLine=
    set LastLine=
    REM Read this file, and save the first and last lines
    for /f "usebackq tokens=*" %%B in ("%%~A") do (
      if "!FirstLine!" EQU "" set FirstLine=%%B
      set LastLine=%%B
    )
    REM Write out this files 2 fields
    echo !FirstLine!,!LastLine!
  )
) > "%OutFile%"

Open in new window

~bp
0
 
LVL 5

Author Comment

by:magento
ID: 39952325
Raheman Mohammed Abdul,

The code u provided works exactly . But it takes only one folder. But i have more than 10 folders so if we give path as C:\\Folders it need to parse each folders and provide csv for each folders..Will it be possible ?

Thanks
0
 
LVL 53

Expert Comment

by:Bill Prew
ID: 39952914
Yes, possible, a few questions.

So all the folders are within C:\Folder1, like:

C:\Folder1\SubFolder1
C:\Folder1\SubFolder2
C:\Folder1\SubFolder3
C:\Folder1\SubFolder4
C:\Folder1\SubFolder5
. . .

And so you want to process all subfolders under C:\Folder1, and all files in each?

Are there any files in C:\Folder1 that need to be processed, or are they all in the subfolders?

~bp
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 39952976
Note that
a)  Raheman's code collects all but the first line in the second column, removing any line break
b) Bill's code stores the last line in column B
so all concepts are different.
Since you "confirmed" Raheman's code as working, I'll extend that:
get-childitem 'C:\Temp\' |
  ? { $_.PsIsContainer } | % {
    $folder = $_
    get-childitem $folder -Filter *.txt | % {
      $content=Get-Content $_.FullName
      $first = $content | select -first 1
      $last = $content | Select -Skip 1
      New-Object PsObject -Property @{
         ColumnA= $first -replace 'AuthorName', ''
         ColumnB = "$last"  }
    } | export-csv "c:\Temp\$(Split-Path -leaf $folder).csv" -NoTypeInformation
  }

Open in new window

0
 
LVL 53

Assisted Solution

by:Bill Prew
Bill Prew earned 100 total points
ID: 39953050
And here's a version of the BAT approach that should gather all the files in subfolders and process as desired, first line, and then all other lines in the second column.

@echo off
setlocal EnableDelayedExpansion

REM Define source and dest folders
set BaseDir=c:\Folder1
set DestDir=c:\temp

REM Get just the folder name from the source folder to create output CSV file path
for %%A in ("%BaseDir%") do set OutFile=%DestDir%\%%~nA.csv

REM Send all output tto the CSV file
(
  REM Process all subfolders in the folder
  for /d %%A in ("%BaseDir%\*.*") do (
    REM Process all files in the folder
    for %%B in ("%%~A\*.*") do (
      REM Reset first and last line holders
      set First=
      set Rest=
      REM Read this file, and save the first and last lines
      for /f "usebackq tokens=*" %%C in ("%%~B") do (
        if "!First!" EQU "" (
          set First=%%C
        ) else (
          set Rest=!Rest! %%C
        )
      )
      REM Write out this files 2 fields
      echo !First!,!Rest!
    )
  )
) > "%OutFile%"

Open in new window

~bp
0
 
LVL 5

Author Comment

by:magento
ID: 39953174
Hi Billprew,


I tried ur first code , but didnt get the expected solution, let me try ur 2nd script and post the feedback , thank you very much.

My folder structure

C:\EE\FOLDER
C:\EE\FOLDER\FOLDER1
C:\EE\FOLDER\FOLDER2
C:\EE\FOLDER\FOLDER3

Qlemo,

The code u provided works fine, but i have to copy the folders to root directory , can you advice why?

The code i tried is below:

get-childitem 'C:\EE\FOLDER\' |
  ? { $_.PsIsContainer } | % {
    $folder = $_
    get-childitem $folder -Filter *.txt | % {
      $content=Get-Content $_.FullName
      $first = $content | select -first 1
      $last = $content | Select -Skip 1
      New-Object PsObject -Property @{
         ColumnA= $first -replace 'Dr', ''
         ColumnB = "$last"  }
    } | export-csv "C:\EE\FOLDER\$(Split-Path -leaf $folder).csv" -NoTypeInformation
  }

 

Open in new window


Error:

PS C:\EE> ./parse.ps1
Get-ChildItem : Cannot find path 'C:\EE\FOLDER1' because it does not exist.
At C:\EE\parse.ps1:4 char:18
+     get-childitem <<<<  $folder -Filter *.txt | % {
    + CategoryInfo          : ObjectNotFound: (C:\EE\FOLDER1:String) [Get-
   ChildItem], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetCh
   ildItemCommand

Get-ChildItem : Cannot find path 'C:\EE\FOLDER2' because it does not exist.
At C:\EE\parse.ps1:4 char:18
+     get-childitem <<<<  $folder -Filter *.txt | % {
    + CategoryInfo          : ObjectNotFound: (C:\EE\FOLDER2:String) [Get-
   ChildItem], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetCh
   ildItemCommand

Get-ChildItem : Cannot find path 'C:\EE\FOLDER3' because it does not exist.
At C:\EE\parse.ps1:4 char:18
+     get-childitem <<<<  $folder -Filter *.txt | % {
    + CategoryInfo          : ObjectNotFound: (C:\EE\FOLDER3:String) [Get-
   ChildItem], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetCh
   ildItemCommand
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 39953420
get-childitem 'C:\EE\FOLDER\' |
  ? { $_.PsIsContainer } | % {
    $folder = $_.FullName
    get-childitem $folder -Filter *.txt | % {
      $content=Get-Content $_.FullName
      $first = $content | select -first 1
      $last = $content | Select -Skip 1
      New-Object PsObject -Property @{
         ColumnA= $first -replace 'Dr', ''
         ColumnB = "$last"  }
    } | export-csv "C:\EE\FOLDER\$(Split-Path -leaf $folder).csv" -NoTypeInformation
  }

Open in new window

0
 
LVL 5

Author Comment

by:magento
ID: 39953466
Works perfectly , thank you .

Need a small modification.

In few text files , i noticed the txt files content.

1.txt
Dr. ss StPO Zwisfahren
C. I. 2/3

2.txt
Dr. ss StPO Zwanahmen B. II. 4/12
good here

3.txt
Dr. ss StPO Verfrenang
A. IV. 1

In few txt files i see the main code comes in line 2 , is it possible via regex to check for line 2 length less than 8 and has Alphabet.Noman_number.Number and append it to line 1 ?

So in the above case 1.txt and 3.txt should append the line 2 to line 1 of the csv Column A and the remaining should send to column B

Any idea please?
0
 
LVL 69

Accepted Solution

by:
Qlemo earned 300 total points
ID: 39953599
That's getting complex and instransparent now somehow ...
get-childitem 'C:\EE\FOLDER\' |
  ? { $_.PsIsContainer } | % {
    $folder = $_.FullName
    get-childitem $folder -Filter *.txt | % {
      $content=Get-Content $_.FullName
      $colA = $content | select -first 1
      $colB = $content | Select -Skip 1
      if ($colB[0].Length -lt 8 -and $colB[0] -match '\w+\.[XVI]+\.\d+)
      {
         $colA += ' ' + $colB[0]
         $colB = $colB[(-$colB.Count+1)..-1]
      }
      New-Object PsObject -Property @{
         ColumnA= $colA -replace 'Dr', ''
         ColumnB = $colB -join ' ' }
    } | export-csv "C:\EE\FOLDER\$(Split-Path -leaf $folder).csv" -NoTypeInformation
  }

Open in new window

0
 
LVL 5

Author Comment

by:magento
ID: 39953642
Hi ,

I am sorry about it . Just realised the txt content is wrong so only added it .

Thanks for your help. I will modify the regex and get it work .

One last question , will this code can be executed in Mac ?

Thanks
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 39953657
No, PowerShell does not work on Mac. You can see if http://blogs.msdn.com/b/powershell/archive/2008/04/08/powershell-on-linux-solaris-mac-etc.aspx works for you if needed.
0
 
LVL 5

Author Comment

by:magento
ID: 39956620
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
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.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

860 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