Filemaker | convert subtitle (.srt) text files to text only and Excel
Someone had asked me if they knew of a way to get .srt in to a more readable format like a word document and also an excel formatted sheet – it was something they needed to do with a large number of subtitle files. I instantly thought of Filemaker, (if I had had more time I would probably of done a nice fancy regex (regular expressions) file, but I didn’t) so using all very native commands I created a conversion process – and it only took me about 30 mins all in all.
.srt files are formatted like this:
00:00:17,794 –> 00:00:22,160
“Some of the biggest men in the United States,
in the Field of commerce and manufacture are afraid of something.
00:00:23,873 –> 00:00:28,707
They know that there is a power somewhere so organized,
so subtle, so watchful, so interlocked, so complete, so pervasive,
00:00:30,765 –> 00:00:39,936
that they better not speak above their breath when they speak in condemnation of it.”
~Woodrow Wilson, former President of the United States
So the basic structure is:
Subtitle number ID
Start time –> End time
Text of subtitle (one or more lines)
This looked simple enough and so I got to work.
First thing to sort out was the fields needed. I decided to setup a global field to contain the .srt text and a number field to indicate which line I would like to display on top of that I then needed 2 calculation fields to create the start and end points of the text for the specific subtitle number ID. Then finally 3 fields needed to be created to display start time, end time and text in separate fields.
Using these the plan was to setup a loop which would go through from 1 up to however big the last subtitle number ID was in the .srt text – creating a new record for each and extract just the text needed and adding it to the previous ID extraction in another global field (of course the loop would simply exit when the calculated text field was empty i.e. end of file).
At the end you would end up with X records all with the relevant start time, end time and specific text in separate fields (which can be exported to excel) and a global field which can then be copied and pasted in to another application with the full text from all the IDs.
There were a few things extra I added to these which I saw from the examples files I was given – that was the use of ” and ~ these were used to start some of the text as quotes and the ~ was used to show who the quote was from. For these I added extra paragraph returns so the text was to some extent better arranged to read.
The open file is below – just remember to toggle the ” and ~ option if you use it with other .srt files that don’t follow this formatting. Also note that once you have run this process it will automatically export a csv file to your desktop and open it.
Let me know how you would of chosen to do this and any bugs you find? Thanks
You’re currently reading “Filemaker | convert subtitle (.srt) text files to text only and Excel”.
- Sunday, August 10th, 2008
- Simon Page
- Artwork (75)
- Design Inspiration (33)
- Design Resources (10)
- Excel (12)
- Filemaker (5)
- Gadgets (26)
- Music (3)
- Photography (3)
- Photoshop (1)
- Press (7)
- Random (5)
- Software (4)
- Video Gaming (15)
- Wallpaper (6)
- Album Cover Artwork Inspiration
- Creative Movie Poster Inspiration
- Criss Cross - iPad and iPhone Wallpaper
- Design Inspiration | typography posters
- Exclusive iPad Wallpaper
- Futurism - Limited Edition Prints
- Geometric Silk Scarves
- Geometric Silk Scarves #2
- Inspiring Gallery of Video Game Concept Art
- International Year of Astronomy 2009 Posters
- International Year of Chemistry 2011 Prints
- iPad HD Retina Wallpaper