Writing multiple lines to columns instead

I have an error report that contains lots of phone numbers, I need to see if any of this data exists in our database.

Data required –

Telephone number – Routing Code – Customer ID

I’m using APIs that gather data from various sources.

The first request is to use the telephone number we already know to grab the same telephone number so I can print it in the terminal. It’s silly and a waste of resources but I wasn’t sure how to only print telephone numbers that have a valid response when querying the database.

curl -s -X GET "http://number.com/api/v1/telephone-number/$rec1" -H  "accept: application/json" -H  'X-API-Key: APIKEY'

Raw response

{
"status": "success",
"data": {
    "telephone_number": {
        "telephone_number": 11111111111,
        "account_reference": "CS039890",
        "unavailable_until": null,
        "links": {
            "self": "/api/v1/telephone-number/11111111111",
            "range_holder": "/api/v1/communications-provider/5237",
            "current_provider": "/api/v1/communications-provider/5237",
            "value_tier": "/api/v1/value-tier/ed4b60b9"
        }
    }
}

I add a filter to only show the telephone number.

| grep telephone_number | sed 's/^[^0-9]*//' | tr -d ','

Filtered Response –

11111111111

Next, I’m getting the routing code of the number –

curl -s -X GET "http://number.com/api/v1/portability/route/$rec1" -H  "accept: application/json" -H  'X-API-Key: APIKEY'

Raw Response –

{
    "status": "success",
    "data": {
        "portability_route": {
            "telephone_number": 11111111111,
            "nppc": 521393
        }
    }

Again I added a filter to display only the routing code –

| grep nppc | sed ‘s/^[^0-9]*//’

Filtered output –

Finally, I need to retrieve the customer ID –

curl -s -X GET "http://number.com/api/v1/telephone-number/$rec1" -H  "accept: application/json" -H  'X-API-Key: APIKEY'

Raw Response –

"status": "success",
"data": {
    "telephone_number": {
        "telephone_number": 11111111111,
        "account_reference": "CS039890",
        "unavailable_until": null,
        "links": {
            "self": "/api/v1/telephone-number/11111111111",
            "range_holder": "/api/v1/communications-provider/5237c92e",
            "current_provider": "/api/v1/communications-provider/5237c92e",
            "value_tier": "/api/v1/value-tier/ed4b60b9"
        }
    }
}

Add the filter –

| grep CS | sed 's/^.*CS/CS/' | tr -d '"' | tr -d ','

Filtered Output –

CS039890

So the final output looks like this –

11111111111
521393
CS039890

At this point, I’m stuck on how to combine this data into a single line separated by commas and export it to a file. There can be 10k+ numbers to process so each result would need to be written to a new line. Example –

11111111111,521393,CS039890
11111111112,521393,CS039891

The full code –

#Cleanup
rm Output/temp*.* 2> /dev/null

#Check file exists and removes underscores 
if tail -n +2 Input/Errors*.csv | tr -d _ > Output/temp.csv ; then
 echo ""
 echo "Input File Found!"
 echo " "
else
 echo ""
 echo "Error - Input file is missing!"
 echo ""
 exit
fi

#Remove leading 0 from the third column 
awk 'BEGIN{FS=OFS=","}{sub(/^0/, "", $3)}1' Output/temp.csv >> Output/temp2.csv

#Add headers to file 
sed -i '1iError Code, Calls, A Number, B Number, Customer, Area, Cost, NPPC, CS' Output/temp.csv

#Read Telephone numbers from column 3 in file temp2.csv
while IFS="," read -r rec1
do
  #echo "$rec1"
  curl -s -X GET "http://number.com/api/v1/telephone-number/$rec1" -H  "accept: application/json" -H  'X-API-Key: APIKEY'  | grep telephone_number | sed 's/^[^0-9]*//' | tr -d ','

  curl -s -X GET "http://number.com/api/v1/portability/route/$rec1" -H  "accept: application/json" -H  'X-API-Key: APIKEY' | grep nppc | sed 's/^[^0-9]*//' # >> Output/NPPC.txt

  curl -s -X GET "http://number.com/api/v1/telephone-number/$rec1" -H  "accept: application/json" -H  'X-API-Key: APIKEY'  | grep CS | sed 's/^.*CS/CS/' | tr -d '"' | tr -d ','
done < <(cut -d "," -f3 Output/temp2.csv | tail -n +2)

Answer

Use command substitution: by placing the command in $(...) you run the command and capture its output in a variable that you can use as needed.

Do only one curl call and save the result in a variable:

curled="$(curl -s -X GET "http://number.com/api/v1/telephone-number/$rec1" -H  "accept: application/json" -H  'X-API-Key: APIKEY')"

Then use that variable in your greps and store the results in another variable:

phone="$(echo "$curled" | sed 's/^[^0-9]*//' | tr -d ',')"
nppc="$(echo "$curled" | grep nppc | sed 's/^[^0-9]*//')"
cs="$(echo "$curled" | grep CS | sed 's/^.*CS/CS/' | tr -d '"' | tr -d ',')"

Then echo these as you like:

echo "$phone,$nppc,$cs" > output-file.csv

That said, you could also look into jq to parse the JSON. sed and grep are probably very brittle.

Advertisement

Answer