Limit the text in a Calc sheet - - with 8000 lines

[sayhello]

hello dear Community,


how to do the following in a calc-sheet

have a calc sheet with 8000 lines - each line contains text - bout 100 to 300 characters

i want to limit the text to max 15 characters - can i do that!?

[RoryOF]

What happens to the 285 other characters of a 300 character text?

[F3K Total]

crossposting

[sayhello]

hello and good day

many many thanks -

see an example of the test in the lines - here:

καὶ νὺξ οὐκ ἔσται ἔτι καὶ οὐκ ἔχουσιν χρείαν φωτὸς λύχνου καὶ φωτὸς ἡλίου, ὅτι κύριος ὁ θεὸς φωτίσει ἐπ᾽ αὐτούς, καὶ βασιλεύσουσιν εἰς τοὺς αἰῶνας τῶν αἰώνων.
Καὶ εἶπέν μοι· οὗτοι οἱ λόγοι πιστοὶ καὶ ἀληθινοί, καὶ ὁ κύριος ὁ θεὸς τῶν πνευμάτων τῶν προφητῶν ἀπέστειλεν τὸν ἄγγελον αὐτοῦ δεῖξαι τοῖς δούλοις αὐτοῦ ἃ δεῖ γενέσθαι ἐν τάχει.

......[....].......

i thougth that this action was necessary due to the limits of uploads here - and i see - i can not upload all.

what i want to do is the following - it is the full text of the novum testamentum graece

i want to have added all the books and chapters -(they gone lost).

See an example....

first question - how to limit the text - in order to be able to do a full upload?

[jrkrideau]

With text in D1
=LEFT(D1;15)

[RoryOF]

So, is it your intention to add in, in front of each line, the book, chapter and verse appropriate to that line? In that case, you should make a new column before the text column. In that column you can enter the first book, the chapter and the verse, then pull down for the number of verses in that chapter. You will have to repeat this after changing the Chapter number and so on until the book is completed.

There will be a lot of editing. A quick search suggests that there are about 27 books, a total of 260 chapters and just under 8000 verses in the New Testament (this may differ depending on just which version one uses, but it gives an approximate figure).

[Villeroy]

Why do you dump human language in an arithmetic calculator?
viewtopic.php?f=9&t=73827
http://www.mediafire.com/download/3ltiv ... lendar.odb

[sayhello]

hello dear RoryOF, hello dear Villeroy, good evening dear jrkrideau,


first of all - many many thanks for the reply with your constructive ideas & thoughts.

jrkrideau,

Code: Select all

=LEFT(D1;15) 

tried to apply - but with no success; ive added a example in the attached files...

@all of you: especially RoryOF - ive got the NT Graece consisting with 7942 verses - in lines: each line contains a Verse

see some New Testament Statistics - eg.... New Testament Statistics compiled by Felix Just, S.J., Ph.D. http://catholic-resources.org/Bible/NT- ... -Greek.htm
The statistics are based on the "Analytical Greek New Testament" (AGNT) as provided by the "Bibloi" program from Silver Mountain Software.

NT Latin
Vulg. NJB NAB KJV REB NIV RSV NRSV
Acts 10: 48 48 48 49 48 48
Acts 19: 40 40 40 40 41 41
2 Cor 13: 13 13 13 13 14 14
3 John: 15 15 15 15 14 14
Total in Acts: 1006 1006 1006 1007 1007 1007
TOTAL in NT: 7956 7956 7956 7957 7957 7957

see more here http://catholic-resources.org/Bible/NT- ... -Greek.htm

i would love to add the whole file here to the forum. to show what i want to do... but at the moment i am not able to attach this...
Well - i need to do exactly the processes that are described by you RoryOF - i need to have the structure.

... note: after i have achieved the i want to identify certain verses.... for each day .... and select them
first of all ; i need to create the structure...

i try to achieve this.. and come back and report all the findings.

i am glad for any and all help - and will appreciate this

[RoryOF]

I suspect that this is reinventing the wheel. I haven't carried out a search, but am reasonably sure that a a suitably formatted document could be found with some careful searching.

