vertical output to horizontal output for a specific parameter

Question

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

is your output.txt already pre-processed? It feels like JSON but the syntax is wrong — Fravadona, Jul 15 '22 at 05:45
Welcome on StackOverflow. SO is not a free coding service, it is a website that helps you with **your** code. Please show what you tried up to now and explain why it did not work as expected. Alternately, you could take a look at the [help center](https://stackoverflow.com/help) and especially at the [asking section](https://stackoverflow.com/help/asking). — Renaud Pacalet, Jul 15 '22 at 06:16
@Fravadona, yes. I just filtered out some parameters via grep and put in output.txt and just to show name and PhoneNumber only. — Ener, Jul 15 '22 at 06:17
with a valid JSON and `jq` you could generate your CSV quite easily — Fravadona, Jul 15 '22 at 06:24
@Fravadona here's the json output. "result": [ { "name": "Bob", "PhoneNumber": "123-555" }, { "type": "user", "name": "Mike", "PhoneNumber": "234-555" }, { "type": "user", "name": "Amber", "PhoneNumber": "456-555" } ] — Ener, Jul 15 '22 at 07:18

Mofi · Answer 1 · 2023-08-12T13:23:09.130

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.

To understand the commands used and how they work, open a command prompt window, execute there the following commands, and read the displayed help pages for each command, entirely and 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 &.

score 0 · Answer 2 · answered Jul 16 '22 at 01:36

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

score 0 · Answer 3 · answered Jul 16 '22 at 01:53

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

score 0 · Answer 4 · answered Jul 16 '22 at 16:25

Your result_file

$ cat result.json
{
 "result": [
    {
      "name": "Bob",
      "PhoneNumber": "123-555"
    },
    {
      "type": "user",
      "name": "Mike",
      "PhoneNumber": "234-555"
    },
    {
      "type": "user",
      "name": "Amber",
      "PhoneNumber": "456-555"
    }
  ]
}

Using jq

$ jq -r '["name","PhoneNumber"],(.result[]|[.name,.PhoneNumber])|join(", ")' result.json
name, PhoneNumber
Bob, 123-555
Mike, 234-555
Amber, 456-555

vertical output to horizontal output for a specific parameter

4 Answers4