Seperate data in cell and change some to date format

English
#1

Hello

The searches I have done have not yielded any results. so please let me know if a solution is available.

The data is imported from a whole lot of MP3 files in a music folder. The pieces of information that I am trying to separate are in a cell which looks like this:
5.3 M 08 10 2020 16:18
At the moment I am manually cutting /pasting into separate columns/ cells:
Size: 5.3 M - Date: 08 10 2020 - Time: 16:18

The first question is - is there a function that I can use to do this ? Text to Columns no good as because of the spaces it splits it into 6 separate cells.

The next is the date which I would like to convert to Thursday 08 Oct 20 but since the digits are also separated by blank spaces just using the formatting brush does not work and I am using Find and Replace to locate blank spaces in between day and month and month and year and replacing them with a / so that Calc recognises it as a date and the formatting can be applied accordingly. Trouble is that it replaces the first space but does not recognise the second one and the cursor shifts to the next cell. However I found that clicking on Find Previous and then Replace does the trick.

Both operations very time consuming - there must be an easier, more effective way. Looked at text, concatenate and trim functions but with my limited programing skills I am not getting anywhere.

Help!!!

#2

I can not see if 8 10 2020 refers to 10 of August (USA) or 8th of October (rest of the world). How should any software be able to decide this?
How can you import these data from a mp3 music file?

#3
  • How do you “import data from a lot of MP3 files”? Don’t understand. Let me guess you mean a kind of directory file.
  • Compounds like 5.3 M 08 10 2020 16:18 are no data at all but just a mess.
  • 08 10 2020 also is no date but just another invitation to errors.

Basically use ISO 8601 for dates, TOD, and DateTime data.

Howt o get good from bad?
See
disask102238resolveSillyCompounds.ods (13.0 KB)

1 Like
#4

BTW - I am on Windows 10
LibreOfiice does - I never use US settings as I find them very confusing but the English (UK) equivalent.

Through a file manager (as Lupp guessed) which prints out the contents of a directory/folder of MP3 files and their properties to PDF. Then simply copy and paste that “data” through Paste Special to "import"it into your spreadsheet and this is what it looks like:

Prince & The Revolution - Kiss.mp3 | 5.3 M | 08 10 2020 16:18 |

Nothing special or extraordinary about it but it’s the format the file manager chooses to print the date and time that causes the issue.

#5

Either use a better file manager or locale that displays a date in an unambiguous date format, or with Paste Special in the dialog select the column and set its type to Date (DMY).

#6

Explanation given above to Villeroy

What do you mean no data at all - it’s straight from my music directory files - what you see with File Explorer depending on what columns you have ticked but produced in that way by the print function of the file manager.
The aim is to correct it so that is not a “mess” but in separated columns and in the format that LO understands.

Again it’s information available when you view music files in Explorer - IT IS A DATE BUT FORMATTED BY THE FILE MANAGER WHEN IT PRINTS THE CONTENTS OF THE FOLDER IN THAT WAY. ARE YOU WITH ME?

it’s not me that is using this format but the conversion the file manager uses when it prints out the “data”.

So the question is how did you convert AsIs to Helper1, Helpert2, DateAsNumber?
Did you do it manually like I have been doing or was it by magic?

If manually then imagine doing that for hundreds of entries which is why I posted the question - are there function that can be used that will do it automatically? Which you have not addressed.

#7

I attached a file. The sheet therein shows the proceeding for one (typical) case.

Of course it wasn’t you who iinvented that kind of compound I called “no data at all” and “a mess”.
Did i miss a line where you told exactly what file manager was that “a file manager (as Lupp guessed)” you used and how you got the so-called data into your sheet?
Anyway: Widespread software often isn’t made to be good, but to produce good earnings. If the format of compound information you got is the only one your file manager supports, it obviously is of that kind.
Again: My attached example is a serious attempt to help you out of the issue. And if you again use a bad date-time format then, I have no means to avoid that. Just a well considered suggestion: Use ISO 8601 for many good reasons.

#8

THERE IS NO REPEAT NO, AMBIGUITY with date format . You are the one who is confused about it or raising it as an issue when it is not, has never been nor will ever be.

"Either use a better file manager “” - Oh yea! - any suggestions that can outdo Total Commander - feel free to post them.

You are joking, aren’t you about switching locale and for what reason?

Tried it - made no difference. still comes out as 08 10 2020 16:18.
And entering today’s date (18/02/2024) in that cell it comes out as : 182Y - hey what? what about the time bit?

You are not getting the point - it’s the blank spaces in between that causes Calc not to recognise the input as a date. Nothing I can do about that. I need a solution that replaces the repetitive copy/paste, find replace,

#9

If you got 182Y after entering a date 18/02/2024 then you formatted the cell as DMY. That is not what I was talking about. I was mentioning the import dialog that pops up upon Paste Special as text and within that dialog set the import column type to Date (DMY).

#10

