如何将SRT字幕文件转换为数据集？能否格式化SRT文件为数据集？

Hey Dave, great question! Converting SRT subtitle files into structured datasets (and formatting them to match your desired style in Excel) is a common task, and I’ve got you covered with both manual and automated approaches.

Before diving in, it helps to know how SRT files are organized—each subtitle entry follows this repeating pattern:

[Subtitle Number]
[Start Timestamp] --> [End Timestamp]
[Subtitle Text (can be one or multiple lines)]
[Blank Line]

This consistent repetition is perfect for turning into a tabular dataset with columns like Subtitle ID, Start Time, End Time, and Subtitle Content.

You’ve got two main paths here, depending on how many files you’re working with:

Option A: Manual Conversion (Small Files) #

If you only have one or a few SRT files, Excel’s Power Query tool is your friend:

• Open Excel, go to the Data tab > Get Data > From File > From Text/CSV.
• Select your SRT file, choose Delimiter as None, then click Load To > Only Create Connection.
• Go back to Data > Queries & Connections, right-click the new query > Edit.
• In the Power Query Editor:
  1. Add an index column (Add Column > Index Column > From 1) to track rows.
  2. Use the Group By feature: Group by Index divided by 4 (since each subtitle block is 4 rows: number, timestamp, text, blank), then aggregate rows into a list.
  3. Expand the list into columns, then clean up:
     • Column 1 = Subtitle ID (convert to number)
     • Column 2 = Split into Start Time and End Time (split on -->)
     • Column 3 = Subtitle Content (keep as text; merge multi-line text if needed)
  4. Remove any empty rows, then close and load the cleaned data into a worksheet.

Option B: Automated Conversion (Large Files/Bulk Processing) #

For multiple files or larger datasets, use Python to script the conversion—it’s faster and less error-prone. Here’s a simple example using the pysrt library:

1. Install the library first:

   pip install pysrt
   

2. Run this script to convert SRT to a CSV (which you can easily import into Excel):

   import pysrt
   import pandas as pd
   
   # Load the SRT file
   subtitles = pysrt.open("your_subtitle.srt")
   
   # Create a list to hold our dataset rows
   data = []
   for sub in subtitles:
       # Convert timestamps to readable strings
       start_time = f"{sub.start.hours:02d}:{sub.start.minutes:02d}:{sub.start.seconds:02d},{sub.start.milliseconds:03d}"
       end_time = f"{sub.end.hours:02d}:{sub.end.minutes:02d}:{sub.end.seconds:02d},{sub.end.milliseconds:03d}"
       # Add row to data list
       data.append({
           "Subtitle ID": sub.index,
           "Start Time": start_time,
           "End Time": end_time,
           "Subtitle Content": sub.text.replace("\n", " ")  # Replace line breaks with spaces, or keep as is
       })
   
   # Convert to DataFrame and save as CSV
   df = pd.DataFrame(data)
   df.to_csv("subtitle_dataset.csv", index=False)
   

Once you have the CSV, just import it into Excel—you’ll get a clean, structured table right away.

Now that you have the structured data, formatting it to your desired look is straightforward:

• Timestamp Columns: Select the Start Time and End Time columns, go to Home > Number Format > choose Custom and enter hh:mm:ss,000 to match SRT timestamp formatting.
• Subtitle Content: If you want multi-line text to display properly, select the column, right-click > Format Cells > Alignment > check Wrap Text.
• Repeating Styles: If you want alternating row colors or consistent cell styles for each subtitle entry, use Home > Conditional Formatting > New Rule—for example, apply a light gray fill to every even row to distinguish blocks.
• Merge/Adjust Layout: If your "specified style" requires merging certain cells or adjusting column widths, use Excel’s standard formatting tools to tweak until it matches your needs.

Let me know if you need help refining any part of this—happy to adjust based on your exact desired format!

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