Things were going pretty well on the development of the online exhibition that the Peachtree Corners Photography Club was getting ready. I was working “the strings behind the curtain”. Photographers were uploading their entries. I assembled the photos in an online OneDrive folder for the judges. We already had over eighty entries. The judges asked for a listing so they could review the photos and make notes ahead of getting together for the final decisions. All that online, of course. These are still the pandemic times.
No problem, right? Well, getting a list of the photo files in the OneDrive folder is not quite straight forward. Windows 10 presents OneDrive looking just like a local folder. So I decided to use the command-line “dir” command to make a listing. I piped the data into a text file. Worked just fine. I grabbed the data in the text file and pasted it into an Excel spreadsheet. Voila, the judges could do their work.
Well, it didn’t take long before one of the judges reported that there was a photo that was missing from the list. Either that or it was out of order.
I started investigating. The number photos and the number of lines in the spreadsheet were the same. Next, was there an ordering problem? How could that be.
I found the item the judge complaint about. Yep, the listing was not in the correct order, not the way the photos displayed in OneDrive.
Looking at the file name I noticed it contained underscores. We had been very liberal, allowing the photographers to use whatever file names they wanted to use so long as it started with their names or initials. Staring at these names it occurred to me that the “dir” command goes back to the early DOS days of the PC era. I faintly recalled that there were a lot of different ways to organize the “alphabet” – where the strange characters, like the underscore, were placed in the order. I ran a quick test with some made-up files and file names. Yep, the “dir” command comes up with a different order from the way they show in File Explorer. Oh, Microsoft!
My quick fix was to just sort the file listing in the Excel sheet into the correct alphabetic order. It had been only hours, well, a fair number of hours. I thought I was done and sent out the new spreadsheets.
Didn’t take long and there was another complaint of out-of-order file listings. Now what? It took me longer this time. The files, as sorted in the Excel sheet, just didn’t all match the order of the files in the OneDrive folder.
The affected files had numbers in them. I finally found, and confirmed in online searches, that the way numbers are handled in Excel and in File Explorer is different. Oh, Microsoft!
Well, dear reader, you are probably just as confused right now as I was. Let me illustrate. Here are my demo photo files in a folder. The “dir” command listing, the File Explorer window, and the sorted list in an Excel spreadsheet.
The first filename, file_sort-demo-1.jpg” had an underscore. None of the others did. The files ended in numbers, 1, 200, 30, 4.
Notice in the “dir” listing the order came out 200, 30, 4, 1. The last one due to the underscore.
In File Explorer, and that is the same as how the files display in OneDrive, the order is 1, 4, 30, 200. In actual “numerical” order. However, when sorted in Excel, the order is 1, 200, 30, 4. Sorted on the value of the characters, left to right.
There is no way to reconcile Excel sorting with the “name” order in the folders. At least not an easy, workable way, that I could find.
It made me want to use words. You know, those kind of words that would make mothers in olden times run for the soap. Of course, I don’t use those kind of words. Besides, I am not a poet but a photographer. So here are the images of my test files. I tried to make my expression of sentiment fairly subtle.
.:. © 2020 Ludwig Keck