[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『字幕ファイル（テキストファイル）を修正で』（凝り性）
字幕ファイル（テキストファイル）を修正しています。
形式は、1）-4）の集まり（部分）を一つとして扱います。
１）半角スペース＋番号+半角スペース
２）時間部分
３）字幕相当（1行又は２行）又は改行文字
４）改行文字
例えば、
1 00:00:12,291 --> 00:00:17,291 ここから始まる
2 00:00:38,625 --> 00:00:39,916 その２
3 00:00:51,083 --> 00:00:53,333
その3
4 00:01:02,250 --> 00:01:03,875 その4 その5
5 00:01:07,000 --> 00:01:08,16
6 その6
この中で「_5_」には、改行だけで字幕相当はありません。
’-----------------------------------------------------------------
希望は、「_5_」ように字幕が無い部分は一括削除した後で
連番が崩れてしまうので通し番号にふりなおす
たとえで言うと
5を削除すると、6の部分が5となるイメージです。
’----------------------------------------------------------------
そこで、以下の処理をEXCEL（VBA）処理したいと思います。
指定の字幕ファイル（テキストファイル）を読み込んで
最初の１）の番号部分をA列に、B列に対応する２）の時間部分、
以後同じくC列-E列に３）に字幕部分、最後の列に改行文字を抽出する。
これができれば、後の処理は自分で何とかできそうです。
アドバイスをお願いします。
＜ 使用 Excel：Excel2021、使用 OS：Windows11 ＞
RANGE(”A1”）に数字の1が有って同じA列の数行先に数字の２があります。
両数字の間の各行にある文字列を別シートの列にそれぞれ配置するVBAのマクロを知りたい。
但し、改行コードのみの場合は、全角スペースに変換の事
例えば、Sheet1に以下ならば
|[A]
[1]| 1
[2]|TOKYO
[3]|OSAKA
[4]|
[5]|NAGOYA
[6]| 2
Sheet2に
|[A]|[B] |[C] |[D]|[E]
[1]| 1|TOKYO|OSAKA| |NAGOYA
[2]| 2| | | |
（凝り性） 2023/10/20(金) 13:41:43
Option Explicit
Sub OneInstanceMain()
Dim i&, r, y&, x&
With Worksheets("Sheet1")
Set r = .Cells(1).CurrentRegion
End With
With Worksheets("Sheet2")
.UsedRange.Clear
If r(1, 1) = 1 Then
y = r(1, 1)
Else
MsgBox "ERR"
Exit Sub
End If
Do
i = i + 1
If r(i, 1) = 2 Then
y = r(i, 1)
.Cells(y, 1) = r(i, 1)
Exit Do
End If
x = x + 1
.Cells(y, x) = r(i, 1)
Loop
End With
End Sub
（隠居Z） 2023/10/20(金) 16:01:41
単純化した（サンプル）コードでは上手く処理できました。
実際の最初の問題（数値が２以上で5000ほどの通し番号）でコードを利用できるか検討しましたが
Set r = .Cells(1).CurrentRegion
とCurrentRegionを利用しているので途中に改行がある場合は利用できません。
最初の例で言えば、
3 00:00:51,083 --> 00:00:53,333 （改行） その3
最初に記載したように
通し番号の一つ前の行は、必ず改行文字があるのでこれを利用できないでしょうか？
（凝り性） 2023/10/20(金) 17:29:21
↑の「Example for Star Wars: Episode II Attack of the Clones:」のテスト用ファイルを作成し、 以下を実行してみた。
Sub test()
Dim t As Object, aLine As String, linetype As Long, typeold As Long, r As Long, c As Long
Dim aPath As String
aPath = "C:\xxx\test.srt"
With CreateObject("Scripting.FileSystemObject").OpenTextFile(aPath)
typeold = 4
r = 1
Rows.Delete
[A1:C1] = [{"連番","タイプ","内容"}]
Do Until .AtEndOfStream
aLine = .ReadLine
r = r + 1
linetype = SrtDataType(aLine)
If typeold = 4 And linetype = 1 Then c = c + 1
Cells(r, 1) = c
Cells(r, 2) = linetype
Cells(r, 3) = "'" & aLine
typeold = linetype
Loop
.Close
End With
End Sub
Private Function SrtDataType(aLine As String) As Long
Select Case True
Case Len(aLine) = 0
SrtDataType = 4
Case aLine Like "##:##:##,* --> ##:##:##,*"
SrtDataType = 2
Case IsNumeric(aLine)
SrtDataType = 1
Case Else
SrtDataType = 3
End Select
End Function
↓結果
[___]|__A__|__B___|________________C_________________| [ 1]|連番 |タイプ|内容 | [ 2]| 1| 1|1 | [ 3]| 1| 2|00:02:16,612 --> 00:02:19,376 | [ 4]| 1| 3|Senator, we're making | [ 5]| 1| 3|our final approach into Coruscant.| [ 6]| 1| 4| | [ 7]| 2| 1|2 | [ 8]| 2| 2|00:02:19,482 --> 00:02:21,609 | [ 9]| 2| 3|Very good, Lieutenant. | [ 10]| 2| 4| | [ 11]| 3| 1|3 | [ 12]| 3| 2|00:03:13,336 --> 00:03:15,167 | [ 13]| 3| 3|We made it. | [ 14]| 3| 4| | [ 15]| 4| 1|4 | [ 16]| 4| 2|00:03:18,608 --> 00:03:20,371 | [ 17]| 4| 3|I guess I was wrong. | [ 18]| 4| 4| | [ 19]| 5| 1|5 | [ 20]| 5| 2|00:03:20,476 --> 00:03:22,671 | [ 21]| 5| 3|There was no danger at all. |
不要行を削った後から、 B列のタイプが「1」の所で連番を振り直せばいい訳だから、そこから先は何とか手作業でも出来そうな...
（白茶） 2023/10/20(金) 18:11:16
Sub test()
Dim ws As Worksheet
Dim r As Range, a As Range
Worksheets("sheet1").Copy
Set ws = ActiveSheet
Set r = ws.Range("A1", ws.Cells(Rows.Count, 1).End(xlUp)).Offset(, 1)
r.Formula = "=IF(ISNUMBER(A1),"""",FALSE)"
Set r = r.SpecialCells(xlCellTypeFormulas, xlLogical).Offset(, -1)
For Each a In r.Areas
If WorksheetFunction.CountA(a) > 1 Then
a.Copy
a(1).Offset(-1, 1).PasteSpecial Transpose:=True
End If
Next
ws.Columns(2).SpecialCells(xlCellTypeFormulas).EntireRow.Delete
Set r = ws.Cells(1).CurrentRegion.Columns(1)
r.Value = WorksheetFunction.Sequence(r.Rows.Count)
End Sub （マナ） 2023/10/20(金) 18:25:53
コードは、種別毎（連番 |タイプ|内容）に分けられて 上手く処理できています。
＞＞不要行を削った後から、
＞＞ B列のタイプが「1」の所で連番を振り直せばいい訳だから、
＞＞そこから先は何とか手作業でも出来そうな...
残念ながら、
「不必要行」は、連番中でタイプ3が無いのが「不必要行」に相当しますが、
連番単位でこの「不必要行」を全て手動で削除して行くのは無理があります。
つまり、
１）参考コードに連番単位でこの「不必要行」を自動でチェックして
相当する連番を全て削除する処理を追加する必要があります。
又、
２）削除すると歯抜けの連番となるので連番を降りなおすと
同時に内容の部分の一番最初も連番とマッチするように番号の修正が必要となります。
１）、２）共に手動で処理するのは無理があります。
マナさん、コードをいただきありがとうございます。
処理行数が5000を超えるので細部まで比較できていませんが
300行までチェックしてBOOK1に書き出された内容は、思っていた内容に成っているのを確認しました。
予想では、後の行も処理出来ていると思われます。
後は、BOOK１を元の縦長の形式に変換してファイルに書き出だす処理が必要なのですが
これは、自前で何とかしたいと思っています。
（暫くなやんでどうにもならないなら改めて相談させてください。）
（凝り性） 2023/10/20(金) 19:40:51
一応、私が想像してる >>何とか手作業でも出来そうな... の作業イメージです。
▼例えば↓連番６はタイプ「3」が欠落したデータであるが、 [___]|___A____|___B____|________________C_________________| [ 1]|連番 |タイプ |内容 | [ 2]| 1| 1|1 | [ 3]| 1| 2|00:02:16,612 --> 00:02:19,376 | [ 4]| 1| 3|Senator, we're making | [ 5]| 1| 3|our final approach into Coruscant.| [ 6]| 1| 4| | [ 7]| 2| 1|2 | [ 8]| 2| 2|00:02:19,482 --> 00:02:21,609 | [ 9]| 2| 3|Very good, Lieutenant. | [ 10]| 2| 4| | [ 11]| 3| 1|3 | [ 12]| 3| 2|00:03:13,336 --> 00:03:15,167 | [ 13]| 3| 3|We made it. | [ 14]| 3| 4| | [ 15]| 4| 1|4 | [ 16]| 4| 2|00:03:18,608 --> 00:03:20,371 | [ 17]| 4| 3|I guess I was wrong. | [ 18]| 4| 4| | [ 19]| 5| 1|5 | [ 20]| 5| 2|00:03:20,476 --> 00:03:22,671 | [ 21]| 5| 3|There was no danger at all. | [ 22]| 5| 4| | [ 23]| 6| 1|6 | [ 24]| 6| 2|00:00:00,0 --> 00:00:00,0 | [ 25]| 6| 4| | [ 26]| 7| 1|7 | [ 27]| 7| 2|00:00:00,0 --> 00:00:00,0 | [ 28]| 7| 3|↑の6番は不正データである | [ 29]| 7| 4| |
▼これはピボットテーブルで集計してみてもタイプ別の欠損が確認出来る (って事は関数等を使ってもリストアップ出来るであろう) [___]|_________A_________|___B___|___C___|___D___|___E___|__F__| [ 3]|データの個数 / 内容|タイプ | | | | | [ 4]|連番 | 1| 2| 3| 4|総計 | [ 5]| 1| 1| 1| 2| 1| 5| [ 6]| 2| 1| 1| 1| 1| 4| [ 7]| 3| 1| 1| 1| 1| 4| [ 8]| 4| 1| 1| 1| 1| 4| [ 9]| 5| 1| 1| 1| 1| 4| [ 10]| 6| 1| 1| | 1| 3|←あ、歯抜けやん [ 11]| 7| 1| 1| 1| 1| 4| [ 12]|総計 | 7| 7| 7| 7| 28|
▼オートフィルタでもなんでもいいからA列基準で不要行を削除する [___]|___A____|___B____|________________C_________________| [ 1]|連番 |タイプ |内容 | [ 2]| 1| 1|1 | [ 3]| 1| 2|00:02:16,612 --> 00:02:19,376 | [ 4]| 1| 3|Senator, we're making | [ 5]| 1| 3|our final approach into Coruscant.| [ 6]| 1| 4| | [ 7]| 2| 1|2 | [ 8]| 2| 2|00:02:19,482 --> 00:02:21,609 | [ 9]| 2| 3|Very good, Lieutenant. | [ 10]| 2| 4| | [ 11]| 3| 1|3 | [ 12]| 3| 2|00:03:13,336 --> 00:03:15,167 | [ 13]| 3| 3|We made it. | [ 14]| 3| 4| | [ 15]| 4| 1|4 | [ 16]| 4| 2|00:03:18,608 --> 00:03:20,371 | [ 17]| 4| 3|I guess I was wrong. | [ 18]| 4| 4| | [ 19]| 5| 1|5 | [ 20]| 5| 2|00:03:20,476 --> 00:03:22,671 | [ 21]| 5| 3|There was no danger at all. | [ 22]| 5| 4| | [ 23]| 7| 1|7 |←「6」を行ごと消した [ 24]| 7| 2|00:00:00,0 --> 00:00:00,0 | [ 25]| 7| 3|↑の6番は不正データである | [ 26]| 7| 4| |
▼[A2]=COUNTIF(B$2:B2,1)のフィルダウンでA列にアタマから連番を振り直す [___]|___A____|___B____|________________C_________________| [ 1]|連番 |タイプ |内容 | [ 2]| 1| 1|1 | [ 3]| 1| 2|00:02:16,612 --> 00:02:19,376 | [ 4]| 1| 3|Senator, we're making | [ 5]| 1| 3|our final approach into Coruscant.| [ 6]| 1| 4| | [ 7]| 2| 1|2 | [ 8]| 2| 2|00:02:19,482 --> 00:02:21,609 | [ 9]| 2| 3|Very good, Lieutenant. | [ 10]| 2| 4| | [ 11]| 3| 1|3 | [ 12]| 3| 2|00:03:13,336 --> 00:03:15,167 | [ 13]| 3| 3|We made it. | [ 14]| 3| 4| | [ 15]| 4| 1|4 | [ 16]| 4| 2|00:03:18,608 --> 00:03:20,371 | [ 17]| 4| 3|I guess I was wrong. | [ 18]| 4| 4| | [ 19]| 5| 1|5 | [ 20]| 5| 2|00:03:20,476 --> 00:03:22,671 | [ 21]| 5| 3|There was no danger at all. | [ 22]| 5| 4| | [ 23]| 6| 1|7 | [ 24]| 6| 2|00:00:00,0 --> 00:00:00,0 | [ 25]| 6| 3|↑の6番は不正データである | [ 26]| 6| 4| |
▼オートフィルタで「B列が1」を絞り込んで、C列にA列を参照する式を入れる [___]|___A____|___B____|________________C_________________| [ 1]|連番 |タイプ |内容 | [ 2]| 1| 1|1 |=A2&"" とか =TEXT(A2,"0") とか... [ 7]| 2| 1|2 | [ 11]| 3| 1|3 | [ 15]| 4| 1|4 | [ 19]| 5| 1|5 | [ 23]| 6| 1|6 | ▼フィルタ解除したら↓こんな感じ [___]|___A____|___B____|________________C_________________| [ 1]|連番 |タイプ |内容 | [ 2]| 1| 1|1 | [ 3]| 1| 2|00:02:16,612 --> 00:02:19,376 | [ 4]| 1| 3|Senator, we're making | [ 5]| 1| 3|our final approach into Coruscant.| [ 6]| 1| 4| | [ 7]| 2| 1|2 | [ 8]| 2| 2|00:02:19,482 --> 00:02:21,609 | [ 9]| 2| 3|Very good, Lieutenant. | [ 10]| 2| 4| | [ 11]| 3| 1|3 | [ 12]| 3| 2|00:03:13,336 --> 00:03:15,167 | [ 13]| 3| 3|We made it. | [ 14]| 3| 4| | [ 15]| 4| 1|4 | [ 16]| 4| 2|00:03:18,608 --> 00:03:20,371 | [ 17]| 4| 3|I guess I was wrong. | [ 18]| 4| 4| | [ 19]| 5| 1|5 | [ 20]| 5| 2|00:03:20,476 --> 00:03:22,671 | [ 21]| 5| 3|There was no danger at all. | [ 22]| 5| 4| | [ 23]| 6| 1|6 | [ 24]| 6| 2|00:00:00,0 --> 00:00:00,0 | [ 25]| 6| 3|↑の6番は不正データである | [ 26]| 6| 4| |
C列をsrtファイルに書き出して完了。
・・・という作業イメージです。
まあ、
「無理がある」と感じる人は感じるでしょうねぇ。
（白茶） 2023/10/20(金) 20:17:33
ほんのちょっとだけ手を加えてみました。
Sub test()
Dim t As Object, aLine As String, linetype As Long, typeold As Long, r As Long, c As Long
Dim f As Long, a As Long
Dim aPath As String
aPath = "C:\xxx\test.srt"
With CreateObject("Scripting.FileSystemObject").OpenTextFile(aPath)
typeold = 4
r = 1
Rows.Delete
[A1:D1] = [{"連番","チェック","タイプ","内容"}]
Do Until .AtEndOfStream
aLine = .ReadLine
r = r + 1
linetype = SrtDataType(aLine)
If typeold = 4 And linetype = 1 Then
c = c + 1
a = r
f = 0
End If
f = f Or (2 ^ (linetype - 1))
Cells(r, 1) = c
Cells(r, 3) = linetype
Cells(r, 4) = "'" & aLine
If linetype = 4 Then Range(Cells(a, 2), Cells(r, 2)) = f
typeold = linetype
Loop
.Close
End With
End Sub
B列が「15」じゃなかったら、その連番はデータの欠損があるという事です。
[___]|__A__|___B____|__C___|________________D_________________| [ 1]|連番 |チェック|タイプ|内容 | [ 2]| 1| 15| 1|1 | [ 3]| 1| 15| 2|00:02:16,612 --> 00:02:19,376 | [ 4]| 1| 15| 3|Senator, we're making | [ 5]| 1| 15| 3|our final approach into Coruscant.| [ 6]| 1| 15| 4| | [ 7]| 2| 15| 1|2 | [ 8]| 2| 15| 2|00:02:19,482 --> 00:02:21,609 | [ 9]| 2| 15| 3|Very good, Lieutenant. | [ 10]| 2| 15| 4| | [ 11]| 3| 15| 1|3 | [ 12]| 3| 15| 2|00:03:13,336 --> 00:03:15,167 | [ 13]| 3| 15| 3|We made it. | [ 14]| 3| 15| 4| | [ 15]| 4| 15| 1|4 | [ 16]| 4| 15| 2|00:03:18,608 --> 00:03:20,371 | [ 17]| 4| 15| 3|I guess I was wrong. | [ 18]| 4| 15| 4| | [ 19]| 5| 15| 1|5 | [ 20]| 5| 15| 2|00:03:20,476 --> 00:03:22,671 | [ 21]| 5| 15| 3|There was no danger at all. | [ 22]| 5| 15| 4| | [ 23]| 6| 11| 1|6 |← [ 24]| 6| 11| 2|00:00:00,0 --> 00:00:00,0 |← [ 25]| 6| 11| 4| |← [ 26]| 7| 15| 1|7 | [ 27]| 7| 15| 2|00:00:00,0 --> 00:00:00,0 | [ 28]| 7| 15| 3|↑の6番は不正データである | [ 29]| 7| 15| 4| |
（白茶） 2023/10/20(金) 20:42:43
Sub test()
Dim ws As Worksheet
Dim r As Range, a As Range
Worksheets("sheet1").Copy
Set ws = ActiveSheet
ws.Rows(1).Insert
Set r = ws.Range("A2", ws.Cells(Rows.Count, 1).End(xlUp)).Offset(, 1)
r.Formula = "=IF(ISNUMBER(A2),"""",FALSE)"
r.Offset(, 1).Formula = "=IF(ISNUMBER(A2),MAX($A$1:A1)+1,A2&"""")"
Set r = r.SpecialCells(xlCellTypeFormulas, xlLogical).Offset(, -1)
For Each a In r.Areas
If WorksheetFunction.CountA(a) < 2 Then
a.Offset(-1).Resize(a.Count + 1).EntireRow.Delete
End If
Next
ws.Columns("C").Value = ws.Columns("C").Value
ws.Rows(1).Delete
ws.Columns("A:B").Delete
End Sub （マナ） 2023/10/20(金) 20:45:38
2023/10/20_18：25：53でいただいたコードで作成された
横型形式を縦長形式に変えるコードを今朝より始めましたが
完成する前に「エクセルの学校」を見ると
昨晩（2023_10/20_20:45:38)に既に縦長の形式でBookに書き出すコードが公表されていました。
シート＞スリム化横型＞縦型ではなく、シート＞スリム化縦型なので
こちらをそのまま利用させていただく事にしました。
コードで上手くスリム化できたシートをテキスト形式で保存すれば完成ですが
そこで思考停止しました。
Bookが保存されていて、Bookからエクセルを起動しているなら
下記のようなコードをBookに記載してテキスト形式で保存できるのですが
Bookはまだ保存前のファイルなのでこの手法は利用できません。
どう処理したら良いのかアドバイスをお願いします。
Sub テキストファイルで保存()
Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets(1)
Dim SaveFile As String Dim savePath as String
SavePath="C:\Users\MUKU\Desktop\"
SaveFile = SavePath & "Slim.txt"
Open SaveFile For Output As #1
Dim i As Long
i = 1
Do While ws.Cells(i, "A").Value <> ""
Print #1, ws.Cells(i, 1).Value
i = i + 1
Loop
Close #1
MsgBox SavePath &"Slim.txtに保存しました。"
End Sub
/////////////////////////////////////////////////////
白茶さん、考え方のアドバイスありがとうございます。
先にマナさんからいただいたコードを完済させた後で
白茶さんの提案を検討したいと思います。
時間をください。
（凝り性） 2023/10/21(土) 08:56:28
Set ws = ThisWorkbook.Worksheets(1)
を
Set wsb = Workbooks("Book1").Worksheets(1)
にすれば良いのに気が付きました。
（凝り性） 2023/10/21(土) 11:14:35
よく見たら時間部分がないこともあるのですか？ それだと期待通りにならないはずです。
> 5 >00:01:07,000 --> 00:01:08,16 > 6 >その6 （マナ） 2023/10/21(土) 11:24:22
サンプルで上げた書き込みが間違っていました。
時間部分がないことはありません。
（凝り性） 2023/10/21(土) 11:35:11
今頃な確認ですみません。
(1)No.3のデータにあるように、字幕部分の最初の行が空白ということは普通にあるんですか？
それは何を意味しているんですか？また2行以上の空白ということはないのですか？
(2)「改行文字」についての説明がよくわかりません。
No.3のデータの例で言えば、データとして、「改行」は 3 改行 00:00:51,083 --> 00:00:53,333改行 改行 その3改行 改行 となっているはずです。あなたの言う「改行」とは、そういう理解でいいんですよね。
(3)改行文字を別途出力したいようですが、それは何故必要なんですか？
データごとに異なる改行文字を使うことがあるんですか？良くわからない。
(4)出力するテキストデータは、どうなればいいんですか？ ・読み込むテキストファイルのデータ例（の正しいもの） ・出力するテキストファイルのデータ例（上記に対応したもの） を改めて示して頂けないですか？
（xyz） 2023/10/21(土) 12:28:01
（1）字幕部分の最初の行が空白ということは普通にあるんですか？
普通は、ありません。
字幕で装飾をしている場合など、私的に必要ないと思われる
こりすぎと思われる部分は事前に削除するようにしています。
また2行以上の空白ということはないのですか？
これもまず無いと思います。
(2)あなたの言う「改行」とは、そういう理解でいいんですよね。
そうご理解ください。
(3)改行文字を別途出力したいようですが、
改行文字だけを全角スペースに変換して出力するのは以下のような場合に限ると思います。
字幕
改行文字 -----＞ 全角スペース
字幕
ただし、改行でも実際字幕ファイルに利用する場合は、問題ないようです。
(4)出力するテキストデータは、どうなればいいんですか？
最初の読み込むテキストファイルの形式と同じ形式でスリム化した変換データも出力です。
（途中の単純化した例での横型での出力は、最終形態に変換する前の形態です。
一度、私的には横型にしたほうが考えやすいと思いましたが
マナさんが、シート＞スリム化縦型とスリム化横型が介在しないコードを作ってくれたので
そのまま利用させていただきました。）
（凝り性） 2023/10/21(土) 13:21:44
>改行文字だけを全角スペースに変換して出力
わたしのコードでは、改行はそのままなので これ↓でループを抜けると、駄目じゃないですか。
> Do While w.Cells(i, "A").Value <> "" （マナ） 2023/10/21(土) 14:54:57
Dim wsb As Worksheet
Set wsb = Workbooks("Book1").Worksheets("Sheet1")
Dim Sfilename
Sfilename = "C:\Users\MUKU\Desktop\" & FileName2 & "_Slim.txt"
Dim myADO As Object
Set myADO = CreateObject("ADODB.Stream")
Dim i As Long
Dim bufLine As String
With myADO
.Type = 2 'adTypeText
.Charset = "UTF-8"
.Open
i = 1
For i = 1 To wsb.Cells(Rows.Count, 1).End(xlUp).Row
bufLine = wsb.Cells(i, "A")
.WriteText bufLine, 1
Next i
.SaveToFile Sfilename, 2
.Close
End With
MsgBox "スリム化されたテキストファイルを保存しました。" & vbCrLf & vbCrLf & _
"C:\Users\TAC_\Desktop\" & FileName2 & "_Slim.txt"
（凝り性） 2023/10/21(土) 16:09:20
回答に感謝します。未だ理解できなところがありますが、解決済みのようなので退出します。 （xyz） 2023/10/21(土) 17:47:58
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.