This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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)
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
============================
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
Hi @Amin2234
With Get & Transform aka Power Query
1/ Download that Excel file
2/ In a command prompt (CMD):
2/ In Excel:
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:
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:
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
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?
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:
Good on my side:
Could you please check this file too and let me know sort it? Must be sorted according to the alphabet and number.