Villeroy's point about not using an arithmetic calculator for text input is valid. Consider: a text is linear - it has no validity if reorganised. The verse identifier (Book, Chapter, verse number) ought be integral with the verse. If the intention is to extract a random verse for each day, this can be done another way, say by generating a random number Mod 7942, which would give a pointer to a specific line within a linear document

I'm possibly offline for much of today.

[karolus]

Hallo

hello dear RoryOF, hello dear Villeroy, good evening dear jrkrideau,
first of all - many many thanks for the reply with your constructive ideas & thoughts.

Oh --
we response decidedly polite here -- in opposite to no response for crossposting in german forum
-- very nice

[B Marcelly]

Hi,
A much simpler solution :

1. Select column A (click on the header A)
2. Right click > Column Width... choose 3 cm

Now you read only the start of the texts. You can reduce or enlarge the column width with the mouse.

[RoryOF]

It is my understanding that each verse does not contain the book/chapter/verse reference, but it's all Greek to me (joke!) [read: I do not understand the detail of texts]

[sayhello]

hello dear RoryOF, dear Karolus, and BMarcelly, and dear Villeroy and jrkrideau,


[note this is a short post since i am short of time at the moment - will respond more later the day ]



First of all many many thanks for all the help and the advices. i will try to do the further trials in reworking the sheet with the 7942 lines in order to upload it here.
This thread shows me how supportive this forum is!!

@ Karolus: many thanks for the ideas - the hints and yes: also for the hint in German. a big big SORRY for the CP.
Note: i am trying to achieve the work and i am trying to treat the calc-document as you adviced me. But i have more time to do so at the weekend.


the goals:
what i want to achieve is not a scientific bible or some thing like that.
we allready have that. see interlinear Textsynopsis of the Old Testament http://biblehub.com/interlinear/genesis/1.htm
a interlinear Textsynopsis of the New Testament http://www.scripture4all.org/OnlineInte ... f/mat1.pdf

that is not necessary to rework and reinvent.

my purpose is to produce a text-calendar with daily quotes (and texts) - see the attached file called " _Daily_texts___2016__.ods"
note: there are the so called losungen (quotes) for the year 2016 in

