@Wanderer, are .ODS files actually able to support more information than .XLSX files?
Actually my example mentioned xls, wich supports less lines and columns.
.
xlsx and ods are both comparable, but different. Problems can arise using advanced Features with both standards. Imhof the bigger issue ist MS advertising Standard but silently using non-standard Elements (you can prevent this, but most xlsx out there are not set to obey standard only.
2 Likes
Apologies, @Wanderer. I had not realized that XLS existed as a format, so I expected that you had mistyped XLSX.
Indeed, Office supports some non-standard elements, but the user is forced to choose whether they prefer compatibility or functionality when the suite is first installed. I do not believe that adding optional functionality is necessarily bad: standardization necessitates some previous experimentation.
1 Like
File format does not matter. When you have something in a Calc window then it is an Open Document Spreadsheet. Any xls(x), csv, dbf, whatever has been converted into an Open Document Spreadsheet. And when you save it back to disk, it is converted back into xls, csv, dbf, whatever.
A Calc spreadsheet has no more than 3 data types: text, floating point numbers and error values. There are no dates, times, booleans, fixed decimals. Anything looking like that is either a text or a formatted number.
A database has a dozend data types. When you drag a record set into a sheet, is comparatively simple to convert any database type into either text or number and every single sheet cell accepts any of them.
The other way round is way more difficult. If the text or the number can’t be converted into the right column type, you get a load of errors messages.
Never mess with the structure of a working database.
Never edit a dBase file in Calc. Always use Base to edit dBase. Otherwise the dBase file may become unusable for the original database application. MS Excel opens dBase files read-only.
Before trying to paste sheet data into a database, you have to make sure that the database structure accepts every single row.
– Every column value must match the column type of the database. This affects numeric scales, text lengths, valid or invalid dates, times, booleans etc.
– Every record must be complete. No missing values in mandatory database columns.
– Every foreign key must have a matching entry in the referenced table in order to keep referencial integrity. A well made database rejects any client ID in a table of invoices where that client ID does not exist in the referenced table of clients.
A spreadsheet does not care about vaidity and integrity. A useful and fully functional sheet may not even contain any structure that could be identified as a “table”.
A tutorial about database data in Calc: [Tutorial] Using registered datasources in Calc
How to analyse a table on a sheet before trying a copy/paste into Calc: Unable to change Key ID - #4 by Villeroy
1 Like