0

I have a question about how to automize the process of copying contents from a .srt file onto a .xls file.

I want to make sure that the content in the .srt file could be pasted into the corresponding columns of the .xls (e.g. the time-in to the B column; time-out to the C column; subtitles to the E column.)

In order to avoid manually copying and pasting, is there a way to script this process? Any ideas?

Thank you very much in advance! :)

UPDATE: I just found that Subtitle Edit can save .srt as csv, which will be able to change the file into an Excel file. That's handy! But there's another problem, I need to copy the content from this csv to another Excel template, which has a different structure, so I can't directly copy and paste the values in the csv. I'm working on how to make this easier...

Can't post images for now, but the situation is that while each time-in text in the srt converted csv file takes up one row, the time-in text in the Excel template takes up two rows, so I can't directly copy and paste all the texts from one excel file to the other. Is there any easier ways to do this? Thank you!

Penny
  • 1,218
  • 1
  • 13
  • 32
  • What does your .srt file look like? Please paste a snippet. What have you tried so far? – Micah Elliott Jul 28 '15 at 21:36
  • To help you more, please, paste your input data and state your desired output. Just into your question (in the similar [way](http://stackoverflow.com/questions/31684194/how-to-create-a-shell-script-to-copy-srt-content-into-corresponding-excel-col/31711448#31711448) as [buff](http://stackoverflow.com/users/328977/buff) did), you do not have to post any images. Sorry, your description is too enigmatic for me. – ryuichiro Jul 31 '15 at 19:39

2 Answers2

1

In Vim I would use:

:%s/\(.\)$\n\|-->/\1\t/g | :g/^$/d | :%s=\s\+$==

I know still not a script but now it should be easy to import it in Excel :-)

It means find the line ending with character and substitute it with that character and tabulator or find characters --> and substitute them with tabulator, and then delete empty lines, and at last remove white spaces at the end of the lines.

ryuichiro
  • 3,765
  • 1
  • 16
  • 21
1

In a script, you can use perl to do the substitution:

perl -0777 -pe 's/\n([^\n])/\t$1/g; s/ --> /\t/g' input.srt | \
perl -ne 's/^\t//; print unless /^$/' > output.csv

For this sample input

1
00:00:01,478 --> 00:00:04,020
Srt sample

2
00:00:05,045 --> 00:00:09,545
<i>italic</i> font

3
00:00:09,378 --> 00:00:13,745
<b>bold</b> font

4
00:00:14,812 --> 00:00:16,144
Multi
Line

you get the following output:

1   00:00:01,478    00:00:04,020    Srt sample
2   00:00:05,045    00:00:09,545    <i>italic</i> font
3   00:00:09,378    00:00:13,745    <b>bold</b> font
4   00:00:14,812    00:00:16,144    Multi   Line

Regarding the command: There are two chained perl commands

  • The first one does the hard work: replaces newlines and arrows with tabs (keeping double newline as one newline).

  • The second one only does some cleaning, it removes tabs from line beginnings and removes redundant empty lines.

buff
  • 2,063
  • 10
  • 16
  • Thank you so much! This is really helpful! I'm gonna try it later! – Penny Jul 29 '15 at 22:30
  • I just found that Subtitle Edit can save .srt as csv, which will be able to change the file into an Excel file. That's handy! But there's another problem, I need to copy the content from this csv to another Excel template, which has a different structure, so I can't directly copy and paste the values in the csv. I'm working on how to make this easier... – Penny Jul 29 '15 at 22:37