Fill Series-Numbers with 000

[TheMadGuy]

Well, maybe I am the only one but I am sure I am not having this problem:

Imagine you have an ebook-series with e. g. 3000 items with ascending numbers. And you export what you own to have kind of a catalogue in Excel (or OpenOffice/LibreOffice).

No my problem is that LibreOffice doesn't sort numbers "the right way" what means they look kind of

1.0
10.0
11.0
12.0
13.0
14.0
15.0
16.0
17.0
18.0
19.0
2.0
20.0
21.0

and so on. And especially when the number of items has for digits (or even more), I am never able to get a correct ascending (or descending) list in LibreOffice.

So is there any possibility to "tell" Calibre - according to the highest number of any series - to fill the other ones with "Zeros" (000) at the beginning to have all with the same number of digits? So there wouldn't be a number 1.0 anymore but a 0001.0

As I got a load of series with at least 3 digits and some more with even 4 digits, help would be really appreciated to finally get correct ascending lists.

Thanks in advance for your support!

[skb]

You can format the column in LibreOffice to have leading zeros.

https://help.libreoffice.org/6.2/en-...ding_zero.html

I just tried it with Format / Cells and then selected a few leading zeros (from the options just below the decimal places bit).

[capink]

You can setup a composite column with the following template:

{series_index:0>6.2f}

Use this column instead of the standard series_index when creating your csv catalog.

[TheMadGuy]

@capink: When I configure the colums for export I only see this.


How/Where could I add the composite column?

[TheMadGuy]

@skb: Just imagine having countless series. And then doing a manual work for thousands of lines. If I can't get it done with the idea of capink, I might try, but I'd prefer getting the data in the correct format from Calibre before I need to change in LibreOffice

[capink]

Originally Posted by TheMadGuy

How/Where could I add the composite column?

Preferences > Add your own columns (see attachment for more details)

[TheMadGuy]

@capink: Now it worked fine! That's THE ultimate solution! Many many thanks!

[TheMadGuy]

@capink: First I had hope... and it looks great in Calibre. A an example: I got one series, numbers 25.000 - 36.000 in Calibre - but after the export I got 25000 - 26000

But that's not the worst: For another series it works sometimes this way sometimes another: Number 1 - 9 are 1.0000 - 9.000 now but 10 - 99 are 10000 - 99000 and then 100 - 141 are 100.00 to 141.00

Sorry to say but this is totally confusing why one formula works on different ways and only partially delivers the results I need. Would you be so kind and explain that to me?

[chaley]

Originally Posted by TheMadGuy

@capink: First I had hope... and it looks great in Calibre. A an example: I got one series, numbers 25.000 - 36.000 in Calibre - but after the export I got 25000 - 26000

But that's not the worst: For another series it works sometimes this way sometimes another: Number 1 - 9 are 1.0000 - 9.000 now but 10 - 99 are 10000 - 99000 and then 100 - 141 are 100.00 to 141.00

Sorry to say but this is totally confusing why one formula works on different ways and only partially delivers the results I need. Would you be so kind and explain that to me?

First, is period the decimal separator in whatever locale you are in? My guess is 'no', given the screen capture you provided post 4.

If period is the decimal separator:

• If you are seeing 1.0000 and 25.000 in calibre then you didn't use the template @capink gave you ({series_index:0>6.2f}). That template generates a number with leading zeros and 2 digits after the decimal point, e.g., 001.00 and 025.00. What template did you use?
• What values are in the CSV file? Are they the same as you are seeing in LibreOffice?
• You should be able to change the column type in LibreOffice to number, avoiding the entire problem. I am not a LibreOffice user so I can't tell you how to do that. LibreOffice should use locales so it won't recognise numbers with the wrong decimal separator.

If your decimal separator is, for example, a comma then try the template

{#series_index:'re(format_number($, '07.2f'), '\.' , ',')'}

[TheMadGuy]

Seeing the look of my Calibre, üeriod IS the decimal separator. But I still can't understand why in Calibre I got the zeros once behind and once before the separator. I am quite sure that I created the column exactly the way of capinks example:



But one es confusing, which might make comma the separator: In the Calibre list I see it only this way "Series [number]" but if I open a book to change Metadata, in the "number"-Field there is always a number with ",00" (Komma Zero Zero). So I'll try to modify the column the way chaley adviced. Thanks for this new try!

[TheMadGuy]

Okay, new try, first happiness and another load of confusion:
This formula
{#series_index:'re(format_number($, '07.2f'), '\.' , ',')'}
didn't work ("Error in the Original") until I took out "#"
Now the numbers look absolutely great - but only in Calibre!

Then I recognized that this is a problem of LibreOffice: When clicking on the CSV, in the preview the column looks great (0001,00 instead of 1) but as soon as I go the next step, its a simply "1" again.

So I just decided - in the preview of the csv - making this column text instead of numbers and it worked! Now open I see 0001,00 and so on and so far it looks fine. If I got further problems, I'll be back^^

[skb]

Originally Posted by TheMadGuy

@skb: Just imagine having countless series. And then doing a manual work for thousands of lines. If I can't get it done with the idea of capink, I might try, but I'd prefer getting the data in the correct format from Calibre before I need to change in LibreOffice

No worries. I understand. Getting Calibre to do the heavy lifting is the first choice.

Having said that, you don't have to do the formatting for "thousands of lines" in LO. Just select the whole column. Job done. Yes, I may be a spreadsheet tragic, sorry ;-)