[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
Am 13.09.2012 00:49, JAMES MAJESKI wrote: Name one feature that makes a database better than a spreadsheet when flexibility of input is required. -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4007128.html Sent from the Users mailing list archive at Nabble.com. *Because* spreadsheets are so flexible you can not edit databases with them. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
Am 13.09.2012 05:18, JAMES MAJESKI wrote: So far nobody has come up with a reason for me to switch to a database where I risk loosing data if I attempt to make a change to the layout. -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4007154.html Sent from the Users mailing list archive at Nabble.com. 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. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
Am 13.09.2012 05:03, JAMES MAJESKI wrote: My only queries of the data are handled adequately by the calculations done in adjacent columns. Nothing special, complex, or complicated will easily replace it nor be more useful. 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. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
Am 13.09.2012 11:54, Andreas Säger wrote: Am 13.09.2012 05:03, JAMES MAJESKI wrote: My only queries of the data are handled adequately by the calculations done in adjacent columns. Nothing special, complex, or complicated will easily replace it nor be more useful. 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. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
Am 10.09.2012 22:30, Andreas Säger wrote: Am 10.09.2012 19:40, Andreas Säger wrote: Base is our database component. It is very, very underdeveloped, nevertheless underestimated. Even the worst database tool does a better database job than the best spreadsheet can do. Since nobody ever uses Base for plain text databases, here is another approach without Base and without macro programming: 1) Prepare a sheet template and format entire columns to your liking, according to the incoming fields. 2) Open the text file in a text editor. 3) Copy all. 4) Open a new spreadsheet from the prepared template, paste and fill out the text import dialog (US English, special numbers, delimiter). That was solution #1a (copy plain text into preformatted template). Solution #1b: Import correct values, copy, paste-special numbers, dates and text into a preformatted template. Solution #2: Import everything as plain text, calculate, filter and sort with function VALUE. Requires spreadsheet skills which seem to be rare these days. Solution #3: Hit F4 and dragdrop your text file into a preformatted sheet. Solution #3 is what I use to do if the source data allow this. It is by far the easiest and most convenient method to deal with tabular text data in office documents. All it takes is a little bit of setup work. Any solution that requires text-to-number conversion will not format the resulting numbers to your liking. So you are at the point where solution #1b applies. -- For unsubscribe instructions e-mail to: users+h...@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
Re: [libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
Hi :) Ahhh, we shorten MS Office to MSO. It might be only LibreOffice lists and a few other such places that use it that way. Regards from Tom :) From: JAMES MAJESKI jamesmaje...@gmail.com To: users@global.libreoffice.org Sent: Thursday, 13 September 2012, 4:03 Subject: [libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets My only queries of the data are handled adequately by the calculations done in adjacent columns. Nothing special, complex, or complicated will easily replace it nor be more useful. I can see how a database between libraries would benefit all the libraries that subscribe to the common database, but my requirements are so simple that a simple spreadsheet does everything I need. You mentioned MSO so I looked it up. The following are the results of that search: macro saccadic oscillation Mail Stream Optimization Main Street Office Maintenance Spare Optimization Maintenance Stores Office Maintenance Support Office Major Sales Opportunities Major Service Outage Major Sponsoring Organization Managed Services Organization Management Services Officer Manager of Search Operations Manufacturers Statement of Origin Manufacturing Shop Order map support office (US DoD) Maplewood/South Orange (New Jersey) marine safety office(r) (US DoD) Marine Survey Office Mariposa Symphony Orchestra (Mariposa, CA) Maritime Security Operations (US) Marketing Sales Office Marketplace Services Organization (Canada) Martha Stewart Living Omnimedia Inc (NYSE) Master Security Officer Material Status Officer McKeesport Symphony Orchestra (McKeesport, PA) Medical Second Opinion Medical Service Organization Medical Stores Organization Medical Support Order (health care) Mees Solar Observatory (Maui, Hawaii) Melbourne Symphony Orchestra Methadone Support Organization Methionine Sulfone (code for modified amino acid) Methylated Seed Oil Mexican Spotted Owl Microsoft Office Microsoft Outlook Mid-Atlantic Symphony Orchestra (Ocean City, MD) Middlesex Sheriff's Office (Medford, MA) Midland Symphony Orchestra (Midland, Michigan) Migrant Student Organization (various organizations) Military Systems Organization (US DoD) Military Satellite Officer Military Service Obligation Military Supply Officer MILSATCOM Systems Office Milwaukee Symphony Orchestra (Milwaukee, WI) Mind Sports Olympiad Mind Sports Organization (est. 1997; Canada) Minesweeper, Ocean (Non-Magnetic) Minor Service Order Mission Safety Officer Mission Space Objects Mission Staging Operation(s) Mission Support Officer Missoula, MT, USA - Missoula International (MSO Airport Code) Mixed Signal Option (Teradyne testers) Mixed Signal Oscilloscope Mobile Switch Office Mobilization Staff Officer (US DoD) Molecular Spin Orbital Molten Salt Oxidation Monadic Second-Order Logic Montessori School of Ojai (Ojai, CA) Montreal Symphony Orchestra Morale Support Officer Most Serious Offense (criminal justice) Most/Main/More Significant Other (Polyamory) Moving Swiftly On MSE Systems Overhaul Multi-System Operator(s) Multi-Service Operation Multi-cultural Student Organization Multimedia Service Operator Multiple Service Operator Multiple Subscriber Organization multiple system operation(s) Multiple System Operator Multi-system Operator (as in satellite/cable TV networks) Murray Symphony Orchestra (Murray, UT) Muslims Speak Out Must Start On (project management) Mustard Seed Oil Mutual Service Office (insurance) My Simple Office (Simple Office Solutions, Inc software) My Sweet One (Phish song) -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4007153.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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 -- For unsubscribe instructions e-mail to: users+h...@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
[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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
My research has convinced me that I do not have neither the time nor the resources to set up and maintain a database. I might consider it if all of the data were received in the same layout, but the layout is as varied as are the sources. Since I am the only one that is using the data, a spreadsheet serves me best as I am able to freely add, delete, and modify the layout, format, calculations, etc. Something a for which a database has never been designed. If I had many collaborators to work on the project, perhaps a database may be of more use, but since I do not, I do not need the added headache of trying to setup and maintain an additional level of complexity. This is the conclusion I have drawn based upon the available documentation on the internet and conversations with people that have worked with their company's database. They all say it does not work until the processes are resolved to the point that they need not change. One person explained the problems that occurred when their processes did require a major modification. After weeks of attempting to make the required modifications, it was decided to set up another database. Then they went through months of consultation and headache to get the essential data transferred from the old database into the new one. A database is a good tool to keep track of large amounts of data and tracking processes, but unless it is a big company that has unchanging processes, a database may be more of a liability than a help. With my data constantly evolving, a database would need to be modified on a regular basis and I am unwilling to invest the extra time to do so. All my data fits on a spreadsheet and is easily modified as the sources are added or deleted as each source has a different idea as to how the data should be presented. -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4006921.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
Am 12.09.2012 07:23, JAMES MAJESKI wrote: Two digit years have always been a problem. I always presume that the use two digit years was obsolete after the Y2K publicity, but bad habits continue. We are no longer in the era of eighty column punch cards, so there is no excuse for two digit years. A spreadsheet does not store 2-digit years nor 4 digits. All spreadsheets store numbers and nothing but numbers (or text). A date in a spreadsheet program is an integer day number (unless it is a string). Day zero is 1899-12-30, day 40,000 was 2009-07-06, today is day 41164. Today's So there can not be any problem with 2-digit years. Format the numbers to your liking so they show 2-digit years, 4-digit years or no year at all. But that will not change the cell value. If you really need to export your currently active sheet into plain text you can apply any format you want. Why csv? Which application do you try to exchange data with? -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
Am 12.09.2012 08:00, JAMES MAJESKI wrote: My research has convinced me that I do not have neither the time nor the resources to set up and maintain a database. I might consider it if all of the data were received in the same layout, but the layout is as varied as are the sources. Since I am the only one that is using the data, a spreadsheet serves me best as I am able to freely add, delete, and modify the layout, format, calculations, etc. Something a for which a database has never been designed. Well, then you don't heve the time nor the resources to set up and maintain a database in spreadsheets. The latter scenario is the nightmare that never stops. A CSV file is an excerpt from a database and you can use any tool where your are free to modify layout, format, calculations etc. But then you save back a modified layout and format with additional calculations. A database program lets you freely handle all data in any shape while maintaining the underlying data structures automatically for you. -- For unsubscribe instructions e-mail to: users+h...@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
Re: [libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
On 09/12/2012 01:23 AM, JAMES MAJESKI wrote: Two digit years have always been a problem. I always presume that the use two digit years was obsolete after the Y2K publicity, but bad habits continue. We are no longer in the era of eighty column punch cards, so there is no excuse for two digit years. ISO8601 is the international standard, so it is not ambiguous. In other formats, using four digit years and month names are not ambiguous no matter the element order. Any other formats require a time consuming examination for clues as to element order or an explanation from the source. My input data may be in any of the formats. Once I determine the order of the elements and resolved two digit years, I can easily convert to ISO8601. In a spreadsheet a date year is a display choice, the actual date is stored as a number relative to a 0 day. In a database, however, it depends on how the field is defined: text or Date/Datetime. AFAIK all Date/Datetime entries require a 4 digit year. The problem with a database is it may use a location specific order (Imm/dd/ for US or dd/mm/ otherwise) for entry and storage. -- Jay Lozier jsloz...@gmail.com -- For unsubscribe instructions e-mail to: users+h...@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
Re: [libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
Hi :) I think all these tools require skill and experience. It's easiest to keep using the tool you have most skills and experience in but at the same time it is a good idea to try to build-up experience with other tools. If Andreas was working with your data then a database-program would be better and faster. If you had his level of skill and experience with Base and your level with Calc/Excel then you would probably find that Base was far, far easier and faster than Calc/Excel for this particular task. It's beyond the scope of this list to set-up a database for you but i suspect that if Andreas or Alex or someone did then you would find that easier to work with than the spreadsheet system you are using at the moment. Something i would quite like to see at some point in the future is people on this list getting paid work on a consultancy basis for odd one-off tasks, perhaps not even at such high rates as consultants might normally get. There are huge philosophical and practical problems around that sort of thing though even though it's allowed within the scope of the GPL and MPL license agreements. If someone approached me off-list i would feel obliged to point them towards someone with more skill and knowledge such as Jay, Regina, Dan and the plethora of other people we see giving great answers week in week out. If the request was made on-list then i thik we could figure out who would be best for a particular task, given time constraints and personal work-loads. Regards from Tom :) From: JAMES MAJESKI jamesmaje...@gmail.com To: users@global.libreoffice.org Sent: Wednesday, 12 September 2012, 7:00 Subject: [libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets My research has convinced me that I do not have neither the time nor the resources to set up and maintain a database. I might consider it if all of the data were received in the same layout, but the layout is as varied as are the sources. Since I am the only one that is using the data, a spreadsheet serves me best as I am able to freely add, delete, and modify the layout, format, calculations, etc. Something a for which a database has never been designed. If I had many collaborators to work on the project, perhaps a database may be of more use, but since I do not, I do not need the added headache of trying to setup and maintain an additional level of complexity. This is the conclusion I have drawn based upon the available documentation on the internet and conversations with people that have worked with their company's database. They all say it does not work until the processes are resolved to the point that they need not change. One person explained the problems that occurred when their processes did require a major modification. After weeks of attempting to make the required modifications, it was decided to set up another database. Then they went through months of consultation and headache to get the essential data transferred from the old database into the new one. A database is a good tool to keep track of large amounts of data and tracking processes, but unless it is a big company that has unchanging processes, a database may be more of a liability than a help. With my data constantly evolving, a database would need to be modified on a regular basis and I am unwilling to invest the extra time to do so. All my data fits on a spreadsheet and is easily modified as the sources are added or deleted as each source has a different idea as to how the data should be presented. -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4006921.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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 -- For unsubscribe instructions e-mail to: users+h...@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
Re: [libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
On 09/12/2012 02:00 AM, JAMES MAJESKI wrote: My research has convinced me that I do not have neither the time nor the resources to set up and maintain a database. I might consider it if all of the data were received in the same layout, but the layout is as varied as are the sources. Since I am the only one that is using the data, a spreadsheet serves me best as I am able to freely add, delete, and modify the layout, format, calculations, etc. Something a for which a database has never been designed. If I had many collaborators to work on the project, perhaps a database may be of more use, but since I do not, I do not need the added headache of trying to setup and maintain an additional level of complexity. This is the conclusion I have drawn based upon the available documentation on the internet and conversations with people that have worked with their company's database. They all say it does not work until the processes are resolved to the point that they need not change. One person explained the problems that occurred when their processes did require a major modification. After weeks of attempting to make the required modifications, it was decided to set up another database. Then they went through months of consultation and headache to get the essential data transferred from the old database into the new one. A database is a good tool to keep track of large amounts of data and tracking processes, but unless it is a big company that has unchanging processes, a database may be more of a liability than a help. With my data constantly evolving, a database would need to be modified on a regular basis and I am unwilling to invest the extra time to do so. All my data fits on a spreadsheet and is easily modified as the sources are added or deleted as each source has a different idea as to how the data should be presented. -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4006921.html Sent from the Users mailing list archive at Nabble.com. Databases are very useful even for small projects because of the querying capabilities of a database. Base and Access (MSO) are both relational databases meaning that the data tables are related and queries can search for data in any table. Relational databases require that the tables be predefined and this can be a problem for data entry when the data is not received in the same format as the data tables. Another option for a database is a NoSQL database such as MongoDB or CouchDB (both are FOSS projects) where the database tables do not require a predefined layout and what may take multiple linked tables in a relational database can be done in one table. For example a book in a relational database may have multiple authors, formats (different ISBN numbers), and subjects. In a relational database you would have a table for the book (title, year, publisher), another for authors, another for ISBN numbers, and another for subjects. In a NoSQL database one could combine this information into one table. The data can be searched. Another problem is that often the MSO bundle does not include Access so a user who needs a database is either forced to find one or use Excel as a substitute. -- Jay Lozier jsloz...@gmail.com -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
I looked into it and decided against it because what I am doing requires an intermediate spreadsheet in most cases. Since I now have many different ways to import the data so that it is correctly formatted, the extension would probably never be used. Detect special numbers and paste special unformatted work towards that end making any extension superfluous. I also have a few other tips and tricks to try that may save me a step or two. Your suggested extension would be useful if there were a need to convert formats, but my problem was importing data correctly. Thank you for your suggestion and if the need ever arises I may reconsider. -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4007049.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
As I said before, getting the date into a spreadsheet as a date was the problem. Once there, I am able to format the data as required. CSV was just an example. I get the data in just about any format (raw (unformatted), comma delimited, tab delimited, xls, xlsx, doc, docx, txt, pdf, etc. Even with the xls I sometimes get dates and numbers in text format. If all the sources would be willing to enter their data into a database, then a database would be useful as the data would then be of a fixed layout with a fixed structure. -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4007127.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
Name one feature that makes a database better than a spreadsheet when flexibility of input is required. -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4007128.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
The problem is that I see no advantage in a database. I do see many disadvantages. For what I am doing, a database is not an option. -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4007129.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
Once I have the data imported into the spreadsheet, there are no problems with dates. It was the importation of the dates that caused the problem. As for date formats, I prefer ISO8601 as I was using that format in the sixties and I never stopped. I can accommodate any *unambiguous* date and time format. It is when the dates and time format becomes ambiguous that I have problems. I found a definition for 'AFAIK'; All Fans Are Inane Koalas. The other definition I found is not fit to print. -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4007131.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
Re: [libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
Hi :) Sometimes on this list we bully, cajole or otherwise try to push people into using tools they are not familiar with. Even if it's a better tool for the task, that doesn't always make it better for the person's work-flow. There is only one right way of doing things and that's your own. Just as mine is for me (although i often think my neighbour's way is better - until i try it and then realise both were wrong and then regret changing and wish everything was back the way it was before i messed it up trying to copy someone else's style, but hopefully that's just me). Regards from Tom :) --- On Wed, 12/9/12, JAMES MAJESKI jamesmaje...@gmail.com wrote: From: JAMES MAJESKI jamesmaje...@gmail.com Subject: [libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets To: users@global.libreoffice.org Date: Wednesday, 12 September, 2012, 23:57 The problem is that I see no advantage in a database. I do see many disadvantages. For what I am doing, a database is not an option. -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4007129.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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 -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
My only queries of the data are handled adequately by the calculations done in adjacent columns. Nothing special, complex, or complicated will easily replace it nor be more useful. I can see how a database between libraries would benefit all the libraries that subscribe to the common database, but my requirements are so simple that a simple spreadsheet does everything I need. You mentioned MSO so I looked it up. The following are the results of that search: macro saccadic oscillation Mail Stream Optimization Main Street Office Maintenance Spare Optimization Maintenance Stores Office Maintenance Support Office Major Sales Opportunities Major Service Outage Major Sponsoring Organization Managed Services Organization Management Services Officer Manager of Search Operations Manufacturers Statement of Origin Manufacturing Shop Order map support office (US DoD) Maplewood/South Orange (New Jersey) marine safety office(r) (US DoD) Marine Survey Office Mariposa Symphony Orchestra (Mariposa, CA) Maritime Security Operations (US) Marketing Sales Office Marketplace Services Organization (Canada) Martha Stewart Living Omnimedia Inc (NYSE) Master Security Officer Material Status Officer McKeesport Symphony Orchestra (McKeesport, PA) Medical Second Opinion Medical Service Organization Medical Stores Organization Medical Support Order (health care) Mees Solar Observatory (Maui, Hawaii) Melbourne Symphony Orchestra Methadone Support Organization Methionine Sulfone (code for modified amino acid) Methylated Seed Oil Mexican Spotted Owl Microsoft Office Microsoft Outlook Mid-Atlantic Symphony Orchestra (Ocean City, MD) Middlesex Sheriff's Office (Medford, MA) Midland Symphony Orchestra (Midland, Michigan) Migrant Student Organization (various organizations) Military Systems Organization (US DoD) Military Satellite Officer Military Service Obligation Military Supply Officer MILSATCOM Systems Office Milwaukee Symphony Orchestra (Milwaukee, WI) Mind Sports Olympiad Mind Sports Organization (est. 1997; Canada) Minesweeper, Ocean (Non-Magnetic) Minor Service Order Mission Safety Officer Mission Space Objects Mission Staging Operation(s) Mission Support Officer Missoula, MT, USA - Missoula International (MSO Airport Code) Mixed Signal Option (Teradyne testers) Mixed Signal Oscilloscope Mobile Switch Office Mobilization Staff Officer (US DoD) Molecular Spin Orbital Molten Salt Oxidation Monadic Second-Order Logic Montessori School of Ojai (Ojai, CA) Montreal Symphony Orchestra Morale Support Officer Most Serious Offense (criminal justice) Most/Main/More Significant Other (Polyamory) Moving Swiftly On MSE Systems Overhaul Multi-System Operator(s) Multi-Service Operation Multi-cultural Student Organization Multimedia Service Operator Multiple Service Operator Multiple Subscriber Organization multiple system operation(s) Multiple System Operator Multi-system Operator (as in satellite/cable TV networks) Murray Symphony Orchestra (Murray, UT) Muslims Speak Out Must Start On (project management) Mustard Seed Oil Mutual Service Office (insurance) My Simple Office (Simple Office Solutions, Inc software) My Sweet One (Phish song) -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4007153.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
So far nobody has come up with a reason for me to switch to a database where I risk loosing data if I attempt to make a change to the layout. -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4007154.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
Two digit years have always been a problem. I always presume that the use two digit years was obsolete after the Y2K publicity, but bad habits continue. We are no longer in the era of eighty column punch cards, so there is no excuse for two digit years. ISO8601 is the international standard, so it is not ambiguous. In other formats, using four digit years and month names are not ambiguous no matter the element order. Any other formats require a time consuming examination for clues as to element order or an explanation from the source. My input data may be in any of the formats. Once I determine the order of the elements and resolved two digit years, I can easily convert to ISO8601. -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4006914.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
The following is a reply to my query from a private source: Data bases are for fixed data columns and standardized input methods and require manipulation by sql type query tools. Unless you are getting automatic feeds of significant amounts of data from your world wide operations I don't see what it would do for you. If you are putting together a business then a data base would be a good thing even if it is simple, but those are available pre-canned and some are open source. Unless you are doing something far more complex than I imagine spread sheets should serve you well and spread sheets have reporting capabilities. In reality, the reason just about every company went to data bases was that it centralized and standardized the data it needed and made it available to each different level of management in order that they could do their jobs and analyze their results. The complexity of data bases arises from how data is loaded and validated and the number of users, which is generally every employee of a corporation. Big companies have petabytes or more of data to manage and parallel and redundant servers are required to ensure business continuity. The other use for data bases is like firefox which has it's own mysql database built in to manage stuff like cookies and cache and run under control of the firefox program. Regardless, a lot of small companies keep some data on incomplete data bases that they got free or cheap and accumulate, compile, analyze and report their data from spread sheets for which they either write macros or use the spread sheet's native reporting tools. To demonstrate the similarities check out this video tutorial. http://www.youtube.com/watch?v=yO2oQhinZ4Y What it implies of course is that if you have many spread sheets and they are formatted in such a way as to correlate inter-relationships you could import them all into your data base and manipulate that data into a report. Fancy data base features include data entry windows for individual contributors and complex reporting engines that can generate charts and graphs as well as specific answers to pre-determined questions. -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4006603.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
Am 10.09.2012 01:49, JAMES MAJESKI wrote: You are correct in that 12/31/12 is not ambiguous, but that is a special case. When each element is unique and without knowing the source preference, there are six different dates that may be generated from 10/11/12: 2010-11-12 2010-12-11 2011-10-12 2011-12-10 2012-10-11 2012-11-10 Your input data have 4 digit years. Before exporting csv from a spreadsheet you have to take care of unambiguous number formatting. Again, there are much better applications to load, edit and save csv. The Base component can be used as import configurator for pre-formatted spreadsheet templates, some spreadsheet geeks prefer macro programming. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
Am 10.09.2012 18:56, JAMES MAJESKI wrote: The following is a reply to my query from a private source: Data bases are for fixed data columns and standardized input methods and require manipulation by sql type query tools. Unless you are getting automatic feeds of significant amounts of data from your world wide operations I don't see what it would do for you. If you are putting together a business then a data base would be a good thing even if it is simple, but those are available pre-canned and some are open source. Unless you are doing something far more complex than I imagine spread sheets should serve you well and spread sheets have reporting capabilities. You want a standardized input method to generate text files with ISO dates. Unlike its users, a spreadsheet does not care about formatting attributes. The only thing that really counts is the cell value. Databases are designed to do what most of today's spreadsheet users try so desparately try to do in spreadsheets. Databases are ubiquitous online and offline. Valid CSV files are exported from databases and they are destined to be imported back into other databases. Each row represents a record, each record has the same amount of fields, each field has a distinct data type. A spreadsheet has no records nor fields and the only data types are text, number and boolean (in Calc even the booleans are numbers). There are various ways to load csv into a spreadsheet so you can process the imported data by means of spreadsheet formulas. Simple statistics, projections, what-if-scenarios are the most typical spreadsheet applications based on database data. You pull it into a sheet and let the spreadsheet do what spreadsheets use to do (which is not text editing). Base is our database component. It is very, very underdeveloped, nevertheless underestimated. Even the worst database tool does a better database job than the best spreadsheet can do. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
James CT2N is a Libre extension, not a file extension http://extensions.libreoffice.org/extension-center?getCategories=getCompatibility=anysort_on=positive_ratingspath=%2FLibreOffice-Extensions-and-Templates%2Fextension-centerportal_type=PSCProjectSearchableText=CT2N Load this into the Libre Extensions Manager in the Calc Tools menu A new icon will appear in your tool bar. Click on it to convert highlighted cells, or whole sheet. Tink. -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4006635.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
Use the extension CT2N. It Converts Text to Numbers. Simples. Tink. -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4006356.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
Am 09.09.2012 11:54, Tinkerer wrote: Use the extension CT2N. It Converts Text to Numbers. Simples. Tink. Nobody needs to install any extension to convert between numbers and text. CT2N may fail in this situation (dmy vs. mdy). If all the dates have been imported as text, a simple regex replacement converts in both directions. It would be better to simply import correct values instead of fixing wrongly imported ones. James knows how to import correct values but he expects automagic number formatting. The program should analyse the assumed formatting of the input strings and apply the corresponding number format codes to the respective target cells. Spreadsheets don't do that. Formatting has zero relevance for the tasks spreadsheets are designed for. Only values (numbers) are relevant. James Majeske wrote: I receive dates in three different formats; big endian [31 Dec 2012], mixed endian [Dec 31, 2012], and small endian [2012-12-31] (also known as ISO8601). With US loale setting and detect special numbers any of the above input strings yield cell value 41274 preformatted to show 41274 as 12/31/12 which is obviously and unambiguously the correct value. This value can be formatted at will and sorted by its numeric value. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
In a spreadsheet I get all the information I require and it is easy to get additional information if needed. I can add, modify, and delete headers both horizontally and vertically. I can add, modify, or delete cells, rows, or columns at will. As long as I leave the original data alone, I can add as many calculated columns or rows as I wish. One of my spreadsheets is a complete double entry bookkeeping system. I can use split or freeze to keep the column and row titles visible while scrolling through the data of the various accounts. According to what I read last night, you must be very careful or you may compromise essential data. The implication was that it would be easier and safer to create a new database than to modify it. From the https://wiki.documentfoundation.org/Documentation/Publications documentation : Fields can be added or deleted, but adding a field requires you to enter the data for that one field for every existing record with an entry for that field. Deleting a field deletes all the data once contained in that field. Changing the field type of a field can lead to data being lost either partially or completely. Deleting a table removes all of the data contained in every field of the table. I will continue reading, but so far it seems that a spreadsheet has all the advantages and none of the disadvantages of a database. My knowledge of databases is very limited. If there is any advantage in using a database over a spreadsheet, please share it with me. -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4006428.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
When I change the file extension (file_name.ct2n), I find no difference in how the file is imported into a spreadsheet. -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4006429.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
With the advice given earlier, I have found several methods that do what I need to get done with no problems. My previous lack of knowledge was easily corrected and I am confident in my ability to adapt the input to conform to the requirements of its intended function. Quote: [With US loale setting and detect special numbers any of the above input strings yield cell value 41274 preformatted to show 41274 as 12/31/12 which is obviously and unambiguously the correct value. This value can be formatted at will and sorted by its numeric value.] You are correct in that 12/31/12 is not ambiguous, but that is a special case. When each element is unique and without knowing the source preference, there are six different dates that may be generated from 10/11/12: 2010-11-12 2010-12-11 2011-10-12 2011-12-10 2012-10-11 2012-11-10 This is why, when not using ISO8601, I choose to use a four digit year AND a month name. No matter the order of the elements, there can be no ambiguity. -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4006430.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
Re: [libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets
On 09/09/2012 06:51 PM, JAMES MAJESKI wrote: In a spreadsheet I get all the information I require and it is easy to get additional information if needed. I can add, modify, and delete headers both horizontally and vertically. I can add, modify, or delete cells, rows, or columns at will. As long as I leave the original data alone, I can add as many calculated columns or rows as I wish. One of my spreadsheets is a complete double entry bookkeeping system. I can use split or freeze to keep the column and row titles visible while scrolling through the data of the various accounts. According to what I read last night, you must be very careful or you may compromise essential data. The implication was that it would be easier and safer to create a new database than to modify it. From the https://wiki.documentfoundation.org/Documentation/Publications documentation : Fields can be added or deleted, but adding a field requires you to enter the data for that one field for every existing record with an entry for that field. Deleting a field deletes all the data once contained in that field. Changing the field type of a field can lead to data being lost either partially or completely. Deleting a table removes all of the data contained in every field of the table. I will continue reading, but so far it seems that a spreadsheet has all the advantages and none of the disadvantages of a database. My knowledge of databases is very limited. If there is any advantage in using a database over a spreadsheet, please share it with me It seems to me that there ought to be advantages to using databases. After all, there are college courses of databases. Oracle has a database program (better known as a database management system that is very complex). The text book for it is 700+ pages. This is used in many of the large businesses. This textbook is known to give students headaches. So, is it possible that the things you see as advantages and disadvantages just might not be the whole story? The following is the link to the draft folder for Base. You can download the database use with Chapter 1 of the Base Guide. There is also a link to the database (Budget.odb) used for Chapters 2-4 of the Base Guide. Perhaps the latter could show the advantages of using a database instead of a spreadsheet. http://www.odfauthors.org/libreoffice/english/base-guide/draft-lo3.4 In the past, I have used a spreadsheet to keep track of my finances. Now I use a database which I prefer. Why? Size is one of the reasons. Each sheet get physically larger as data is entered, a database does not. Spreadsheet: data is entered into individual sheets based upon the specific data. Database: the entries are made in a single form. Spreadsheet: data must be linked from one sheet to another (I had links between data with a sheet). Database: This is done using table and field names. To me, one of many advantages of a database is the query. You can tell it to get data from specific fields, and it will display it in a table format. This does not take up physical space like it would in a spreadsheet. Another is what can be done with the simple sum function in a query. For example, my financial database has a field named, Amount. In this field I enter all of the expenses and income that I have. I create the query to find the sum for the Amount field. I also tell the query to separate this sum so that I get a subtotal for each of my Accounts (I have 9). So I now have 9 subtotals of the Amount field. I also tell the query to list the names in the Accounts field alphabetically. In the end, the query's output is 2 columns by 9 columns: first column contains the names of the accounts listed alphabetically, and the second column contains the balance for each account listed. This is the SQL statement for the query in Budget.odb (it only has 5 accounts): SELECT Account, SUM( Amount ) AS Account Balance FROM Data WHERE Account IS NOT NULL GROUP BY Account ORDER BY Account This is the output: Account Account Balance Bank of America -600 Bank of Tennessee -541 Cash 20.48 Wamu MasterCard 649.84 Wells Fargo VISA 218.45 I use the same structure for the balances of my accounts. The only difference is that in Budget.odb, the Account field has 5 entries (hence 5 rows of subtotals) whereas mine has 9 entries (hence 9 rows of subtotals). It just occurred to me another advantage: the Data Source window which you open in Writer or Calc using the F4 key. You can see the query output without opening the database. --Dan -- For unsubscribe instructions e-mail to: users+h...@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