如何从200+TXT文件提取指定信息并导出至XLS文件

Got it, dealing with 200+ PM7 output files one by one with sed is such a drag—here are a couple of efficient, scalable ways to batch extract your target parameters (like HEAT OF FORMATION and TOTAL ENERGY) and export them to a format Excel can work with (we’ll use CSV first, since it’s trivial to convert to XLS/XLSX):

Option 1: Bash Shell Script (Quick & Command-Line Friendly) #

If you’re comfortable with the terminal, this script will loop through all your .txt files, extract the values with sed, and build a CSV file you can open directly in Excel.

# First, write the CSV header
echo "Filename,HEAT OF FORMATION,TOTAL ENERGY" > pm7_results.csv

# Loop through all .txt files in the current directory
for file in *.txt; do
    # Extract HEAT OF FORMATION (adjust the regex if your output line format differs)
    heat_formation=$(sed -n 's/^.*HEAT OF FORMATION = *\([0-9.-]*\).*/\1/p' "$file")
    # Extract TOTAL ENERGY
    total_energy=$(sed -n 's/^.*TOTAL ENERGY = *\([0-9.-]*\).*/\1/p' "$file")
    # Write to CSV (wrap filename in quotes to handle spaces/special characters)
    echo "\"$file\",\"$heat_formation\",\"$total_energy\"" >> pm7_results.csv
done

How to use: #

• Save this as extract_pm7.sh in the folder with your TXT files.
• Make it executable: chmod +x extract_pm7.sh
• Run it: ./extract_pm7.sh
• Open pm7_results.csv in Excel, then save it as an XLS/XLSX file if needed.

Note: #

If your PM7 output lines have different formatting (e.g., extra spaces, different units), tweak the sed regex. For example, if lines start with whitespace, change ^.* to ^[[:space:]]*.

Option 2: Python Script (Flexible & Robust) #

If you need to add more parameters later, or want better handling of missing values, a Python script is the way to go. It’s more readable and less prone to regex edge cases.

import os
import csv
import re

# Define the parameters you want to extract and their matching regex patterns
target_params = {
    "Filename": None,
    "HEAT OF FORMATION": r"HEAT OF FORMATION = *([0-9.-]+)",
    "TOTAL ENERGY": r"TOTAL ENERGY = *([0-9.-]+)"
    # Add more parameters here, e.g., "DIPOLE MOMENT": r"DIPOLE MOMENT = *([0-9.-]+)"
}

# Set up the output CSV file
output_path = "pm7_results.csv"
with open(output_path, 'w', newline='', encoding='utf-8') as csv_file:
    writer = csv.DictWriter(csv_file, fieldnames=target_params.keys())
    writer.writeheader()

    # Iterate over all TXT files in the current directory
    for filename in os.listdir('.'):
        if filename.lower().endswith('.txt'):
            result = {"Filename": filename}
            # Read the entire file content
            with open(filename, 'r', encoding='utf-8') as txt_file:
                content = txt_file.read()
                # Match each parameter against the content
                for param, pattern in target_params.items():
                    if pattern:  # Skip Filename since we set it manually
                        match = re.search(pattern, content)
                        result[param] = match.group(1) if match else "N/A"
            # Write the result row to CSV
            writer.writerow(result)

print(f"Extraction complete! Results saved to {output_path}")

How to use: #

• Save this as extract_pm7.py in your TXT folder.
• Run it with Python: python3 extract_pm7.py (or just python extract_pm7.py on Windows).
• Open the generated CSV in Excel and save as XLS/XLSX.

Benefits: #

• Easily add new parameters by extending the target_params dictionary.
• Automatically fills "N/A" if a parameter is missing from a file, so the script doesn’t crash.
• Handles special characters in filenames smoothly.

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