Solved Extracting Data From File Name

July 12, 2013 at 00:40:37
Specs: Windows Vista, 1.6 gb 4gb
I would be grateful for any help on a problem I have. This entails extracting data from files names and inserting it into an Excel file. Not wishing to reinvent the wheel I have searched, but without success, for how this could be achieved.

Basically, I have a directory full of MP3 files. What I need is a script which will go through each of the files in the directory and extract the 3 parts of the file name together with the extension and save all these to a comma delimited csv file. The next bit I can do as it is straightforward as it involves just importing this csv file into Excel.

Each file name has the same format ie it has 3 sections separated by a hyphen and a tilde (alway in the same order) eg:
Blue Murder - The Screaming Habdabs ~ 1975 Lossiemouth Live In The Hanger.mp3

I need to save the data from each file in a csv file as:
Blue Murder,The Screaming Habdads,1975 Lossiemouth Live In The Hanger, MP3

I suspect that this will require Regex to achieve and that is probably beyond me!

Thank you for any help you can offer


See More: Extracting Data From File Name

Report •


#1
July 12, 2013 at 02:34:02
✔ Best Answer
::====== script starts here ===============
:: change - ~ . to commas for CSV
:: juniper.bat 2013-07-12 16:03:40.42
@echo off > NEWFILE & setLocal enableDELAYedeXpansioN

:main
for /f "tokens=1-4 delims=-~." %%a in ('dir/b *.mp3') do (
echo.%%a,%%b,%%c,%%d
)>> NEWFILE
move/y NEWFILE MY.CSV
goto :eof
::====== script ends here =================


ฺBTW, it's refreshing to read clear writing.

=====================
M2 Golden-Triangle


Report •

#2
July 12, 2013 at 07:22:28
Hi, Mechanic2Go,

Thank you for your prompt response - as in the past, you've helped me again via the power of your batch files! I am always amazed what a few lines of code in a batch file can do! I thought I would have to work it all out using Right, Left,Mid and Instr functions or even the dreaded Regex!

From your coding I can now work out what to do if I should require additional delimited fields and various extensions such as avi, Mp4, mpg etc. As the last delimited field before the file extension (the one following the tilde) will be imported into a "Comments" column in Excel, it occurred to me that if I could extract only that field it could be quite helpful. My attempts to modify your batch file to achieve this have ended in dismal failure! The batch file keeps reading from left to right so I get everything except that field. What is the secret?? It's not essential but it would be a bonus if I could also treat that extra field separately

Thank you ever so much for what you have given me so far - it fits the bill excellently


Report •

#3
July 12, 2013 at 10:07:35
::====== script starts here ===============
:: change - ~ . to commas for CSV
:: juniper2.bat 2013-07-12 16:03:40.42
@echo off > NEWFILE & setLocal enableDELAYedeXpansioN

:main
for /f "tokens=1-4 delims=-~." %%a in ('dir/b *.mp3') do (
echo.%%c
)>> NEWFILE
move/y NEWFILE aftertil.CSV
goto :eof
::====== script ends here =================

=====================
M2 Golden-Triangle


Report •

Related Solutions

#4
July 14, 2013 at 23:58:10
Hi Mechanix2Go,

Brilliant - that worked a treat! Although I was originally looking for a VBScript to do all this for me your batch file is much better. It is easier to modify the batch file to get what I want rather than make the necessary changes to a VBScript. (Assuming I know what I'm doing!)

Many thanks once again!


Report •


Ask Question