New
#1
EXCEL Gurus Text sorting problems - seems an error here
Hi folks
Seems that Alpha numeric sorting causes a problem in EXCEL
If you look at the example shown in the folder path Z:\Classic TV series\0-9\12 Monk...... comes before Z:\Classic TV series\0-9\6 Million dollar man ..... etc. Anyway to solve this -- am using ENG lang for sort collate so in my understanding "6 followed by a space" should sit before "12". I know that text sorting always has problems but is there possibly a way of replacing the leading space by a zero -- which should solve the issue. I can't rename the original files on the remote server (NAS) though. Look at Rows by the red arrow in the screenshot.
Q2 --- which I can't find an answer to anywhere and I've looked -- is there a way to also add the owner or last user of the data file. If one is tidying up disks ne surely needs to know who owns the data so they can either back it up themselves or delete it before any administrator does it. I know Unix like systems (Linux etc) give those attributes but that's a horrendous amount of work extracting that info from the remote server and massaging the data into my spreadsheet typically like this:
Unix / Linux systems give ownership easily enough :
e.g few files from /home
Windows must have similar stored somewhere so as to be able to control who has access to data.
I know getting into the appropriate directory via Windows DIR and then issuing DIR /Q or for all files withi directory command DIR /Q /S will show it so a script could be done to add to the spreadsheet. However this doesn't worked for Shared Networked files from a NAS / Linux type server as data isn't available as to who is the original / current owner
Here's small output from DIR /Q from the current directory via Windows command line which lists ownership of directories. Adding the /S also goes through the files.
From a NAS / REMOTE server user ownership is missing in Windows :
I really don't want to have to create datasets on the server and pump them across to Windows to merge into EXCEL. Seems a bit of a long way round to do it !!!.
Finally Solved -- simply created a MySQL (now MariaDB) database on the server with GUI front end PHPmyadmin. Created a query on the Linux server with the data I needed -- then I used the EXCEL power query to get data from my database rather than a Folder using Windows explorer. Works brilliantly.
MariaDB was installed on the server so no probs with using that system to create a new query.
Why on earth should this stuff be so difficult -- it's taken me around 3 days to get a simple process to work !!!
anyway "Stttttoooooooopid" management allowed 6 weeks for the task so I'm sitting pretty now !! Some of these management guys have no idea how to estimate time needed for I.T tasks -- nice for some of us freelancers. !!! 6 Weeks money for 1 weeks work !! what's to complain about.
Marking FINALLY as SOLVED.
Perhaps I should give some courses in EXCEL power query. Whatever one might think of Ms, the EXCEL spreadsheet system still has no equal if you need to delve into its inner workings -- why though the Power Qery language is in "M" while the main part of EXCEL still uses VB is a mystery only known to Ms. !!
and if people need EXCEL hints - just drop them here. I'm quite good with a load of this stuff !!
Deserve one of these !! (I.e a bit of blowing my own trumpet - or Halo polishing).
cheers
jimbo
Last edited by jimbo45; 23 Mar 2022 at 10:35. Reason: Added Windows DIR / Q example and now solved