-German (see the attached file called daily texts - here we have the 365 verses .- /(note all of them are new testament text-quotes...)
-Greek - i want to have the corresponding greek texts

therefore i need to get the Greek Novum Testamenum in a structured form - so that i can identify the greek verses for the daily text-quotess


i have to close now - but will respond more later the day...

love to hear from you !! Have a great day!!

regards
say


see the sources that i use...
http://www.losungen.de/download/ - with the excel-file...
http://www.moravian.org/todays-daily-text/daily-text/

[Villeroy]

It has been done already.
Open my database file.
Open the reports.
To change the language for the year calendar, open the year form, change the language and open the report again.
For light changes in layout and formatting, right-click the report and open it for editing. It is a Writer table with 2 placeholder rows.
If you don't like the layout, right-click the report's underlying query and create a new report.
If the layout options are too limited, install the report builder extension, restart the office suite and try again.

[sayhello]

hello dear Villeroy,

first of all - many many thanks for your answer and the solution! Note; i just discovered it - meanwhile you created this calendar db last year.
At a first glance - It looks very very promising..

It has been done already.
Open my database file.
Open the reports.
To change the language for the year calendar, open the year form, change the language and open the report again.
For light changes in layout and formatting, right-click the report and open it for editing. It is a Writer table with 2 placeholder rows.
If you don't like the layout, right-click the report's underlying query and create a new report.
If the layout options are too limited, install the report builder extension, restart the office suite and try again.

again - many thanks - all looks very very sophisticated
as for the advices and processes - i did as adviced above - and openend the report-tool

cult-calendar Year calendar

question: does this work for the text-quotes for 2016 too.
should i do anything here - eg. load them form the losungen 2016? - from http://www.losungen.de/download/

here some lines of the results for the next days...- from 2015 december 3rd till 7th

Donnerstag, 3. Dezember 2015 Revelation 3:8
οἶδά σου τὰ ἔργα, ἰδοὺ δέδωκα ἐνώπιόν σου θύραν ἠνεῳγμένην, ἣν οὐδεὶς δύναται κλεῖσαι αὐτήν, ὅτι μικρὰν ἔχεις δύναμιν καὶ ἐτήρησάς μου τὸν λόγον καὶ οὐκ ἠρνήσω τὸ ὄνομά μου.

Freitag, 4. Dezember 2015 Romans 2:11
οὐ γάρ ἐστιν προσωπολημψία παρὰ τῷ θεῷ.

Samstag, 5. Dezember 2015 1 Thessalonians 5:18
ἐν παντὶ εὐχαριστεῖτε· τοῦτο γὰρ θέλημα θεοῦ ἐν Χριστῷ Ἰησοῦ εἰς ὑμᾶς.

Sonntag, 6. Dezember 2015 1 Thessalonians 5:5
πάντες γὰρ ὑμεῖς υἱοὶ φωτός ἐστε καὶ υἱοὶ ἡμέρας. Οὐκ ἐσμὲν νυκτὸς οὐδὲ σκότους·

Montag, 7. Dezember 2015 1 Timothy 2:3
τοῦτο καλὸν καὶ ἀπόδεκτον ἐνώπιον τοῦ σωτῆρος ἡμῶν θεοῦ,

Above all: This calendar makes an excellent solution. I have already used it and played around with it.
I'm very glad that i have posted the request - after some trials to find out a solution for the issues.

so the question is - the allready produced cult-calendar - works with the text-qutes for the year 2015, Can i do this with the text-quotes of the next year - 2016?

love to hear from you


Thank you Villeroy - thank you!!

greetings
say

[Villeroy]

so the question is - the allready produced cult-calendar - works with the text-qutes for the year 2015, Can i do this with the text-quotes of the next year - 2016?

Open the form "Year Calendar", change the year and re-open the report.

[sayhello]

hello dear villeroy

many thanks for the quick reply - you re very quick!

so the question is - the allready produced cult-calendar - works with the text-qutes for the year 2015, Can i do this with the text-quotes of the next year - 2016?

Open the form "Year Calendar", change the year and re-open the report.

i used the base-db that i found here in this thread: viewtopic.php?f=9&t=73827
you ve created it last year in november i guess.

i did as you adviced -. and got back the following resuits in the subsequently created report

Freitag, 1. Januar 2016 Kolosser 3:15
καὶ ἡ εἰρήνη τοῦ Χριστοῦ βραβευέτω ἐν ταῖς καρδίαις ὑμῶν, εἰς ἣν καὶ ἐκλήθητε ἐν ἑνὶ σώματι· καὶ εὐχάριστοι γίνεσθε.

Samstag, 2. Januar 2016 Römer 5:2
δι᾽ οὗ καὶ τὴν προσαγωγὴν ἐσχήκαμεν [τῇ πίστει] εἰς τὴν χάριν ταύτην ἐν ᾗ ἑστήκαμεν καὶ καυχώμεθα ἐπ᾽ ἐλπίδι τῆς δόξης τοῦ θεοῦ.

Sonntag, 3. Januar 2016 Lukas 12:15
εἶπεν δὲ πρὸς αὐτούς· ὁρᾶτε καὶ φυλάσσεσθε ἀπὸ πάσης πλεονεξίας, ὅτι οὐκ ἐν τῷ περισσεύειν τινὶ ἡ ζωὴ αὐτοῦ ἐστιν ἐκ τῶν ὑπαρχόντων αὐτῷ.

Montag, 4. Januar 2016 Matthäus 6:12
καὶ ἄφες ἡμῖν τὰ ὀφειλήματα ἡμῶν, ὡς καὶ ἡμεῖς ἀφήκαμεν τοῖς ὀφειλέταις ἡμῶν·

but this is exaclty the same as in the year 2015 - see the following excerpt that i actually created by looking at the spreadsheet of the dally-text - the losungen of the year 2015 that i just downloaded at the page http://www.losungen.de/download/

Well i guess that you ve created it last year in november: Unless you did not make usage of the new text-quotes (for the new year 2016) then it is just impossible to get "better" results than shown above; see the results for the fist days in the year 2015

01.01.2015 Donnerstag Kolosser 3,15 Seid dankbar.
02.01.2015 Freitag Römer 5,2 Durch Jesus Christus haben wir den Zugang im Glauben zu dieser Gnade, in der wir stehen, und rühmen uns der Hoffnung der.....
03.01.2015 Samstag Lukas 12,15 Niemand lebt davon, dass er viele Güter hat.
04.01.2015 Sonntag Matthäus 6,12 Vergib uns unsere Schuld, wie auch wir vergeben unsern Schuldigern.

just compare the "losungen for the year 2015 ",,,, The text-quotes are exactly the same! so - it seems to be evident that there are some issues - but wait - Perhaps i did something wrong?!!!!

hmmm - not sure if i was processing all the adviced steps of creating the report correctly
in other words - perhaps i am wrong and did some mistakes. If so - please set me straight....

if not - we should use the version of "losungen" for the new year - 2016....

love to hear from you

greetings

sayhello

update:

well and if we have a closer look at the year 2017 then we get the same results...

Sonntag, 1. Januar 2017 Colossians 3:15: καὶ ἡ εἰρήνη τοῦ Χριστοῦ βραβευέτω ἐν ταῖς καρδίαις ὑμῶν, εἰς ἣν καὶ ἐκλήθητε ἐν ἑνὶ σώματι· καὶ εὐχάριστοι γίνεσθε.
Montag, 2. Januar 2017 Romans 5:2: δι᾽ οὗ καὶ τὴν προσαγωγὴν ἐσχήκαμεν [τῇ πίστει] εἰς τὴν χάριν ταύτην ἐν ᾗ ἑστήκαμεν καὶ καυχώμεθα ἐπ᾽ ἐλπίδι τῆς δόξης τοῦ θεοῦ.
Dienstag, 3. Januar 2017 Luke 12:15: εἶπεν δὲ πρὸς αὐτούς· ὁρᾶτε καὶ φυλάσσεσθε ἀπὸ πάσης πλεονεξίας, ὅτι οὐκ ἐν τῷ περισσεύειν τινὶ ἡ ζωὴ αὐτοῦ ἐστιν ἐκ τῶν ὑπαρχόντων αὐτῷ.
Mittwoch, 4. Januar 2017 Matthew 6:12: καὶ ἄφες ἡμῖν τὰ ὀφειλήματα ἡμῶν, ὡς καὶ ἡμεῖς ἀφήκαμεν τοῖς ὀφειλέταις ἡμῶν·
Donnerstag, 5. Januar 2017 Matthew 6:9: Οὕτως οὖν προσεύχεσθε ὑμεῖς· Πάτερ ἡμῶν ὁ ἐν τοῖς οὐρανοῖς· ἁγιασθήτω τὸ ὄνομά σου·

... and so forth. guess that this is and will be the same untill the end of times..
now i try to add the new text quotes to the db...

[Villeroy]

Yes, when I did this in 2014 365 texts sorte out - workin on a calendar with greek-NT, I misunderstood the purpose of that database. I thought it should be an eternal calendar with one distinct verse for each day in any year in Greek, German or any other language you have a translation for. A major problem with requests like this is that we never get any details about the input data, their origin, their file format, the access mode so we never know the exact starting point of the problem to be solved.

Now I know that you download the verses from a distinct web site as tab-delimited csv files, each year has another set of verses and you are interested in something like my database's yearly report, right?
The solution may be very simple since we can connect a Base document to a collection of csv files so each file appears as one database table and then create a report from one csv file.
Is it the report named "Year Calendar" what you are after? It reflects the columns that are labeled in your files as "Datum", "Losungsvers" and "Lehrtext" disregarding the columns "Sonntag", "Losungstext", "Lehrtextvers" and "Wtag". The weekday displayed in my existing report is derived from the date.
The attached zip file contains a folder with a database document and 2 csv files. The one for 2016 is the currently active one renamed to "Losungen.csv". I created a query selecting the 3 columns of that file and copied the year report from the other database into this one.
Open the database document. The data are not stored in the database document. The 2 tables reflect the contents of the 2 csv files.
The query selects 3 columns from the table named "Losungen".
The report refers to that query.

[sayhello]

hello again, good day dear Villeroy,

first of all - many thanks Villeroy for your continued help.

Now I know that you download the verses from a distinct web site as tab-delimited csv files, each year has another set of verses and you are interested in something like my database's yearly report, right?

Yes - this is aimed: then i can add the results into a calendartemplate - like the following

The solution may be very simple since we can connect a Base document to a collection of csv files so each file appears as one database table and then create a report from one csv file.

yes i attache a file of the text quotes that need to be added to the Base-db accordingly. - See the attached document... "the year 2016 with the Lehrtext and the Lehrtextverse"

what is aimed: i want to have a parallel translation of the so called "Lehrtextvers" and the "Lehrtext" se two examples:

as for the 2 nd of January 2016

"Lehrtextvers": Johannes 10,27
"Lehrtext": Jesus Christus spricht:/ Meine Schafe hören meine Stimme, und ich kenne sie und sie folgen mir.

and for the 3 nd of January 2016

"Lehrtextvers" 1.Petrus 2,9
"Lehrtext": Er hat euch berufen von der Finsternis zu seinem wunderbaren Licht.

and what is aimed: .... to translate the Lehrtext into GREEK.
in other words: here i want to have a parallel translation - that means - i want to have the Lehrtextvers translated to Greek language

Background: with these translations in a daily calendar - i want to refresh the knowldge in Greek-language on a daily base. With this individual calendar that i use. I am a heavy-user of a paper-based Calendar. And with the translation of the text it gets more and more valuable: For its superb value as great reference-tool and its practical value in day-to-day use for me who know German as a first language and Greek as a language that ive learned years ago. This German-Greek-parallel New Testament is great as i can refresh the language-knowldge of the colloquial Greek (Koine) language of the Apostle Paul's time.

i ve attached a file that contains the data for the year 2016 - see some of them,,,,

01.01.2016 Freitag Epheser 3,20-21 Dem, der überschwänglich tun kann über alles hinaus, was wir bitten oder verstehen, nach der Kraft, die in uns wirkt, dem sei Ehre in der Gemeinde und in Christus Jesus zu aller Zeit, von Ewigkeit zu Ewigkeit! Amen.
02.01.2016 Samstag Johannes 10,27 /Jesus Christus spricht:/ Meine Schafe hören meine Stimme, und ich kenne sie und sie folgen mir.
03.01.2016 Sonntag 1.Petrus 2,9 Er hat euch berufen von der Finsternis zu seinem wunderbaren Licht.
04.01.2016 Montag Epheser 3,17 Christus wohne durch den Glauben in euren Herzen.
05.01.2016 Dienstag Lukas 21,38 Alles Volk machte sich früh auf zu ihm, ihn im Tempel zu hören.
06.01.2016 Mittwoch Kolosser 1,16 In Christus ist alles geschaffen, was im Himmel und auf Erden ist, das Sichtbare und das Unsichtbare, es seien ....

Again - as mentioned above: all i want to have the Lehrtextvers translated to Greek language - i want to have the parallel-translation of all the above mentioned texts. Thats all.

Is it the report named "Year Calendar" what you are after? It reflects the columns that are labeled in your files as "Datum", "Losungsvers" and "Lehrtext" disregarding the columns "Sonntag", "Losungstext", "Lehrtextvers" and "Wtag". The weekday displayed in my existing report is derived from the date.

Yes - wecan disregard the Columns with Sonntag, Losunstext! - Those things are not necessary.
In fact - all, that is wanted and needed are the "prerequisites" called "Lehrtextvers" and the "Lehrtext" - and what is aimed: .... to translate the Lehrtext into GREEK.

Now i try to figure out what you did - and try to do all the steps that you adviced.

The attached zip file contains a folder with a database document and 2 csv files. The one for 2016 is the currently active one renamed to "Losungen.csv". I created a query selecting the 3 columns of that file and copied the year report from the other database into this one. Open the database document. The data are not stored in the database document. The 2 tables reflect the contents of the 2 csv files. The query selects 3 columns from the table named "Losungen".The report refers to that query.

.- i try to get done all the steps..... Later the day i come back and report all the findings...

Many thanks for all your help and support!

say;-)

