Batch File to List File Names into 2 column TXT file

[JustLearning]

Hi,
I am relatively new to batch files and I hope someone can help me please.
I have a folder which contains 10,000 client files. Each client can have 3 or 4 files. The naming convention for the files is as follows
document_1_client_dd123ccc.doc
document_2_client_dd123ccc.doc
document_3_client_dd123ccc.doc
document_1_client_gg333ddd.doc
document_2_client_gg333ddd.doc      etc. etc

I need to attach information regarding these files to a database. At present I run a simple batch file which collects the names of the files into a txt file in the folder. The batch file is as follows,

cd\
cd\MyDir\FolderName
DIR /B "%*"*>C:\"MyDir\FolderName\FileNameList.txt"

The FileNameList.txt file contains all the names of the files as follows.
document_1_client_dd123ccc.doc
document_2_client_dd123ccc.doc
document_3_client_dd123ccc.doc
document_1_client_gg333ddd.doc
document_2_client_gg333ddd.doc     

I then link the table in the database to the FileNameList.txt file and it works fine.

However to improve this and this is where I need help, I want to extract the client identification part of the file names into a second (comma separated) column in the FileNameList.txt file, which should look like this when it finishes,

document_1_client_dd123ccc.doc, dd123ccc
document_2_client_dd123ccc.doc, dd123ccc
document_3_client_dd123ccc.doc, dd123ccc
document_1_client_gg333ddd.doc, gg333ddd
document_2_client_gg333ddd.doc, gg333ddd

Is this possible? I would really appreciate any help anyone can give me.

Many thanks in advance.

[Geek-9pm]

Is this batch file of a database?
If a database, a spreadsheet would be more suitable.

[JustLearning]

Hi Geek-9pm,
Thanks for taking the time to respond to me.
No it is not from a database. It is simply a list of all files in a folder external to a database. However I do use it to link into the database. The database has a linked table in it which links to the external txt file.   

[Squashman]

And you are absolutely positive that all the files you are putting into your File List conform to this file name format?
document_1_client_dd123ccc.doc

[JustLearning]

Hi Squashman,

Thank you for responding

Yes, each Client will have a maximum of 4 files associated with their client record. Therefore the document identifier ( document_1 ) part of the file name will only ever go as high as "document_4" and the client identifier ( client_dd123ccc ) part of the file name will always remain constant for the individual client. The document extension ".doc" likewiase will also remain constant.

[Squashman]

Code: [Select]

for /F "tokens=1-4 delims=_" %%G in ('dir /a-d /b *.doc') do (
     >>filelist.txt echo %%G_%%H_%%I_%%J,%%~nJ
)

[JustLearning]

Hi Squashman
That worked perfectly. Thank you so much. Really appreciated.

[paultomasi]

Code: [Select]

@echo off
(
  for /f "tokens=2,4 delims=_" %%a in ('dir /a-d /b *.doc') do echo document_%%a_client_%%b.doc, %%b
)>filelist.txt


[Squashman]

Code: [Select]

@echo off
(
  for /f "tokens=2,4 delims=_" %%a in ('dir /a-d /b *.doc') do echo document_%%a_client_%%b.doc, %%b
)>filelist.txt


I would rather not assume that the words document and client are hard coded into the file names.