How to export file names into an excel file?

Question

How to export file names into an excel file?

Amin2234 146

Hi,

I want to export 1000 file names into a excel doc with 4 columns by the following instruction:
File-1.mp3 =>> column 1
File-2.mp3 =>> column 2
File-3.mp3 =>> column 3
File-4.mp3 =>> column 4
File-5.mp3 =>> column 1
File-6.mp3 =>> column 2
File-7.mp3 =>> column 3
File-8.mp3 =>> column 4
.
.
.
File-997.mp3 =>> column 1
File-998.mp3 =>> column 2
File-999.mp3 =>> column 3
File-1000.mp3 =>> column 4

All files ended with number at the end…sorted by numbers from 1 to 1000 (exactly like above)

Answer 1

Lz._ 8,816

@Amin2234

I wasn't expecting a big RAR file but the .txt file only
Due to the structure (SublistNumber-FileNumber) of your file names I revised the query => Output:

Updated workbook available here

Lz._ 8,816 Reputation points

2022-01-28T10:49:56.82+00:00

@Amin2234

Take your time to check the query does what you want....
In the meantime I found another approach that doesn't require to do a DIR in a command prompt + I improved the perf. of the queries
Amin2234 146 Reputation points

2022-01-28T16:05:27.537+00:00

Thank your very much @Lz._

Could you please tell me how did you apply these filters? step-by-step (sorry for asking I am not pro in Excel)...Because I have another files should be exported and sorted.

Also what is your other method which you just found ("

I found another approach that doesn't require to do a DIR in a command prompt + I improved the perf

")?

Another and IMPORTANT question:
Is there any custom and reliable methods that work with the following ways?

I have different shape of mp3 files, sch as:
(2 columns):
Amongst Friends-1.mp3 ==> column 1
Amongst Friends-2.mp3 ==> column 2
Amongst Friends-3.mp3 ==> column 1
Amongst Friends-4.mp3 ==> column 2
.
.
.
Amongst Friends-499.mp3 ==> column 1
Amongst Friends-500.mp3 ==> column 2

============================

(3 columns):
300 most important French adjectives 1 - 25-1.mp3 ==> column 1
300 most important French adjectives 1 - 25-2.mp3 ==> column 2
300 most important French adjectives 1 - 25-3.mp3 ==> column 3
300 most important French adjectives 1 - 25-4.mp3 ==> column 1
300 most important French adjectives 1 - 25-5.mp3 ==> column 2
300 most important French adjectives 1 - 25-6.mp3 ==> column 3
.
.
.
300 most important French adjectives 1 - 25-298.mp3 ==> column 1
300 most important French adjectives 1 - 25-299.mp3 ==> column 2
300 most important French adjectives 1 - 25-300.mp3 ==> column 3

============================
Lz._ 8,816 Reputation points

2022-01-31T13:55:46.727+00:00

Hi @Amin2234

Could you please tell me how did you apply these filters? step-by-step
I'm afraid to say that this isn't realistic. Power Query as any other techno. isn't something you learn nor teach in a few minutes

I have taken your 3 different patterns (Sublist, Amongst Friends, 300 most important French adjectives) and built a new query that should do what you want assuming files are in different folders. There's no more need to generate a .txt file with a DIR from the command prompt, the query picks the .mp3 files that exist in the Folder path:

There's another parameter (Number of output columns) for you to use...
New query is avaible here for download

Answer 2

Lz._ 8,816

Hi @Amin2234

With Get & Transform aka Power Query

1/ Download that Excel file

2/ In a command prompt (CMD):

Switch the dir. where your .mp3 are stored
DIR *.mp3 /B > FileNames.txt

2/ In Excel:

Open the downloaded file
Update cell F2 with the path + file name created at #2
Right-click on the green table > Refresh

Amin2234 146 Reputation points

2022-01-27T20:12:56.84+00:00

Thanks @Lz._

But the result is not what I wanted and showed in your screenshot.

There are some unwanted data which I could solved by this command: Dir *.MP3 /B >files.txt

But the main problem is the file names are not respectively.

This is my file names:

Answer 3

Lz._ 8,816

@Amin2234

As you experienced it's not DIR *.mp3 > FileNames.txt but DIR *.mp3 /B > FileNames.txt - my bad :( + sorry about that
That said, I renamed my dummy files and without changing anything in the query I get the expected result:

I currently have no idea why this doesn't work as expected on your side. Could you:

Upload & share (i.e with OneDrive, GoogleDrive...) your files.txt after a DIR *.mp3 /B > FileNames.txt?
Confirm your Excel (2016, 2019.... 365) release + Version & Build (Go to File > Account)

Amin2234 146 Reputation points

2022-01-28T07:04:35.377+00:00

@Lz._

Here is the link to download.
It contains the exported txt (files.txt) file and also all of my mp3 files.

My office ver is Microsoft® Excel® 2019 MSO (Version 2112 Build 16.0.14729.20224) 32-bit
Amin2234 146 Reputation points

2022-02-07T12:58:22.6+00:00

@Lz._

I used your last file and the result is a little different. The numbers are not sorted:

Could you please help me out? Please tell me how can I sort so I can apply it to other files too.
Also take a look at row 2 (where I indicated with ?). Why is that?

Here is file to download
Lz._ 8,816 Reputation points

2022-02-07T14:27:37.83+00:00
@Amin2234

The sorting is OK in the Power Query editor but not on the worksheet it's working. I can't explain why but here is how I fixed the issue:

Click somewhere in the query ouput table

Go to Data tab > Load To > choose Only Create Connection > OK > OK

Select cell B5

Go to Data tab > Queries & Connections > Right-click on query FileNamesInColumns > Load To... > choose Table > OK

Good on my side:

Answer 4

Amin2234 146

@Lz._

Could you please check this file too and let me know sort it? Must be sorted according to the alphabet and number.

Answer 5

Lz._ 8,816

@Amin2234

This looks like a never ending story... My last update on this case in this file

Amin2234 146 Reputation points

2022-02-13T07:17:30.827+00:00

@Lz._

Sorry to bother you.
But is there any way that could works with all patterns?

How to export file names into an excel file?

4 additional answers

Activity