Dear all,
I’m not able to translate the .ods or .xlsx formulas into SQL. This is why
I found
which theoretically converts .xlsx to access-format file. As Libreoffice can convert .xlsx to .ods and reverse, I suppose that Libreoffice can also convert access-format to .odb and reverse.
Is the link above fake or am I unable to use it?
I also found
But I don’t understand the answers.
You can not simply convert the files. And SQL is not an Access-DB.
.
Usually SQL-Databases can be created by commands in SQL. So your first link tries to generate a series of commands, wich would create a database-table with the same contents as your spreadsheet, when you feed the commands to an appropiate database.
.
I usually define my tables directly in the database, then copy the data from a spreadsheet via clipboard to the table in Base, wich is assisted by an import-filter.
.
Why do you wish to convert the data?
.
Have you read the guide for Base, provided on LibreOffice.org at get help> documentation?
English documentation | LibreOffice Documentation - LibreOffice User Guides
Have you any idea, wich database you wish to use?
What is your target database engine?
What structure has your .ods file?
Thank you very much Wanderer for your answer.
“Why do you wish to convert the data?”
I have approx. 40 columns of approx 36,000 rows in my calc file. This file became too often too slow. I’ve been thinking that working with a .odb file would be faster.
“Have you read the guide for Base, provided on LibreOffice.org at get help> documentation?”
Thank you, I already read few times more than the half of it. I don’t find anywhere where you can write something like “=A4*A6/(B1+cos(B2))” for instance though I know it should be written in SQL.
“Have you any idea, wich database you wish to use?”
I’ve been thinking that I could copy my columns of my calc-file to a .odb database.
I’d suggest you provide a shortened copy of your Calc file as example (something like the first 5 to 10 lines.
.
At first: In a database you would NOT store derived values but create them in a query. Therefore you don’t find any formula in the table itself. So for typical examples you may store price and quantity in the table and you have a query
SELECT price, quantity, price*quantity AS total, (price*quantity)*0.1 as tax
When you need to work across rows things get complicated. So, if I assume your rows are one dataset a formula like
=A4*D4/(B4+cos(C4))
could be converted quickly to a database query, if the database has cos as a function. But your formula would be a problem. When you use a database you let the database decide where to store something, so you don’t know anything about next or previous row. Instead you have to ask the database in your query to do a sub-query, for example to retrieve the data from previous month to calculate the difference.
.
Depending on your needs it could turn out a combined approach is useful: SELECT a subset of data in a query and drag the result back in a smaller spreadsheet, to tackle aggregated data in spreadsheet.
The world of databases is much more complicated. For “embedded” databases your data is actually in the . odb but base offers two possibilities HSQLDB and Firebird. Also you can have your data elsewhere handled by a database-server like Postgres or MariaDB. The server can even be somewhere on the internet. Then the .odb has only the queries and reports, while your data is somewhere in the database. Split databases are to be preferred for stability to enbedded ones (for them I recommend frequent backups).
.
So while you can convert some/a lot of spreadsheets to databases you may need much more work to get something useful.
.
But: Yes a database is a much more stable source and “home” for data, after proper conversion and it will handle most requests quicker than Calc, because there is a fixed structure for the tables (you usually provide this), while Calc allows a scattered mixture of data, formula and decoration.
How can you easily convert a motorcycle into a truck? Both do transportation on wheels.
Thank you Regina, but I don’t understand your questions.
I began to copy my excel files in a database that you can see here:
Thank you for your attention.
Would you mind receiving more than 5 to 10 lines ?
Base works like Access or any other database frontend. You can connect many different types of databases to an Access document without converting a single byte of data. Then you build your own frontend around the connected database (forms and reports backed by queries and macros). In this respect Base is like Access. However, Base is just a tiny addition to this office suite, whereas Access is a multi-million Dollar database development kit.
I use this online converter and copy all my future tables sheet by sheet. It works just as well for spreadsheets or csv.
Thank you very much Primus. I used your website, and I got a .sql file (I should understand the 4 ways for obtaining an sql file).
If I understood Wanderer well. I need to apply this sql file to the data that I copied in a .odb file.
I don’t understand what is “NEW -You can now Save the complete data and settings, and then later Load them from your saved file.”
at the end of the webpage.
Could you explain me what it is please?
No, Base can not do this. There is no simple function within Libre Office to “Open this Access file in Base” or “Export this ODB file to Access.”
You can, however, either use a spreadsheet as a read only datasource for a ODB file, or copy your spreadsheet data into one or more separate tables in a previously created Base file, which may or may not contain previously defined table definitions.
Look at the Libre Office Base guide for more information on this topic, because there are a number of “gotchas” to be aware of.
Thank you very much iplaw67 for this piece of information.