[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets

Andreas Säger wrote:

*Because* spreadsheets are so flexible you can not edit databases with them.

Simply because all you have is a database excerpt in plain text. You never
asked for flexibility. You asked for data integrity (keep the exact same
date encoding when saving back to a database file). You do not want to use
*flexibility*. What you ask for is automatic formatting on import. Yes, the
Base component helps to import database data into preformatted spreadsheets
and Writer documents. Base is *not* a database program. First and foremost
it is a bridge to import various types of tabular data into preformatted
documents.

Calc can do all this without very easily with the help of the Base component
which you strictly reject.

What you asked for in the first posting was about importing the correct
values rather than text and then you want the application to derive the
correct number format code for each cell. This does not happen. Not in Calc
nor Excel nor any other spreadsheet.

Then you mentioned some co-editors. If they do not have have spreadsheet
software at hand, what is the software they use? If you exchange data via
csv, I would assume that they use some kind of database. Otherwise you could
exchange spreadsheets in plain old xls format.

Reply:

I apologize that I was not clear in the fact that I needed a method of
importing date and time data into a spreadsheet so that the date and time
would be a date and time, not text, so I could present the date and time
data according to the appropriate time zone by applying the appropriate
formula to the input date and time data. The early replies supplied the
information that cured my ignorance. I now have many different ways to
convert text formatted date and time data into something I can work with in
a spreadsheet. I also have several other options available that I have yet
to try. The wealth of information presented is impressive.

Once that problem was solved, I was presented with the idea of using a
database or base component (I am still unclear as to the distinction). I
explored the options and warnings for Libre Base and found no advantage and
many disadvantages to using another method to enter, modify, calculate the
results, and display the data. The reason is that once the date and time
data is properly imported, the only process used is to apply the time zone's
UTC offset. For this I add a column if the time zone is not already in use.
In this way I may conveniently present any date and time data correctly for
any time zone in use. I can then use split or freeze to keep the appropriate
time zone column in place while scrolling through the rest of the data.

Much of what I do is ephemeral, so I keep it as simple as possible. For
example, a travel itinerary that spans time zones. A column for each time
zone, a few columns for travel data, and a couple of columns for calculated
incremental duration and total duration. The key feature is that the date
and time data are available in each of the time zones. This also is a
benefit when the traveler wishes to share the itinerary with others. The
time zones of the others can also be incorporated at any time. If I were to
do this type of setup in a database, would I be requried to set up a
separate input field for each time zone with all the other time zones
calculated? In order to make database, would I need to include all of the
one hundred and five possible time zones (Z-12 to Z+14 in quarter hour
increments)?

This feature would have benefited the traveler in Jules Verne's story 'Le
tour du monde en quatre-vingts jours' (1873). If the trip log had kept to
the origin's time zone as well as the current local time, there would have
been no problem as the traveler would have known the origin's date and time
at all times during the trip.

I do use programs that utilize a database. Firefox uses a database for the
information it collects. Calibre puts the input data into a database.

I do not "strictly reject" using a database when appropriate. I do have one
set of data that may be amenable to a database. The input data has five
columns; date and time, observed measurement, data discriminator, historical
applicator used count/future data point, and historical dose applied with
notes/future test units available with notes. I am considering spliting off
the notes that have been inserted in the fifth input column and creating a
sixth input column for notes, but have not arrived at a decision. Currently
I slightly favor leaving it as is as the additional notes are few. The first
four columns do have a fixed format, but the last two or three columns have
a variable format that may change.

The calculation columns are much more complex as I have not integrated all
the calculations required into a single formula, but have additional
calculations done with derived results to achieve the desired result. I
currently have twenty-seven calculation columns that display interim results
for each of the six data discriminator types and for the unfiltered data.

I found lots of information about Libre Base and when I have assimilated it,
I may try to set up a database for the test results. Currently there are
1554 rows of historical data, but as time progresses, the data count will
increase. The rows of future data are projections based upon the historical
data and are replaced with historical data when the observed measurement is
entered. Please note that the data is only current from the time it is
collected to the time the spreadsheet finishes its calculation update after
the data is entered.

A final point is that there are no co-editors. Exploring the option of a
database, I visualized the primary reason I would use a database. I
concluded that a database might be preferable if there were other people
inputting data as that would standardize the data input into the database.




--
View this message in context: 
http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4007326.html
Sent from the Users mailing list archive at Nabble.com.

-- 
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted