move columns to rows w semicolon separator

bry can

Hi,
i have thousands of lines like this which show various number of files under them.
How can i re format to where ‘directory…’ is one column, date, time, size, filename are separate columns
separated by semicolon

How old data is:
Directory of e:\banana\AFFACT\AA Director

06/19/2007 06:26 AM 239,616 AA Sexual Harassment Survey.mdb
04/09/2008 10:13 AM 299,008 AASexualHarassmentPrevSurvey.mdb
2 File(s) 538,624 bytes

Directory of e:\banana\AFFACT\AA PLANS\AA PLAN 1999-2000

09/26/2005 10:26 AM 21,460,992 bkruk.mdb
1 File(s) 21,460,992 bytes

Directory of e:\banana\AFFACT\AA PLANS\AA PLAN 2000-2001

02/09/2006 11:36 AM 21,504,000 bkruk.mdb
1 File(s) 21,504,000 bytes

Directory of e:\apple\AFFACT\AA PLANS\AA PLAN 2001-2002\AA PLAN 2001 - 2002

12/16/2002 09:41 AM 21,499,904 bkruk.mdb
1 File(s) 21,499,904 bytes

Directory of e:\orange\AFFACT\AA PLANS\AA PLAN 2002-2003\AA PLAN 2002 - 2003

09/26/2005 10:42 AM 21,549,056 bkruk.mdb
1 File(s) 21,549,056 bytes

Want to make data look like: (word wrapping may hinder, please see image):
Directory of e:\apples\AFFACT\AA Director;06/19/2007; 06:26 AM; 239,616; AA Sexual Harassment Survey.mdb
;04/09/2008; 10:13 AM; 299,008; AASexualHarassmentPrevSurvey.mdb;2 File(s) 538,624 bytes
Directory of e:\apples\AFFACT\AA PLANS\AA PLAN 1999-2000;09/26/2005; 10:26 AM;21,460,992; bkruk.mdb;1 File(s) 21,460,992 bytes
Directory of e:\orange\AFFACT\AA PLANS\AA PLAN 2000-2001;02/09/2006;11:36 AM;21,504,000; bkruk.mdb;1 File(s) 21,504,000 bytes
Directory of e:\apples\AFFACT\AA PLANS\AA PLAN 2001-2002\AA PLAN 2001 - 2002; 12/16/2002; 09:41 AM; 21,499,904; bkruk.mdb;1 File(s) 21,499,904 bytes

Directory of e:\banana\AFFACT\AA PLANS\AA Plan 2021 - 2022\New HRAA Access database update\January 2022;02/23/2022;12:43 PM; 9,846,784; COPYHforce20220131.accdb;4 File(s) 9,846,784 bytes
;02/23/2022;12:43 PM; 9,846,784; 2COPYHforce20220131.accdb
;02/24/2022;12:43 PM; 9,846,784; 3COPYHforce20220131.accdb
;02/25/2022;12:43 PM; 9,846,784; 4COPYHforce20220131.accdb

notepad++-excelView.png

Thanks for any assistance.

Alan Kilborn

@bry-can

My eyes literally hurt trying to look at that mess.
Let me see if I can do better:

Before:

Directory of e:\banana\AFFACT\AA Director

06/19/2007 06:26 AM 239,616 AA Sexual Harassment Survey.mdb
04/09/2008 10:13 AM 299,008 AASexualHarassmentPrevSurvey.mdb
2 File(s) 538,624 bytes

Directory of e:\banana\AFFACT\AA PLANS\AA PLAN 1999-2000

09/26/2005 10:26 AM 21,460,992 bkruk.mdb
1 File(s) 21,460,992 bytes

Directory of e:\banana\AFFACT\AA PLANS\AA PLAN 2000-2001

02/09/2006 11:36 AM 21,504,000 bkruk.mdb
1 File(s) 21,504,000 bytes

Directory of e:\apple\AFFACT\AA PLANS\AA PLAN 2001-2002\AA PLAN 2001 - 2002

12/16/2002 09:41 AM 21,499,904 bkruk.mdb
1 File(s) 21,499,904 bytes

Directory of e:\orange\AFFACT\AA PLANS\AA PLAN 2002-2003\AA PLAN 2002 - 2003

09/26/2005 10:42 AM 21,549,056 bkruk.mdb
1 File(s) 21,549,056 bytes

After:

Directory of e:\apples\AFFACT\AA Director;06/19/2007; 06:26 AM; 239,616; AA Sexual Harassment Survey.mdb
;04/09/2008; 10:13 AM; 299,008; AASexualHarassmentPrevSurvey.mdb;2 File(s) 538,624 bytes
Directory of e:\apples\AFFACT\AA PLANS\AA PLAN 1999-2000;09/26/2005; 10:26 AM;21,460,992; bkruk.mdb;1 File(s) 21,460,992 bytes
Directory of e:\orange\AFFACT\AA PLANS\AA PLAN 2000-2001;02/09/2006;11:36 AM;21,504,000; bkruk.mdb;1 File(s) 21,504,000 bytes
Directory of e:\apples\AFFACT\AA PLANS\AA PLAN 2001-2002\AA PLAN 2001 - 2002; 12/16/2002; 09:41 AM; 21,499,904; bkruk.mdb;1 File(s) 21,499,904 bytes

Directory of e:\banana\AFFACT\AA PLANS\AA Plan 2021 - 2022\New HRAA Access database update\January 2022;02/23/2022;12:43 PM; 9,846,784; COPYHforce20220131.accdb;4 File(s) 9,846,784 bytes
;02/23/2022;12:43 PM; 9,846,784; 2COPYHforce20220131.accdb
;02/24/2022;12:43 PM; 9,846,784; 3COPYHforce20220131.accdb
;02/25/2022;12:43 PM; 9,846,784; 4COPYHforce20220131.accdb

Nah, that isn’t much better, because I think the data is fubar’d.
Which just makes it less inconvenient for those that want to help to actually do that.
Sure, someone will (probably) come along, put the time in to straighten out your data (your problem statement), and then will solve your problem.

PeterJones

@bry-can

It looks like you probably have the dir output from cmd.exe environment. If you had read the “Please Read Before Posting” and the Search/Replace FAQ it references, it would have been easier for us to be sure, rather than guessing. Also note: including data in your AFTER that wasn’t in your BEFORE is really confusing. And showing a screenshot from a spreadsheet program when you are asking for help with Notepad++ isn’t very helpful, either.

Given it’s your first post, and you at least tried to provide BEFORE and AFTER data, I will take pity on you. But in the future, we will expect you to put in more effort to format your posts to be more readable. The FAQs linked at the bottom of my post will help you.

Normally, I would tackle something like this is in my favorite programming language (and I actually wouldn’t parse the dir output; I’d navigate the directories from inside the programming language).

However, if you insist on doing it in Notepad++ with Regular Expressions, it’s possible, especially if you are willing to do a multi-step process (all of which assume Search Mode is Regular Expression mode):

Remove all leading spaces:
FIND = ^\h*
REPLACE = (ensure the box the box empty)
Remove the blank line between “Directory” and the actual listing
FIND = (?-s)^(Directory.*\R)\R
REPLACE = $1
format the lines starting with dates
FIND = (?-s)^(\d\d/\d\d/\d\d\d\d)\h*(\d\d:\d\d [AP]M)\h*(\d[\d,]*)\h*(.*)$
REPLACE = ;$1;$2;$3;$4
move the summary line to the end of each block’s first ;-starting line. This one uses some pretty fancy tricks
FIND = (?-s)^Directory.*\R;.*\K(\R(?:^;.*\R)*)(\d+ File.s.)\h+(.*)\R*
REPLACE = ;$2;$3$1
combine the “Directory” line with the first ;-line below:
FIND = (?-s)^(Directory.*)\R
REPLACE = $1;

AFTER

Directory of e:\banana\AFFACT\AA Director;;06/19/2007;06:26 AM;239,616;AA Sexual Harassment Survey.mdb;2 File(s);538,624 bytes
;04/09/2008;10:13 AM;299,008;AASexualHarassmentPrevSurvey.mdb
Directory of e:\banana\AFFACT\AA PLANS\AA PLAN 1999-2000;;09/26/2005;10:26 AM;21,460,992;bkruk.mdb;1 File(s);21,460,992 bytes
Directory of e:\banana\AFFACT\AA PLANS\AA PLAN 2000-2001;;02/09/2006;11:36 AM;21,504,000;bkruk.mdb;1 File(s);21,504,000 bytes
Directory of e:\apple\AFFACT\AA PLANS\AA PLAN 2001-2002\AA PLAN 2001 - 2002;;12/16/2002;09:41 AM;21,499,904;bkruk.mdb;1 File(s);21,499,904 bytes
Directory of e:\orange\AFFACT\AA PLANS\AA PLAN 2002-2003\AA PLAN 2002 - 2003;;09/26/2005;10:42 AM;21,549,056;bkruk.mdb;1 File(s);21,549,056 bytes

You can record that sequence as a macro and save it with a name and keyboard shortcut if you’re going to be doing this a lot. (If you do, please note that my sequence assumes you don’t have any <DIR> directory entries, and that it doesn’t show the “# Dir(s)” line after the “# File(s)” line, both of which are weird assumptions given the default output of dir, but match your shown data)

----

move columns to rows w semicolon separator

Useful References