使用curl读取文件中的ID并拉取对应关联数据写入新文件

Great question—this is exactly the kind of problem where combining curl with a simple shell script saves you tons of time and bandwidth. No need to pull all 300k records and filter later! Here’s how to do it step by step:

Step 1: Get Your IDs into a Plain Text File #

Curl can’t read Excel files directly, so first we need to extract the 6,000 IDs into a simple text file where each ID is on its own line. Here’s how:

• In Excel, select the column with your IDs, copy it, and paste into a text editor (like Notepad or TextEdit). Save the file as ids.txt.
• If your IDs are in a CSV file (exported from Excel), you can extract the ID column with a quick command:

  # Replace $1 with the column number your IDs are in (e.g., $2 for the second column)
  awk -F ',' '{print $1}' your_excel_export.csv > ids.txt
  

  Add NR>1 to skip the header row if your CSV has one:

  awk -F ',' 'NR>1 {print $1}' your_excel_export.csv > ids.txt
  

Step 2: Write a Shell Script to Fetch Data for Each ID #

Assuming your database exposes an API that returns user data (like name and email) when you pass an ID (e.g., https://your-api-url.com/users/12345), here’s a script that loops through each ID, fetches the data, and saves it to a CSV file (easy to import back into Excel):

#!/bin/bash

# Define where to save the results
OUTPUT_FILE="user_data.csv"

# Write a header row for the CSV
echo "ID,Name,Email" > "$OUTPUT_FILE"

# Loop through each ID in ids.txt
while IFS= read -r id; do
    # Skip empty lines (in case your text file has them)
    [[ -z "$id" ]] && continue

    echo "Fetching data for ID: $id"

    # Use curl to fetch the user data (replace the URL with your actual API endpoint)
    RESPONSE=$(curl -s "https://your-api-url.com/users/$id")

    # Check if the curl request succeeded
    if [[ $? -ne 0 ]]; then
        echo "$id,Error fetching data,Error" >> "$OUTPUT_FILE"
        continue
    fi

    # Extract name and email from the JSON response (use jq for parsing)
    # You’ll need to adjust the .name and .email to match your API’s JSON structure
    NAME=$(echo "$RESPONSE" | jq -r '.name')
    EMAIL=$(echo "$RESPONSE" | jq -r '.email')

    # Write the result to the CSV
    echo "$id,$NAME,$EMAIL" >> "$OUTPUT_FILE"
done < "ids.txt"

echo "All done! Results saved to $OUTPUT_FILE"

Notes for this script: #

• Install jq: This tool parses JSON easily. On Linux, run sudo apt install jq; on macOS, use brew install jq.
• Adjust the API URL: Replace https://your-api-url.com/users/$id with the actual endpoint that returns user data for a given ID.
• If your API uses POST: If you need to send the ID in a request body instead of the URL, update the curl command like this:

  RESPONSE=$(curl -s -X POST -H "Content-Type: application/json" -d '{"id": "'"$id"'"}' "https://your-api-url.com/users/fetch")
  

Optional: Speed Up with Parallel Requests #

If 6,000 requests take too long running one at a time, you can use xargs to run multiple requests in parallel. Just be careful not to hit your API’s rate limits!

# Run 10 requests at once (adjust -P to change the number of parallel processes)
cat ids.txt | xargs -P 10 -I {} bash -c '
    id={}
    response=$(curl -s "https://your-api-url.com/users/$id")
    name=$(echo "$response" | jq -r '.name')
    email=$(echo "$response" | jq -r '.email')
    echo "$id,$name,$email"
' > user_data.csv

Add a small delay (sleep 0.1) inside the bash command if your API has strict rate limits to avoid getting blocked.

Key Tips to Avoid Headaches #

• Test with a small ID list first: Run the script with 5-10 IDs to make sure it works before processing all 6,000.
• Check for rate limits: Most APIs limit how many requests you can make per minute/hour. If you get errors, slow down the script with sleep 1 (1 second delay between requests).
• Handle missing data: If some IDs don’t exist, the script will write "null" (from jq) or an error message—you can clean that up later in Excel.

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