[sayhello]

Hello dear Villeroy - i get back the following report.

Die Abfrage mit dem Ausdruck
'SELECT "Datum" AS "Date", "Losungsvers" AS "Verse", "Lehrtext" AS "Text" FROM "Losungen"'
konnte nicht ausgeführt werden.
Überprüfen Sie Ihre Datenquelle.

hmmm i will try it again....

Best regards
Say

[Villeroy]

The status bar indicates the type and location of the database connection. It reads "Text" and a path to some folder.
The folder can be manipulated like this:
menu:Edit>Database>Properties...
Path to text files: /some/path/where/you/extracted/the/downloaded/zip/
Hit the [Browse...] button to change the connection path.
Now you should see the 2 tables in the tables section. They are named "Losungen" and "Losungen Free 2015" after the 2 csv files "Losungen.csv" and "Losungen Free 2015.csv" which actually keep all the data. The query selects from the "Losungen" table, the report uses the query data.

Next year you will download a new file "Losungen Free 2017.csv", rename the old "Losungen.csv" to "Losungen 2016.csv" and rename the new file to "Losungen.csv". When you reload the database, the report still refers to the "Losungen" which had been renamed from "Losungen Free 2017.csv". No setup, no programming. Just rename the files accordingly.

If you really want the report in a spreadsheet:
menu:Tools>Options>Base>Databases...
Register your Losungen.odb under some name, e.g. "Losungen" or any other name.
Open your Calc file, get an empty sheet, hit F4 to open the data source window, open your "Losungen", the "Queries" therein and then drag the query icon "qYearCalendar" to A1 of the blank sheet.
This dumps a flat table into the sheet. Next year, after renaming the new Losungen.csv, you click any cell in that import area and call menu:Data>Refresh to pull the new data into the spreadsheet.

