I have folders of audio samples, and I'm about to delete what I don't use, but I'd like to have a record of the original contents in an SQLite database.
Currently, I'm writing directly to the database, but I think writing to a .CSV
file and importing to a database would be faster because there are hundreds of files.
Use this batch code to create a directory tree like my file system + dummy files, for testing. It will create within wherever you execute the file. (In my case, the top folder was named Testing
:
SET "BigFish=Z. Unsorted\Big Fish Audio\Midnight Jazz Train\Brass and Winds"
SET "Fragments=Z. Unsorted\Loopmasters\Fragments 02\FR2_SOUNDS_&_FX\FR2_BASS"
SET "LCycles=Z. Unsorted\Loopmasters\Hip-Hop Lunar Cycles\LUN_SYNTH_MELODY_LOOPS"
FOR %%A IN (Tenor_Sax, Trumpet) DO MD "%BigFish%\%%A"
MD "%Fragments%"
MD "%LCycles%"
@echo justtestjunk > "%Fragments%\loop.wav"
@echo justtestjunk > "%LCycles%\drum.aiff"
@echo justtestjunk > "%LCycles%\file.txt"
FOR %%A IN (Tenor_Sax, Trumpet) DO echo justtestjunk > "%BigFish%\%%A\data.sql"
With help, I've managed to create a database and make some entries, but my output looks as follows (just a sample of the first row):
loop.wav, Testing, Z. Unsorted, Loopmasters, Fragments 02, 15
When what I want is this:
loop.wav, Loopmasters, Fragments 02, FR2_SOUNDS_&FX, FR2_BASS, 15
I think the problem is with the delimiting in the code.
Also, I need the code to handle the absence of a Type
value (leaving it NULL
)
Code:
@echo off
setlocal enabledelayedexpansion
SET fld="Z. Unsorted"
sqlite3 %fld%\TestDb.sqlite "CREATE TABLE IF NOT EXISTS \"Samples\" (File TEXT NOT NULL, Publisher TEXT, Pack TEXT NOT NULL, Category TEXT, Type TEXT, Size INT);"
for /r %fld% %%A in (*.aiff *.wav) do (
call :part "%%~dpA"
set "File=%%~nxA"
set "File=!File:'=''!"
sqlite3 %fld%\TestDb.sqlite "INSERT OR REPLACE INTO \"Samples\" VALUES ('!File!', '!Publisher!', '!Pack!', '!Category!', '!Type!', '%%~zA');"
@echo '!File!', '!Publisher!', '!Pack!', '!Category!', '%%~zA'
)
PAUSE
:part
for /f "tokens=6-10 delims=\" %%A in ("%~1") do (
set "Publisher=%%~A"
set "Publisher=!Publisher:'=''!"
set "Pack=%%~B"
set "Pack=!Pack:'=''!"
set "Category=%%~C"
set "Category=!Category:'=''!"
set "Type=%%~D"
set "Type=!Type:'=''!"
)