do I need db if xls files suffice for now lazarus / 16-Databases ---------------------------------------------------------------------- Weiss · 2024-01-29 08:11 I am developing software for tracking machinery performance from different work sites. This necessitates storing historical data, simple numeric information, not quite worthy an extra effort. Excel files suffice for now. But I really don’t know where it is going to lead in the future. And this bring about the question. Can excel files be “promoted” to database if needed? Learning databases will slow me for some time, can I get to it later, without creating too much of extra effort in the future? ---------------------------------------------------------------------- Thaddy · 2024-01-29 08:22 Well, it is already a kind of database, so yes. I wouldn't use xsl, but xslsx. ---------------------------------------------------------------------- Zvoni · 2024-01-29 08:26 Quote from: Weiss on January 29, 2024, 09:11:35 amI am developing software for tracking machinery performance from different work sites. This necessitates storing historical data, simple numeric information, not quite worthy an extra effort. Excel files suffice for now. But I really don’t know where it is going to lead in the future. And this bring about the question. Can excel files be “promoted” to database if needed? Learning databases will slow me for some time, can I get to it later, without creating too much of extra effort in the future? Yes, Excel-Files can be promoted to Database (MS Access even offers a direct Import-option) In terms of Excel a Workbook (The Excel-File itself) would be the Database, the Sheets would be the Tables, the Columns would be...well... the columns... :-) ---------------------------------------------------------------------- Thaddy · 2024-01-29 08:29 Not an import option msaccess can handle a xlsx file directly. ---------------------------------------------------------------------- Zvoni · 2024-01-29 08:34 Quote from: Thaddy on January 29, 2024, 09:29:37 amNot an import option msaccess can handle a xlsx file directly. IT IS an Option (since i used it in the past). You can either import an Excel-File into a Table, which makes it an Access-Table, or you can LINK to an Excel-file ("Federated table" in MySQL) ---------------------------------------------------------------------- Thaddy · 2024-01-29 08:39 No, it is on the driver level. You are confused. ---------------------------------------------------------------------- Zvoni · 2024-01-29 08:46 Quote from: Thaddy on January 29, 2024, 09:39:26 amNo, it is on the driver level. You are confused. Then so is Microsoft... https://support.microsoft.com/en-us/office/move-data-from-excel-to- access-90c35a40-bcc3-46d9-aa7f-4106f78850b4 QuoteOnce the data is in Access, you can delete the Excel data. ---------------------------------------------------------------------- Weiss · 2024-01-29 17:20 thank you fellas! This is what I thought, but wanted to verify. ---------------------------------------------------------------------- CharlyTango · 2024-01-30 18:38 Sorry guys but as soon as giants like Exel or Access come into play for such a simple task, my lifetime experience tells me: don't use them. I would use a simple SQLite database for this task, even if the learning curve is perhaps a little steeper. For Windows OS: the right sqlite3.dll access DLL (caution bitness 32/64 bit depending on OS and Lazarus) in the EXE directory and in the lazarus-exe directory and then work with Lazarus-SQLDB components. Add to this an external database manager and the thing will also run on other platforms later. You can also use Excel via ODBC for analyses, but for pure data collection I avoid third-party programs from large manufacturers wherever possible. ---------------------------------------------------------------------- Zvoni · 2024-01-30 18:58 Quote from: CharlyTango on January 30, 2024, 07:38:18 pmSorry guys but as soon as giants like Exel or Access come into play for such a simple task, my lifetime experience tells me: don't use them. I would use a simple SQLite database for this task, even if the learning curve is perhaps a little steeper. For Windows OS: the right sqlite3.dll access DLL (caution bitness 32/64 bit depending on OS and Lazarus) in the EXE directory and in the lazarus-exe directory and then work with Lazarus-SQLDB components. Add to this an external database manager and the thing will also run on other platforms later. You can also use Excel via ODBC for analyses, but for pure data collection I avoid third-party programs from large manufacturers wherever possible. The question wasn’t Access vs. SQLite, but if an Excel workbook can be promoted to a database. I mentioned Access only, because it has a direct import function for Excel files. I‘d go with SQLite over Access any day of the week, and even the days that are not part of a week ---------------------------------------------------------------------- MarkMLl · 2024-01-30 21:17 Quote from: Weiss on January 29, 2024, 09:11:35 amAnd this bring about the question. Can excel files be “promoted” to database if needed? Learning databases will slow me for some time, can I get to it later, without creating too much of extra effort in the future? Noting everybody else's replies, but I'd suggest that the sooner it were done the better. While superficially useful, spreadsheets can be a massive problem because of the way that they mix in code and data: in effect, you can't rely on any cell (or row, or column) containing an unmassaged value. TBH, there's a whole lot of crap written about databases. If you start off considering that a database contains one (or possibly more) tables, and that each table is basically a grid of rows and columns, and that you don't want to put anything into it that is ambiguous or duplicated, then you're already in a position stronger than if you were using a spreadsheet. Once you've got the basics in there, you can, if you want, add columns with either a computed initial value, or defaulting to NULL (which is a special case representing "no data", it does not mean the string "NULL"). Designate something, possibly a timestamp, as a key, and in conjunction with the site ID most of your searching and sorting problems are solved. MarkMLl ---------------------------------------------------------------------- Zvoni · 2024-01-31 09:30 Quote from: MarkMLl on January 30, 2024, 10:17:12 pmQuote from: Weiss on January 29, 2024, 09:11:35 amAnd this bring about the question. Can excel files be “promoted” to database if needed? Learning databases will slow me for some time, can I get to it later, without creating too much of extra effort in the future? Noting everybody else's replies, but I'd suggest that the sooner it were done the better. While superficially useful, spreadsheets can be a massive problem because of the way that they mix in code and data: in effect, you can't rely on any cell (or row, or column) containing an unmassaged value. TBH, there's a whole lot of crap written about databases. If you start off considering that a database contains one (or possibly more) tables, and that each table is basically a grid of rows and columns, and that you don't want to put anything into it that is ambiguous or duplicated, then you're already in a position stronger than if you were using a spreadsheet. Once you've got the basics in there, you can, if you want, add columns with either a computed initial value, or defaulting to NULL (which is a special case representing "no data", it does not mean the string "NULL"). Designate something, possibly a timestamp, as a key, and in conjunction with the site ID most of your searching and sorting problems are solved. MarkMLl I don't think starting out with a Spreadsheet (Excel, Calc, whatever) to start "learning" about Databases is wrong, especially if you don't have any experience with Databases beforehand (as TS stated). The Problem people have with "learning" about Databases is understanding Data-Structures and how they relate to each other. Once they understand how Data-Structures ("Records") represent what entity, then the jump to Primary Keys, Foreign Keys et al is not that giant leap anymore. Remember some 20-30 years ago those plastic boxes you could find on any office-Desktop, where you had the Business-cards of your Business- Partners (Clients, or in my case Suppliers, since i worked in Purchasing). This plastic box IS a Database (Note Present Tense!!), albeit with only one Table, and the Business-Cards were the Records. You "queried" that Database-Table by going through it, because you had the Cards sorted (hopefully) by some criteria (Company-Name, Last Name of the contact or whatever). Subconciously you even went through that sorted "table" in a binary search mode (Grab the first card from somewhere in the middle, "Ah...No...the Name starts with "M"....i'm looking for a name starting with "P"....let's grab the next card from the second half of the pile" and so on. With the advent of Excel some 25 years ago, those plastic boxes and Business-Cards got replaced with Spreadsheets (Excel), where suddenly you could use an Autofilter, where you could hit CTRL+F and search directly and so on Once you understand, that "Databases", "Data-Structures" etc. are all around you and how they interact with each other, THEN it's not Rocket Science to understand, that a VLOOKUP in Excel is actually something similiar to what a Primary Key/Foreign Key-Combination in a Database does (and yes, i'm leaving out Referential Integrity here!). And this is just one small example, why i think that Excel might be a good starting point for a DB-layman to start "learning" about Databases ---------------------------------------------------------------------- MarkMLl · 2024-01-31 10:26 I don't think this impacts on OP at the moment /but/... Quote from: Zvoni on January 31, 2024, 10:30:42 amRemember some 20-30 years ago those plastic boxes you could find on any office-Desktop, where you had the Business-cards of your Business-Partners (Clients, or in my case Suppliers, since i worked in Purchasing). This plastic box IS a Database (Note Present Tense!!), albeit with only one Table, and the Business-Cards were the Records. Yes, and I also remember roughly 25 years ago selecting PostgreSQL due to its competent support for transactions: something which ICL/Fujitsu overlooked hence the massive miscarriage of justice and associated political turmoil in the UK. Hence also my misgivings about various RAD's graphical database controls, which don't- in fact /can't/- "play nicely" with dropped connections https://github.com/MarkMLl/testdb ---------------------------------------------------------------------- Zvoni · 2024-01-31 10:48 Quote from: MarkMLl on January 31, 2024, 11:26:33 amI don't think this impacts on OP at the moment /but/...It might, it might not, but someone else might find this thread, so.... *shrug* QuoteYes, and I also remember roughly 25 years ago selecting PostgreSQL due to its competent support for transactions: something which ICL/Fujitsu overlooked hence the massive miscarriage of justice and associated political turmoil in the UK. Hence also my misgivings about various RAD's graphical database controls, which don't- in fact /can't/- "play nicely" with dropped connections https://github.com/MarkMLl/testdb Mark, my post was not about any specific DBMS (and in what year it was available), and whatever database controls play nice with it or not.. It was for people, never having anything to do with Databases, and for whom words like "PostgreSQL", "MySQL" etc. sound like gibberish. It was more about the concept, of what a Database CAN be, and that they are not even aware of using it