Your calendar sheet can be set up with links to the flat table. Mind the leap years.

[sayhello]

Hello dear Villeroy,


many thanks for the continued help, the additional hints and data-sets. All your help is apreciated.

i did all as adviced - and set the files to this path:

C:\Users\Helmut\Desktop\martin\a_kalenderbau\db_files_villeroy\Losungen
- Losungen_Free_2015.csv
- Losungen.csv
- Losungen.odb

Next year you will download a new file "Losungen Free 2017.csv", rename the old "Losungen.csv" to "Losungen 2016.csv" and rename the new file to "Losungen.csv". When you reload the database, the report still refers to the "Losungen" which had been renamed from "Losungen Free 2017.csv". No setup, no programming. Just rename the files accordingly.

Registering the db: i registered the db ( named losungen.odb ) to the same path

C:\Users\Helmut\Desktop\martin\a_kalenderbau\db_files_villeroy\Losungen

Then i open the db "Losungen", the "Queries" therein and then drag the query icon "qYearCalendar" to A1 of the blank sheet.

and followed all the described processes accordingly.

Open your Calc file, get an empty sheet, hit F4 to open the data source window, open your "Losungen", the "Queries" therein and then drag the query icon "qYearCalendar" to A1 of the blank sheet. This dumps a flat table into the sheet.