Yes and thank you but you still haven’t revealed what method you used to get from AsIs to Helper1, to Helpert2, to DateAsNumber and so I must presume it was the copy and paste that I use.

No but it’s in my reply to erAck. Your stating “Let me guess you mean a kind of directory file.” indicated to me that you would be implying the use of a file manager to read the “directory file”.

Well do you know how to print a directory listing using Windows File manager? Can it? If not then yes I agree with you, MS often isn’t made to be good but only out to make huge profits. Hope that does not get me into trouble but hey isn’t that why we use and support open source software?

Thanks for trying, maybe I need to look for an open source program that lists and prints directory listings with ISO 8601 in mind and which Calc can cope with,

#12

BTW - thanks for your suggestions erAcK .

Found the import column. playing with it and the different separators as the file details are all in the one line .
Will let you know how I go but if you would like to try it for yourself here is a sample:

Aretha Franklin - Freeway Of Love.mp3 5.6 M 09 02 2018 00:03 -a–
Aretha Franklin - Who’s Zoomin’ Who.mp3 6.5 M 09 02 2018 13:40 -a–

#14

There are formulas. Evaluating formulas is the main purpose spreadsheets are made for.
However, you can actually use the old Dir command to get a much better usable listing of file properties which can be tzreated by Text To Columns.
See new attachment.
disask102238resolveSillyCompoundsWithVariantBasedOnDosDir.ods (46.5 KB)

#16

We can not know this. And there is plenty of software using US dates anyway. LibreOffice provides full control over these settings when importing text data from plain text files or clipboard. You get an import dialog where you are prompted to describe the incoming data. The locale prompted in the text import dialog is about the locale which is used to translate the incoming text data. It is not about what you want as output. Any locale setting will fail in this particular case, because day, month and year are separated by spaces, which is rather uncommon. In cases like this, you have to mark the column as DMY date which works well with many uncommon date formats.

#21

Ok you didn’t mention a leading file name in the original question, without that could had worked with fixed size columns. But this needs pre-processing.

#22

see

I used a | to indicate a space, so it actually looks like:

Prince & The Revolution - Kiss.mp3 5.3 M 08 10 2020 16:18

The problem is that when importing the only portion of the data that gets put into a column because it is separated to the rest by a (- ) is Prince & The Revolution which I have added to Other separation . The rest are all in one line with spaces in between .

Thank you Lupp . A viable solution for sure. That is so much better and easier and thanks Wanderer for mentioning it too.

The data looks like this according to my local date settings:
08/10/2020 16:18 5,644,466 Prince & The Revolution - Kiss.mp3
09/02/2018 00:03 5,971,422 Aretha Franklin - Freeway Of Love.mp3
09/02/2018 13:40 6,853,049 Aretha Franklin - Who’s Zoomin’ Who.mp3

And when imported separated by a - under Other :

import — LibreOffice Calc
import — LibreOffice Calc717×598 33.8 KB

It will then be simply a matter of joining columns to account for artists, song titles, might cut the .mp3 altogether, then adding a . for the file size after the first digit (5.644466) and delete decimal places to read 5.64.
The list can then be sorted by which ever criteria I want, Need to add columns headers (Date, Time, MB, Artist etc…)

There other bits of information that would be nice to extract - like bitrate, length and year - can that be done with the same dir DOS command?

Thank you all for your contributions.

Will also be investigating this one, Wanderer to see if can extract more information than the dir command. Thank you.

#11

Hallo
weird workflow!!
why not open a new calc.ods and run:

from pathlib import Path
from datetime import datetime as dt

def get_music_folder_info():
    
    folder = Path.home()/ "to" / "your" / "musicfolder" #change to your needs!!
    out = []
    for p in folder.glob("*.mp3"):
        out.append((f"{p.name}",
                    f"{dt.fromtimestamp(p.stat().st_ctime):%Y-%m-%d %H:%M:%S}",
                    f"{p.stat().st_size}"))
        
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.CurrentController.ActiveSheet
    sheet[:len(out), :len(out[0])].FormulaArray = out
#13

Hello karolus

Thank you, would love to try out your solution but silly question (as a complete novice in programming) for which I apologise in advance:

how do you run the code?

#15

Avoid the situation and use something old like command line with an very old DOS command:
DIR /B *.MP3 >mp3files.csv and import the resulting .csv in Calc. (Actually already mentioned by @Lupp)

I use a slightly more advanced “scanner” named ExifTool wich has a -csv option for my Image Database, and docs tell ExifTool can also read tags from .mp3
https://exiftool.org/exiftool_pod.html

#17

have a look on my file and play with it to acknowledge the complexity:
seperate string_v0000_122210.ods (22.7 KB)

#18

=DATE(VALUE(C10);VALUE(C9);VALUE(C8)) returns a valid day number, a “spreadsheet date”.

btw: in the input string 5,3 Mbyte 8 10 2021 16:180:9876543210, is 5,3 a comma decimal or is it a “5” followed by a column separator and then “3 Megabyte”? Nobody can be sure about these things.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.