Transcription, but less tedious?
February 11, 2020 10:35 PM Subscribe
I have received my first and somewhat sudden transcription request and find it tedious. Am I doing it wrong?
I am currently between jobs and was asked by a relative whether I would do a transcription/translation for a bit of money. It's an audio file in language A, and I only need to provide the script in language B, and can leave out irrelevant utterances. I sent a sample of a few minutes in word and it was okayed. As a fast typer and someone who used to interpret before, this was fairly easy, but when I was done with the first file, I was told the client wanted everything typed in Excel with time stamps. I understand that that is probably fairly normal and I my initial setup was most likely too good to be true, and am now spending hours going through my file again, copying it into Excel by speaker and adding time stamps.
I have two more audio files to translate and time stamp, and I dread it. Am I doing this in an unnecessarily slow way or is stopping the audio file to check the time and typing in the translation into the corresponding cell really the only way?
I am currently between jobs and was asked by a relative whether I would do a transcription/translation for a bit of money. It's an audio file in language A, and I only need to provide the script in language B, and can leave out irrelevant utterances. I sent a sample of a few minutes in word and it was okayed. As a fast typer and someone who used to interpret before, this was fairly easy, but when I was done with the first file, I was told the client wanted everything typed in Excel with time stamps. I understand that that is probably fairly normal and I my initial setup was most likely too good to be true, and am now spending hours going through my file again, copying it into Excel by speaker and adding time stamps.
I have two more audio files to translate and time stamp, and I dread it. Am I doing this in an unnecessarily slow way or is stopping the audio file to check the time and typing in the translation into the corresponding cell really the only way?
Best answer: If generating Word documents as a first cut is a workflow that suits you, you shouldn't need to type all the content again just to make that into an Excel sheet; reformatting as a Word table, possibly with a bit of helper VBScript, should let you import the text into Excel with very few issues.
Once the text is in Excel, it ought to be possible to add time stamps just by listening to the source audio and clicking in a cell at the start and end of each utterance. Again, my weapon of choice for this would be VBScript, and the approach I'd use would start with writing a macro that inserted the current time of day into the active cell, then attaching that macro to each cell in a column as an on-click action. Once the time stamps had been clicked in, they could then be made relative to the start of the source audio by subtracting a fixed starting time from each.
posted by flabdablet at 11:32 PM on February 11, 2020
Once the text is in Excel, it ought to be possible to add time stamps just by listening to the source audio and clicking in a cell at the start and end of each utterance. Again, my weapon of choice for this would be VBScript, and the approach I'd use would start with writing a macro that inserted the current time of day into the active cell, then attaching that macro to each cell in a column as an on-click action. Once the time stamps had been clicked in, they could then be made relative to the start of the source audio by subtracting a fixed starting time from each.
posted by flabdablet at 11:32 PM on February 11, 2020
Response by poster: flabdablet, that sounds amazing! Do you have any sort of guide for your way of creating macros? I don't usually use Excel because I normally work verbally.
posted by LoonyLovegood at 11:39 PM on February 11, 2020
posted by LoonyLovegood at 11:39 PM on February 11, 2020
Best answer: I don't currently know how to do any of that in detail, but I am absolutely certain it could be done without vast amounts of work. Here are some of the places I've been looking for clues:
Quick start: Create a macro
How to Assign a Macro to a Cell in Excel
How to Change a Cell Value With VBA
VBA Time function
VBA TimeValue function
Let me know if the approach would actually work better for you than whatever off-the-shelf recommendations you get from the rest of this thread. If it would, I'll get the same version of Office you're working with going in a VM and see what I can do (this could take me a few hours of enjoyable yarrr, Windows and Office not being my daily drivers).
posted by flabdablet at 12:08 AM on February 12, 2020 [5 favorites]
Quick start: Create a macro
How to Assign a Macro to a Cell in Excel
How to Change a Cell Value With VBA
VBA Time function
VBA TimeValue function
Let me know if the approach would actually work better for you than whatever off-the-shelf recommendations you get from the rest of this thread. If it would, I'll get the same version of Office you're working with going in a VM and see what I can do (this could take me a few hours of enjoyable yarrr, Windows and Office not being my daily drivers).
posted by flabdablet at 12:08 AM on February 12, 2020 [5 favorites]
Best answer: I have no translation skills whatsoever, but I'm pretty keen on building flexible automated workflows out of free software. Is there a way you could get me a sample of the audio you need to transcribe and translate? I'd be curious to see whether I could use free software to automate breaking it into a set of timestamp-named snippet files based on finding silent regions, then creating a script that would let you do the following for a selection of such snippet files:
1. Open the audio snippet in a VLC Media Player window only just big enough to expose the playback controls, allowing you to play it repeatedly, pause it, and so forth in order to generate a transcription.
2. At the same time, open a new text file in Notepad or some similarly low-effort plain text editor, with a name that relates to the audio snippet file, for you to type the transcription/translation into. The first line could be a code (perhaps as simple as a single letter) that identifies which speaker the utterance belongs to.
3. Detect closure of both the VLC and Notepad windows, then repeat from step 1 until all the initially selected snippet files have been processed.
After doing the transcriptions, you could then drag and drop a selection of the resulting timestamp-named text files onto another script, which would merge them all into an Excel-compatible CSV output file that presents timestamp, speaker and text information in whatever format you like.
posted by flabdablet at 12:35 AM on February 12, 2020
1. Open the audio snippet in a VLC Media Player window only just big enough to expose the playback controls, allowing you to play it repeatedly, pause it, and so forth in order to generate a transcription.
2. At the same time, open a new text file in Notepad or some similarly low-effort plain text editor, with a name that relates to the audio snippet file, for you to type the transcription/translation into. The first line could be a code (perhaps as simple as a single letter) that identifies which speaker the utterance belongs to.
3. Detect closure of both the VLC and Notepad windows, then repeat from step 1 until all the initially selected snippet files have been processed.
After doing the transcriptions, you could then drag and drop a selection of the resulting timestamp-named text files onto another script, which would merge them all into an Excel-compatible CSV output file that presents timestamp, speaker and text information in whatever format you like.
posted by flabdablet at 12:35 AM on February 12, 2020
Best answer: If you’re re-listening in real-time to add time stamps, you could just use “CTRL ;” (or maybe “CTRL-shift ;” - One is date, one is datetime) to enter the current time in the current cell. So ctrl-; downarrow, wait for start of next bit, repeat. After that, subtract the start datetime from each of the entries, to get the offset. (Weird excel date/86400= seconds).
posted by pompomtom at 1:10 AM on February 12, 2020 [3 favorites]
posted by pompomtom at 1:10 AM on February 12, 2020 [3 favorites]
Do you have a foot pedal? I have set hotkeys with my transcription software and a foot pedal so that I can very easily add timestamps with just a ctrl+shift+t as I'm typing. Express scribe has an option for hot keying a timestamp and I believe that "ctrl shift t" is the default. It looks like there are a few tutorials online about how to set up hotkeys in that software.
A foot pedal also allows you to rewind or fast forward by a few seconds and pause very very easily so you're not doing all of that work with your hands, which can slow you down. I also find that slowing down the audio slightly makes me transcribe a lot faster because I'm pausing a lot less. Having a foot pedal is really worth your time if you are going to be doing any volume of transcription. I believe I have an Infinity pedal and I purchased it for $50 about 10 years ago, it looks like they still cost $50 now. YMMV depending on location.
Transcription is pretty dull. It generally takes about 4 minutes to transcribe 1 minute of audio for an average transcriptionist, and that's in the same language. I would imagine that translating it over to a different language is more time consuming. Good luck finishing this out.
posted by k8lin at 1:43 AM on February 12, 2020 [5 favorites]
A foot pedal also allows you to rewind or fast forward by a few seconds and pause very very easily so you're not doing all of that work with your hands, which can slow you down. I also find that slowing down the audio slightly makes me transcribe a lot faster because I'm pausing a lot less. Having a foot pedal is really worth your time if you are going to be doing any volume of transcription. I believe I have an Infinity pedal and I purchased it for $50 about 10 years ago, it looks like they still cost $50 now. YMMV depending on location.
Transcription is pretty dull. It generally takes about 4 minutes to transcribe 1 minute of audio for an average transcriptionist, and that's in the same language. I would imagine that translating it over to a different language is more time consuming. Good luck finishing this out.
posted by k8lin at 1:43 AM on February 12, 2020 [5 favorites]
So I use ELAN for transcription. It's designed for much more complex transcription tasks than what you want to do, but it does have the functionality you need, even though the learning curve is pretty steep. (Less steep than building macros, though!).
You can definitely break up the audio into snippets either based on silences, or by doing a first pass through using segmentation mode, where you press a key to introduce breaks.
You transcribe using key shortcuts to move to the next snippet, and to play, pause or replay the aligned audio.
You can export as comma delimited files (which can then be opened with Excel) with timestamps, and then you could open them up in excel and easily enough manipulate the format if they aren't quite the way you want them.
posted by lollusc at 2:01 AM on February 12, 2020 [2 favorites]
You can definitely break up the audio into snippets either based on silences, or by doing a first pass through using segmentation mode, where you press a key to introduce breaks.
You transcribe using key shortcuts to move to the next snippet, and to play, pause or replay the aligned audio.
You can export as comma delimited files (which can then be opened with Excel) with timestamps, and then you could open them up in excel and easily enough manipulate the format if they aren't quite the way you want them.
posted by lollusc at 2:01 AM on February 12, 2020 [2 favorites]
I've also heard good things about how much more usable F4Transkript is than ELAN if you don't want the fancy extra linguistics features of the latter. I haven't used it myself, though, and it's not totally free like ELAN. I think it can still do everything you need.
posted by lollusc at 2:03 AM on February 12, 2020 [1 favorite]
posted by lollusc at 2:03 AM on February 12, 2020 [1 favorite]
Response by poster: Thank you, everyone! Unfortunately, I don't have any equipment because I am not usually a transcriber, and this just came in with the deadline being in a few days. They want everything in Excel, which they sprang on me after I accepted, so if there is any fancy software that does timestamps, I also need to be able to put them into Excel later, and not just in one cell, but time IN, time OUT, and the utterance are all in separate lines. I am banging my head against the wall... >.<
posted by LoonyLovegood at 5:08 AM on February 12, 2020
posted by LoonyLovegood at 5:08 AM on February 12, 2020
Best answer: They want everything in Excel, which they sprang on me after I accepted, so if there is any fancy software that does timestamps, I also need to be able to put them into Excel later, and not just in one cell, but time IN, time OUT, and the utterance are all in separate lines.
As a freelancer, you are justified in raising your rates if the client adds more work after you agree to the job. I know working for family is complicated, making the request retroactive is complicated, and it might not feel like an option in your case. But for future reference, it is a perfectly ethical and normal thing to do.
posted by nebulawindphone at 5:29 AM on February 12, 2020 [11 favorites]
As a freelancer, you are justified in raising your rates if the client adds more work after you agree to the job. I know working for family is complicated, making the request retroactive is complicated, and it might not feel like an option in your case. But for future reference, it is a perfectly ethical and normal thing to do.
posted by nebulawindphone at 5:29 AM on February 12, 2020 [11 favorites]
I also need to be able to put them into Excel later, and not just in one cell, but time IN, time OUT, and the utterance are all in separate lines.
So time IN is the time the utterance you're translating starts, time OUT is the time it finishes, all times being relative to the start of the original audio file, yes? Presumably this is all eventually destined for generating closed captions or some similar appearing/disappearing text?
Is there a sample Excel file available that demonstrates the output format you need?
posted by flabdablet at 6:48 AM on February 12, 2020
So time IN is the time the utterance you're translating starts, time OUT is the time it finishes, all times being relative to the start of the original audio file, yes? Presumably this is all eventually destined for generating closed captions or some similar appearing/disappearing text?
Is there a sample Excel file available that demonstrates the output format you need?
posted by flabdablet at 6:48 AM on February 12, 2020
Also, are the original audio files nice clean speech-only with good silence between utterances, or are we talking film soundtracks or what?
posted by flabdablet at 6:50 AM on February 12, 2020
posted by flabdablet at 6:50 AM on February 12, 2020
Also also, what operating system are you running on the box you're using for this job, and what version of MS Office have you got?
posted by flabdablet at 6:55 AM on February 12, 2020
posted by flabdablet at 6:55 AM on February 12, 2020
Response by poster: fladdablet, I PM'ed you to keep this within the guidelines of MeFi.
posted by LoonyLovegood at 6:58 AM on February 12, 2020
posted by LoonyLovegood at 6:58 AM on February 12, 2020
I need to transcribe interviews as part of my job and use Otter.ai, a free AI transcription tool- I'm not sure what language you're starting with or if AI transcription is only available in English, but Otter and other English tools I've tried automatically include time stamps for speakers. It would be easy to import that data into Excel and then translate the written text instead of doing all of the steps yourself.
posted by pinochiette at 8:02 AM on February 12, 2020 [1 favorite]
posted by pinochiette at 8:02 AM on February 12, 2020 [1 favorite]
I also need to be able to put them into Excel later, and not just in one cell, but time IN, time OUT, and the utterance are all in separate lines
I'm pretty sure the software I mentioned can do this. ELAN for example has a whole lot of different options for export in terms of where the timestamps go and what other info is included and the exported files can be opened in Excel. (And then if the format is still not perfect, you can almost certainly automatically convert it to what you need using built in Excel functions, or worst case, macros.)
posted by lollusc at 6:10 PM on February 12, 2020 [1 favorite]
I'm pretty sure the software I mentioned can do this. ELAN for example has a whole lot of different options for export in terms of where the timestamps go and what other info is included and the exported files can be opened in Excel. (And then if the format is still not perfect, you can almost certainly automatically convert it to what you need using built in Excel functions, or worst case, macros.)
posted by lollusc at 6:10 PM on February 12, 2020 [1 favorite]
This thread is closed to new comments.
The Excel file has four colums, "Time In", "Time Out", "Moderator" and "interviewee", btw.
posted by LoonyLovegood at 10:44 PM on February 11, 2020