Skip to content

Instantly share code, notes, and snippets.

@aborruso

aborruso/csvBuild.md

Last active February 1, 2019 13:39
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save aborruso/4e8ffc1c606f54504a71277a2d2daa32 to your computer and use it in GitHub Desktop.
Save aborruso/4e8ffc1c606f54504a71277a2d2daa32 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
csvBuild.md

source file (input.txt)

TITLE:   Untitled Sequence.01
000001  GUIDE_VO                         A     C        12:36:54:21 12:37:06:19 01:00:00:00 01:00:11:23
*FROM CLIP NAME:  SFX CLIP 1
*SOURCE FILE: GUIDE VO
000002  HORROR.COPY.01.MP3               A     C        01:00:00:00 01:00:22:10 01:00:11:23 01:00:34:08
*FROM CLIP NAME:  SFX CLIP 2
*SOURCE FILE: HORROR.COPY.01.MP3
000003  93_HORROR_DRONE.MP3              A     C        01:00:04:15 01:00:08:08 01:00:34:08 01:00:38:01
*FROM CLIP NAME:  SFX CLIP 3
*SOURCE FILE: 93 HORROR DRONE.MP3

extract the clipname file

grep -oE 'SFX CLIP [0-9]{1,}' input.txt | sed 's/ /_/g' >./clipname.txt

The output is

SFX_CLIP_1
SFX_CLIP_2
SFX_CLIP_3

extract the details file

grep -E '^00' input.txt >./details.txt

The output is

000001  GUIDE_VO                         A     C        12:36:54:21 12:37:06:19 01:00:00:00 01:00:11:23
000002  HORROR.COPY.01.MP3               A     C        01:00:00:00 01:00:22:10 01:00:11:23 01:00:34:08
000003  93_HORROR_DRONE.MP3              A     C        01:00:04:15 01:00:08:08 01:00:34:08 01:00:38:01

merge the two files

paste -d " " clipname.txt details.txt >out.txt

The output is

SFX_CLIP_1 000001  GUIDE_VO                         A     C        12:36:54:21 12:37:06:19 01:00:00:00 01:00:11:23
SFX_CLIP_2 000002  HORROR.COPY.01.MP3               A     C        01:00:00:00 01:00:22:10 01:00:11:23 01:00:34:08
SFX_CLIP_3 000003  93_HORROR_DRONE.MP3              A     C        01:00:04:15 01:00:08:08 01:00:34:08 01:00:38:01

create the CSV

mlr --inidx --ifs ' ' --ocsv --repifs cat out.txt >final.txt

The output is

1,2,3,4,5,6,7,8,9
SFX_CLIP_1,000001,GUIDE_VO,A,C,12:36:54:21,12:37:06:19,01:00:00:00,01:00:11:23
SFX_CLIP_2,000002,HORROR.COPY.01.MP3,A,C,01:00:00:00,01:00:22:10,01:00:11:23,01:00:34:08
SFX_CLIP_3,000003,93_HORROR_DRONE.MP3,A,C,01:00:04:15,01:00:08:08,01:00:34:08,01:00:38:01

You could rename the header with

mlr --csv label clip,id,name3,name4,name5,name6,name7,name8,name9 final.txt

clip,id,name3,name4,name5,name6,name7,name8,name9
SFX_CLIP_1,000001,GUIDE_VO,A,C,12:36:54:21,12:37:06:19,01:00:00:00,01:00:11:23
SFX_CLIP_2,000002,HORROR.COPY.01.MP3,A,C,01:00:00:00,01:00:22:10,01:00:11:23,01:00:34:08
SFX_CLIP_3,000003,93_HORROR_DRONE.MP3,A,C,01:00:04:15,01:00:08:08,01:00:34:08,01:00:38:01
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment