Solved

Windows batch script

Posted on 2014-03-24
17
520 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 68

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 68

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
 
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 18

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 51

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 51

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 68

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 51

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 68

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 68

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 68

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Join & Write a Comment

Microsoft Windows Server Update Service (WSUS) is free for everyone, but it lacks of some desirable features like send an e-mail to the administrator with the status of all computers on the WSUS server. This article is based on my PowerShell script …
A procedure for exporting installed hotfix details of remote computers using powershell
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

707 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

17 Experts available now in Live!

Get 1:1 Help Now