see the result below - i have the verse and the according text : .... but no greek text....
see the attached file - it shows the ourcome of the process. No greek text no translation appears - did i something worng; Did i miss something!?

Look forward to hear from you!

regards say;-)

[Villeroy]

OK, including the greek text. Where does it come from? Are there similar csv files with the greek texts for 2016?

[sayhello]

hello dear Villeroy

many many thanks for the quick answer - you are very very fast. I sent you the link to the dl of the file as pm.
You get the full greek text. I can imagine that you were able to set up the db as a translation-engine.

Note: due to several reasons, i am not able to post it here in the forums
one reason ...- (all the file-uploads are limited to 130 KB ....)

love to hear from you ...

greetings
say

[sayhello]

hello dear all -

just wanted to let you know that all runs well and the db also works pretty good.

one quesiton - can i reuse the db for creating the calendar phrases of the next year - is this possible?

Guess that i need to add the so called "Losungen" for the next year.

. say

[Villeroy]

http://www.mediafire.com/download/ofdk7 ... Verses.zip includes a readme file.

[sayhello]

hello dear villeroy


many thanks - i will download and read all the man-pages carefully.

Retards say

[Villeroy]

Copy the relevant German info from a spreadsheet.
Relevant info is in 3 columns:
01.06.17 Lukas 2,29-30 Herr, nun lässt du deinen Diener in Frieden fahren, ...
Select the icon of the database table "GEMAN_VERSES" and paste.
A dialog pops up where you choose to append data and map the date, the verse locator and the verse text to the 3 columns.
Enter 2017 in the Y column of the filter table (or use the input form).