如何用Bash脚本将Klipfolio表格数据提取为CSV文件

需求说明 #

需要用Bash脚本从Klipfolio公开页面提取裁判指派表格数据到CSV文件，要求包含所有行列，包括页面源码中可见的Referee、AR1及AR2的姓名。预期输出示例（前3行）：

DATE,TIME,COMPETITION,DIVISION,HOME TEAM,AWAY TEAM,VENUE,REFEREE,AR1,AR2,REFEREE NAME,AR1 NAME,AR2 NAME
Friday 2 May,18:15,Veto Sports State League (Womens),Women 1 South-East Reserves,Croydon City Soccer Club (SLW1),Ashburton United SC (SLW1),Dorset Recreation Reserve,FV Appointed,,,Kosta Kelly,,
Friday 2 May,18:30,Veto Sports State League (Womens),Women 3 South,Chelsea FC (SLW3),Monash University SC - Women's Thirds (SLW3),Edithvale Recreation Reserve,FV Appointed,,,Sarah Rogers,,
Friday 2 May,18:30,Veto Sports State League (Womens),Women 4 East,Mooroolbark SC (SLW4),Eastern Lions SC (SLW4),Esther Park,FV Appointed,,,Pearl Peckrose,,

现有脚本问题 #

当前脚本仅能获取每列前100个唯一值，原因是错误地从dashboardSchemaString中提取组件元数据，而非实际的表格行数据。现有脚本如下：

#!/bin/bash
FileBase="${HOME}/tmp/RefAppts_tmp"
RefApptUrl="https://app.klipfolio.com/published/6b16ab677623c60708ba3ef462e6ad8e/football-victoria-referee-appointments"
curl ${RefApptUrl} -o $FileBase.1

#Extact the line with the data string
cat $FileBase.1 | grep dashboardSchemaString  | head -1 > $FileBase.2

#Extract the data string from within the double quotes
cut -d"\"" -f2 $FileBase.2 > $FileBase.3

#Make readable json by unescaping the data string
echo -e $(cat $FileBase.3) > $FileBase.4

#Extract a subset of the data which looks interesting. 
jq .[].klips[].components[].components $FileBase.4 > $FileBase.5

#Extract Venues
jq '.[] | select(.displayName=="FV Venue")' $FileBase.5 > $FileBase.6

#Extract TIMEs
jq '.[] | select(.displayName=="TIME")' $FileBase.5 > $FileBase.7

#And so on for each column

正确解决方案 #

正确思路是定位页面中存储表格行数据的JSON数组，直接解析每行数据并映射到CSV列。以下是完整脚本：

#!/bin/bash
FileBase="${HOME}/tmp/RefAppts_tmp"
RefApptUrl="https://app.klipfolio.com/published/6b16ab677623c60708ba3ef462e6ad8e/football-victoria-referee-appointments"
OutputCSV="${HOME}/tmp/referee_appointments.csv"

# 1. 抓取页面源码
curl -s "$RefApptUrl" -o "$FileBase.1"

# 2. 提取包含表格数据的JSON字符串（定位到实际行数据数组）
grep -o 'window\.__KLIPFOLIO_INITIAL_STATE__ = .*;' "$FileBase.1" | sed 's/window\.__KLIPFOLIO_INITIAL_STATE__ = //; s/;$//' > "$FileBase.2"

# 3. 用jq解析JSON，提取所有行数据并转换为CSV
echo "DATE,TIME,COMPETITION,DIVISION,HOME TEAM,AWAY TEAM,VENUE,REFEREE,AR1,AR2,REFEREE NAME,AR1 NAME,AR2 NAME" > "$OutputCSV"

jq -r '
  .klips[]
  | select(.id == "klip_1")  # 定位存储裁判数据的模块（若页面结构变化需检查id）
  | .data[]
  | [
      .DATE, .TIME, .COMPETITION, .DIVISION, ."HOME TEAM", ."AWAY TEAM", .VENUE,
      .REFEREE, .AR1, .AR2,
      ."REFEREE NAME", ."AR1 NAME", ."AR2 NAME"
    ]
  | @csv
' "$FileBase.2" >> "$OutputCSV"

# 清理临时文件（可选）
rm -f "$FileBase."*

echo "CSV文件已生成：$OutputCSV"

关键说明 #

• 直接定位到window.__KLIPFOLIO_INITIAL_STATE__中的实际行数据数组，确保获取所有行数据
• 使用jq的@csv过滤器自动处理CSV格式转义（如含逗号、引号的字段）
• 通过select(.id == "klip_1")精准定位存储裁判数据的模块（页面结构变化时需重新确认klip的id）
• 自动处理空值，保证CSV列对齐

内容的提问来源于stack exchange，提问作者Stew-au