如何用脚本提取CSV文件中的数字？技术选型与分步执行指引

There are several straightforward ways to strip non-numeric characters from your CSV file, depending on your comfort level and environment:

• Shell Scripting: Use command-line tools like sed, awk, or grep—ideal for quick, one-off processing directly in your terminal without needing a full programming setup.
• Python: Write a simple script using string manipulation or the csv module—great if you anticipate needing to extend the logic later (like handling edge cases or additional data processing).
• Perl: Leverage its powerful regex support for text processing tasks, similar to Shell tools but with more flexibility for complex patterns.
• Excel/Google Sheets: Use built-in formulas like REGEXREPLACE(A1,"[^0-9]","") to clean each cell—perfect if you’re not comfortable with code and prefer a graphical interface.

Let’s walk through the simplest Shell solution using sed, which works across most Unix-like systems (Linux, macOS, WSL):

1. Inspect your input file first #

Before making any changes, confirm the content of students.csv to avoid mistakes:

cat students.csv

You should see your original data:

sumith123,manu456,siva789

2. Test the cleaning command (without saving) #

Run this command to preview the cleaned output immediately—no files are modified yet:

sed 's/[^0-9]//g' students.csv

You’ll see the desired result right away:

123,456,789

What this does:

• sed: The stream editor tool that processes text line by line.
• s/[^0-9]//g: This is the core substitution rule:
  • s: Short for "substitute".
  • [^0-9]: Matches any character that is not a digit (the ^ inside brackets negates the match).
  • //: Replaces those non-digit characters with nothing (i.e., deletes them).
  • g: Applies the substitution globally across the entire line (not just the first occurrence).

3. Save the cleaned output to a new file #

To preserve your original data, redirect the output to a new file:

sed 's/[^0-9]//g' students.csv > cleaned_students.csv

4. Verify the cleaned file #

Check the new file to ensure everything worked as expected:

cat cleaned_students.csv

You should see the final cleaned data:

123,456,789

5. Optional: Edit the original file in place #

If you’re confident and want to modify the original file directly (use this carefully—there’s no undo!), add the -i flag (in-place edit):

• On Linux/WSL:

  sed -i 's/[^0-9]//g' students.csv
  

• On macOS:

  sed -i '' 's/[^0-9]//g' students.csv
  

If you need more control over individual CSV fields (e.g., if your file might have non-digit characters in other contexts later), awk is a robust choice:

awk -F, '{for(i=1;i<=NF;i++) gsub(/[^0-9]/,"",$i); print}' students.csv > cleaned_students.csv

Breakdown:

• -F,: Sets the field separator to a comma, so awk treats each CSV entry as a separate field.
• for(i=1;i<=NF;i++): Loops through every field in the current line.
• gsub(/[^0-9]/,"",$i): Removes all non-digit characters from the current field.
• print: Prints the modified line back to the output.

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