CODEWITHSUNDEEP
windowsbatch-file
CabNt
CabNt

Reputation: 31

Converting log data into csv file with a desirable format

I have a log data as:

Name:Mark
City:London
Country:UK

Name:Ben
City:Paris
Country:France

Name:Tom
City:Athens
Country:Greece

And I need to make a CSV output with the format as:

Name   City      Country
Mark   London    UK
Ben    Paris     France
Tom    Athens    Greece

The Batch that I have created for this is the simple one to convert to CSV. Which is as follows:

@echo off

cd /d %~dp0
set infilenm=abc.log
set outfilenm=abc.csv
set beforestr=
set afterstr=, 

type nul >%outfilenm%

setlocal enabledelayedexpansion

for /f "tokens=1,2,3 delims=" %%A in (%infilenm%) do (   
    set line=%%A      
    echo !line:%beforestr%=%afterstr%!>>%outfilenm%
)
endlocal

exit /b

As I am a very new for batch script, can any body help me out for this!

Upvotes: 1

Views: 1258

Answers (4)

user6811411
user6811411

Reputation:

A PowerShell solution which doesn't care about the number of address properties.
The only constant it requires is an empty line separating the addresses and
a colon between property:value

If needed it could be invoked from a batch (to be more on topic)

  • It uses Regular Expression for the splitting into sections (addresses),
    to split each section into lines and to split each line into property and value.
  • it inserts the properties with value into each new address,
  • the adjusting of missing properties in the resulting table is done automagically by PowerShell
  • displaying as a table with column width autodetected by Format-Table
## Q:\Test\2018\07\04\SO_51166380.ps1
$InputFile = '.\abc.log'
$OutputFile= '.\abc.csv'

$Sections = ((Get-Content $InputFile -Raw) -split "`r?`n *`r?`n" -ne '')

$Csv = ForEach($Section in $Sections){
    $Address = New-Object PSCustomObject
    ForEach($PropVal in ($Section -Split "`r?`n" -ne '')){
        $Prop,$Val = $PropVal.Split(':',2)
        Add-Member -InputObject $Address `
                   -NotePropertyName $Prop `
                   -NotePropertyValue $Val
    }
    $Address
}
$Csv | Format-Table -Auto
$Csv | Export-Csv $OutputFile -NoTypeInformation

Sample output with modified abc.log

> type abc.log
Name:Mark
City:London
Country:UK
LastName:Anonymus

Name:Ben
Country:France

Name:Tom
City:Athens

Name:Antonio
City:Mexico
Country:Mexico

> .\SO_51166380.ps1

Name    City   Country LastName
----    ----   ------- --------
Mark    London UK      Anonymus
Ben            France
Tom     Athens
Antonio Mexico Mexico

> type .\abc.csv
"Name","City","Country","LastName"
"Mark","London","UK","Anonymus"
"Ben",,"France",
"Tom","Athens",,
"Antonio","Mexico","Mexico",

Upvotes: 1

Aacini
Aacini

Reputation: 67216

Your question is unclear in several points, so we can only guess...

@echo off
setlocal EnableDelayedExpansion

rem Put here the width of the output columns
set "width=10"

set "spaces="
for /L %%i in (1,1,%width%) do set "spaces= !spaces!"
set "head=" & "out=" & set "output="
for /F "tokens=1-3 delims=:" %%a in ('findstr /N "^" logData.txt') do (
   if "%%b" neq "" (
      if not defined output (
         set "col=%%b%spaces%"
         set "head=!head!!col:~0,%width%!"
         set "out=!out!^!%%b:~0,%width%^!"
      )
      set "%%b=%%c%spaces%"
   ) else (
      if not defined output (
         echo !head!
         set "output=!out!"
      )
      for /F %%o in ("!output!") do echo %%o
      for %%a in (!head!) do set "%%a=%spaces%"
   )
)

With this logData.txt:

Name:Mark
City:London
Country:UK

Name:Ben
Country:France

Name:Tom
City:Athens

Name:Antonio
City:Mexico
Country:Mexico

This is the output:

Name      City      Country
Mark      London    UK
Ben                 France
Tom       Athens
Antonio   Mexico    Mexico

This program requires that the first group of data include all the columns, and that the last group of data be followed by an empty line...

Upvotes: 1

michael_heath
michael_heath

Reputation: 5372

@echo off
setlocal

set "output=abc.csv"
2> "%output%" echo.

set "line=Name,City,Country"
call :write

for /f "tokens=1,* delims=:" %%A in (abc.log) do call :append %%A %%B
exit /b

:append
setlocal
set  "key=%~1"
set  "value=%~2"
endlocal & (
    if /i "%key%" == "Name" set "line=%value%"
    if /i "%key%" == "City" set "line=%line%,%value%"
    if /i "%key%" == "Country" set "line=%line%,%value%"& call :write
)
exit /b

