How to export file names into an excel file?

Amin2234 171

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)

Accepted answer

Lz._ 9,006 Reputation points

2022-01-28T08:36:30.393+00:00

@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
Please sign in to rate this answer.
Lz._ 9,006 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 171 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._ 9,006 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
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

4 additional answers

Lz._ 9,006 Reputation points

2022-01-27T18:13:32.26+00:00
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
Please sign in to rate this answer.
Amin2234 171 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:
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Lz._ 9,006 Reputation points

2022-01-28T04:46:58.823+00:00
@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)
Please sign in to rate this answer.
Amin2234 171 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 171 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._ 9,006 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:
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Amin2234 171 Reputation points

2022-02-11T16:12:40.807+00:00

@Lz._

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

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Lz._ 9,006 Reputation points

2022-02-12T10:18:32.103+00:00

@Amin2234

This looks like a never ending story... My last update on this case in this file
Please sign in to rate this answer.
Amin2234 171 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?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to export file names into an excel file?

4 additional answers

Your answer