如何将多API返回的多行数据合并为逗号分隔列并导出

核心修改思路 #

把每个API调用的结果存成变量，循环末尾将三个变量用逗号拼接成一行，直接写入结果文件，就能解决当前每行单独输出的问题。

修改后的完整脚本 #

#Cleanup
rm Output/temp*.* 2> /dev/null
rm Output/result.csv 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

# 写入结果文件的表头
echo "Telephone Number,Routing Code,Customer ID" > Output/result.csv

#Read Telephone numbers from column 3 in file temp2.csv
while IFS="," read -r rec1
do
  # 获取电话号码并存储
  phone=$(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 ',')

  # 获取路由码并存储
  route=$(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]*//')

  # 获取客户ID并存储
  cust_id=$(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 ',')

  # 拼接成单行写入结果文件
  echo "$phone,$route,$cust_id" >> Output/result.csv
done < <(cut -d "," -f3 Output/temp2.csv | tail -n +2)

echo "处理完成，结果已保存至 Output/result.csv"

关键修改点 #

• 变量存储API结果：用$(...)捕获每个curl的输出，赋值给变量，避免直接输出换行。
• 单行拼接输出：把三个变量用逗号连起来，一行输出到结果文件，符合格式要求。
• 修正temp2.csv生成：原脚本用>>会导致重复数据，改成>覆盖生成干净的文件。
• 添加结果表头：给输出文件加上表头，方便后续查看。

10000+数据的效率优化 #

串行处理一万条数据会很慢，推荐用parallel做并发请求，大幅提升速度：

# 先把所有号码提取到单独文件
cut -d "," -f3 Output/temp2.csv | tail -n +2 > Output/numbers.txt

# 并行处理，-j控制并发数（根据API限流调整，比如10）
parallel -j 10 '
  phone=$(curl -s -X GET "http://number.com/api/v1/telephone-number/{}" -H "accept: application/json" -H "X-API-Key: APIKEY" | grep telephone_number | sed "s/^[^0-9]*//" | tr -d ",")
  route=$(curl -s -X GET "http://number.com/api/v1/portability/route/{}" -H "accept: application/json" -H "X-API-Key: APIKEY" | grep nppc | sed "s/^[^0-9]*//")
  cust_id=$(curl -s -X GET "http://number.com/api/v1/telephone-number/{}" -H "accept: application/json" -H "X-API-Key: APIKEY" | grep CS | sed "s/^.*CS/CS/" | tr -d "\"" | tr -d ",")
  echo "$phone,$route,$cust_id"
' < Output/numbers.txt > Output/result.csv

注意：并发数别设太高，先拿少量数据测试，避免被API限流。

内容的提问来源于stack exchange，提问作者gareth0402