:write
setlocal
for /f "tokens=1-3 delims=," %%A in ("%line%") do (
    set "a=%%~A          "
    set "b=%%~B          "
    set "c=%%~C          "
)
>> "%output%" echo %a:~,10% %b:~,10% %c:~,10%
set "line="
exit /b

The header is written to file first by setting it to the variable named line and calls the label :write to format and write to the csv output file.

The for loop splits each line by : with tokens 1,* to get the 1st token before : and the 2nd token as the remainder after the :. It calls the label :append to concatenate the line based on the 1st token. If the token equals Country, then a call to the label :write formats the line and writes it to the csv output file.

Upvotes: 1

aschipfl
aschipfl

Reputation: 34919

You have got a wrong logic in your script; for /F reads one line after another, so you have to collect the data of three lines before writing one output line.

Here is an example of how to accomplish your task, not using for /F but input redirection (<) and set /P to read the log file:

@echo off
setlocal EnableDelayedExpansion
for /F %%C in ('^< "abc.log" find /C /V ""') do set /A "COUNT=(%%C+1)/2"
set "FIRST=#"
< "abc.log" > "abc.csv" (
    for /L %%I in (1,1,%COUNT%) do (
        set "LINE1=" & set /P LINE1=""
        if defined LINE1 (
            set "LINE2=" & set /P LINE2=""
            set "LINE3=" & set /P LINE3=""
            if defined FIRST (
                echo Name,City,Country
                set "FIRST="
            )
            echo(!LINE1:*:=!,!LINE2:*:=!,!LINE3:*:=!
        )
    )
)
endlocal

This relies on the shown format of your log file, so it does not verify the strings left to the colons.

Here is a more flexible approach, which is based on the above one, but it collects the field values by their names which are held in a predefined configurable list (constant _LIST). One or more empty lines complete a returned row. If a certain field name cannot be found in the currently processed block of the log file, its returned CSV field is empty. This is the code:

@echo off
setlocal EnableExtensions EnableDelayedExpansion

rem // Define constants here:
set "_INPUT=abc.log"  & rem // (log file to process)
set "_OUTPUT=abc.csv" & rem // (CSV file to return)
set "_LIST=Name,City,Country" & rem /* (comma-separated list of field names, which must
                                rem     not contain any of the following characters:
                                rem     `:`, `,`, `*`, `?`, `<`, `>`, `!`, `"`, `=`) */
set "_SEPARATOR=,"    & rem /* (separator character to be used; the default is `,`;
                        rem     the following separator characters are forbidden:
                        rem     `!`, `^`, `&`, `(`, `)`, `<`, `>`, `|`) */
set "_QUOTED=#"       & rem // (if not empty, defines to quote the returned items)
set "_HEADER=#"       & rem // (if not empty, defines to write a header row)

set "_SEPARATOR=!_SEPARATOR!," & set "_SEPARATOR=!_SEPARATOR:~,1!"
if not defined _QUOTED (set "QUOTE=") else set "QUOTE="^" & rem/^"
for /F "delims==" %%D in ('2^> nul set $ARRAY[') do set "%%D="
for /F %%C in ('^< "abc.log" find /C /V ""') do set /A "COUNT=%%C+1"
< "abc.log" > "abc.csv" (
    set "FLAG=" & if defined _HEADER if defined _LIST (
        echo(%QUOTE%!_LIST:,=%QUOTE%%_SEPARATOR%%QUOTE%!%QUOTE%
    ) else echo(%QUOTE%%QUOTE%
    for /L %%I in (1,1,%COUNT%) do (
        set "LINE=" & set /P LINE=""
        if defined LINE (
            for /F "delims=: eol=:" %%J in ("!LINE!") do set "$ARRAY[%%J]=!LINE:*:=!"
            set "FLAG=#"
        ) else (
            if defined FLAG if defined _LIST (
                set "COLL=" & for %%J in ("!_LIST:,=","!") do (
                    set "COLL=!COLL!%_SEPARATOR%%QUOTE%!$ARRAY[%%~J]!%QUOTE%"
                    set "$ARRAY[%%~J]="
                )
                echo(!COLL:~1!
            ) else echo(%QUOTE%%QUOTE%
            set "FLAG="
        )
    )
)
endlocal
exit /B

This script collects the list items in some kind of array $ARRAY[] whose indexes are the field names, hence the strings left to the (first) colon of every line in a block of the log file, and whose element values are the strings right to the (first) colon, and may look like this (with respect to the first block of your example log data):

$ARRAY[Name]=Mark
$ARRAY[City]=London
$ARRAY[Country]=UK

Upvotes: 4

Related Questions