vertical output to horizontal output for a specific parameter-CodePudding

I have a vertical output from a curl like below and I would like to use shell script or batch script to have a horizontal output to put in a csv format. Is it possible or can you share a command to have this kind output? thanks.

let's say i stored the output in output.txt

"name" : "Bob",
"PhoneNumber" : "123-555"
"name" : "Mike",
"PhoneNumber" : "234-555"
"name" : "Amber",
"PhoneNumber" : "456-555"

Horizontal Output:

name, PhoneNumber
Bob, 123-555
Mike, 234-555
AMber, 456-555

CodePudding user response：

The batch file command lines below can be used if the file output.txt in directory of the batch file contains the lines:

"name" : "Bob",
"PhoneNumber" : "123-555"
"name" : "Mike",
"PhoneNumber" : "234-555"
"name" : "Amber",
"PhoneNumber" : "456-555"

The batch file is:

@echo off
setlocal EnableExtensions DisableDelayedExpansion
if not exist "%~dp0output.txt" echo File "%~dp0output.txt" not found.& exit /B 1
set "Name="
(   echo name,PhoneNumber
    for /F usebackq^ tokens^=1^,3^ delims^=^" %%I in ("%~dp0output.txt") do (
        if "%%I" == "name" (set "Name=%%J") else if "%%I" == "PhoneNumber" (
            set "PhoneNumber=%%J"
            setlocal EnableDelayedExpansion
            if "!Name:,=!" == "!Name!" (
                echo !Name!,!PhoneNumber:,=!
            ) else (
                echo "!Name!",!PhoneNumber:,=!
            )
            endlocal
        )
    )
)>"%~dp0Data.csv"
endlocal

This batch file creates in the batch file directory the CSV file Data.csv with the lines:

name,PhoneNumber
Bob,123-555
Mike,234-555
Amber,456-555

If a name contains a comma, the name is enclosed in " in the CSV file Data.csv. The code makes sure that a phone number does not contain a comma before writing it into the CSV file.

For understanding the used commands and how they work, open a command prompt window, execute there the following commands, and read entirely all help pages displayed for each command very carefully.

call /? ... explains %~dp0 ... drive and path of argument 0 which is the batch file path always ending with a backslash.
echo /?
endlocal /?
exit /?
for /?
goto /?
if /?
set /?
setlocal /?

See also:

Microsoft documentation about Using command redirection operators for an explanation of the redirection operator >.
Single line with multiple commands using Windows batch file for an explanation of unconditional command operator &.

CodePudding user response：

This solution is a general-use one that converts an input file with any number of vertical lines for each horizontal data; you just need to specify the number of lines in the code:

@echo off
setlocal EnableDelayedExpansion

rem Set the number of lines that comprise each data group
set "num=2"

set "header="
set "head="
set "data="
set "n=0"
(for /F "tokens=1,2 delims=:, " %%a in (output.txt) do (
   set "head=!head!, %%~a"
   set "data=!data!, %%~b"
   set /A "n=(n 1)%%num"
   if !n! equ 0 (
      if not defined header (
         set "header=!head!"
         echo !header:~2!
      )
      echo !data:~2!
      set "data="
      set "head="
   )
)) > output.csv

CodePudding user response：

It's a bit fragile, but pretty trivial to do this with awk:

$ cat input
"name" : "Bob",
"PhoneNumber" : "123-555"
"name" : "Mike",
"PhoneNumber" : "234-555"
"name" : "Amber",
"PhoneNumber" : "456-555"
$ awk 'BEGIN{print "name, PhoneNumber"}
    /name/ {printf "%s, ", $3}
    /Phone/{print $3}'  FS='[": ,]*' input
name, PhoneNumber
Bob, 123-555
Mike, 234-555
Amber, 456-555

You could make it (slightly) more robust with something like:

$ awk 'BEGIN{print "name, PhoneNumber"} 
>     $2 == "name" {printf "%s, ", $3}
>     $2 == "PhoneNumber"{print $3}
> ' FS='[": ,]*' input
name, PhoneNumber
Bob, 123-555
Mike, 234-555
Amber, 456-555