Hello, Helpful People This is a rather long explanation/question, but I wanted to include as much info as I can so you’d know exactly where I am with this project and the problem I have.
I’m a digi-dinosaur. I started in the 1970’s with punch cards and when I retired in 2005, I had been a micro-computer bench tech, a LAN designer and administrator, an interpreter between users and programmers, and a Director of Info Services and Computing for a 400-bed hospital. All that is to say I can comprehend what you’re saying and you don’t have to start with “turn on the computer”. I know when to ask questions.
I am using LO Base 24.8. I have the version 25.0 msi, but don’t want to upgrade until I get this db for info on some audio books created. I have three tables: Authors, Series, and Books. I have three one to many relationships: Authors - Series, Authors - Books, and Series - Books.
I now want to populate the table Authors from a LO Calc spreadsheet. The names of the columns in the spreadsheet and the names of the fields in the Authors table are exactly the same. Setting the column/cell types in the spreadsheet doesn’t really correspond with the field types in the table, but I got them as close as possible.
The Ask LO member skyanderson got me past not being able to use the copy option in the table menu. But, simple copying wouldn’t work. I kept getting “null value” errors. I read a dozen different pieces of advice on this problem and could get nothing to work.
I have been able to create a Authors Input table from the spreadsheet directly. The table is populated and just what I want, though I had to add the AuthorID key field to the table after it was created.
Now I’d like to copy that table to my db. I named the newly created table Authors Input so that I would not have a problem with like named tables in my db table field. As suggested by others, I tried opening both databases and dragging the table from one to the other. That didn’t work. I have tried a number of other suggestions, but none have worked.
I’m so close to having the db I want that I can taste it, but I can’t seem to get beyond this issue of populating my tables in a bulk action. The data is not massive, just a few dozen records, but I don’t want to have to enter all that data record by record. And, I shouldn’t have to. BTW, yes, I have read the manual and users guide. I didn’t find it very helpful on the subject of populating a data table. If I’ve missed something there, please advise me. Do you recommend I turn to a .cvs file method? I’ve used that for spreadsheets and text files, but not for db tables.
I am open to any and all suggestions.
You don’t tell anything about your database nor the actual error message.
create_table.ots (41.3 KB)
Open the template.
Save it to some place where embedded macros are allowed.
Copy your data, including the column labels.
Paste-special values to A1 of the first sheet.
Push the button on the second sheet.
Follow the instructions. Choose a database type, provide a table name, name of any additional primary key (auto-ID), the data types of each column, default values, nullable or not.
The spreadsheet gives some hints why the import may fail.
After fixing your data, copy the CREATE TABLE command from A36, run the command, paste the data from the forst sheet and append them to the new table.
SQL is a language of the 70ies. It did not change very much since then.
For the manual way I either mark all data in Calc (Ctrl-A or by manal selection of the needed range), Copy to Clipboard (Ctrl-C), then select Paste from the context-menu (right click) of the previously created table in the database. An assistant is opening, wich allows re-orgenizing columns etc., wich I don’t need - but it is a visual conformation to use a fitting table-layout. Works for me since years.
.
Some Problems:
Spreadsheet-formulas often use IF ... ; """; ... to show “nothing” but this lines are not empty and will be copied also, if you use Ctrl-A. And this lines may be in conflict with your table-definitions.
.
If you provide Id-fields, they have to be UNIQUE over the table. If you don’t provide them (column empty) you will need a working autoincrement (in your table definition), wich provides the necessary contents.
.
Relations can cause trouble and require proper sequence when adding data. I guess you will need to fill the authors table, before you can add a book, as the books table will reference author-ids. Sometimes it is useful/necessary to remove a relation to populate tables via imports.
Actually I never use drag’n’drop here, but only copy and paste of complete rows, when a database is the source. With spreadsheet as source ranges are possible too. So I copy records, not entire tables.
.
Also I prefer to copy from /to calc, not between databases directly. It seems easier to get the right match of types.
Not helpful unless your database has a good way to import from there directly. (And it seems most databases tried to implement this differently). Can be useful, if you wish to script something like repeating imports from other databases.
An exact error message copied from the dialog window would help.
And the database type connected to your Base document as displayed in the status bar of the Base document window.
The spreadsheet template I’ve posted is far from perfect. However, it should help to track down common problems with spreadsheet data. It generates a CREATE TABLE statement creating a table that fits your current data on the first sheet.
Spreadsheet data:
- may have blanks where the database column may not allow any Null values.
- can have 2 different types of data, double and text, with error values as a third type returned by formulas. Databases have many more simple data types representing decimals and integers of different scales, dates, times, text of different scale, case-sensitive and insenstitve.
- tend to have different types of data in the same column which may or may not convert to the type of the respective database column.
- have no concept of tables, records nor fields. Any list-shaped rectangle of cells may follow normalization rules – or not.
- may contain foreign key data with no valid reference to the primary key of some other table. Databases do not accept data violating referencial integrity, for instance an invoice record with Client-ID 9999 when there is no client with that ID number in the clients table.
Usually, there is nothing to be done on the database side of the problem. You need to fix your spreadsheet data, so every single row in that cell range is compatible with the rules implied by your database engine.
Here are a few things to try immediately following the “Paste” option when importing a spreadsheet to LO base “Tables”.
- If the new table exists, backup by renaming it, or delete it! Then save, close and reopen the odb.
- While pasting the spreadsheet data, in the “Copy table” pop-up dialog, enter the new Table name, then select “Definition and data” and “Use first line as …” options only. (Do not create primary key)
- Click “Create” (not “Next”) and Do Not create a unique index or primary key when prompted! All columns in the resulting table should now have type “Text(varchar)” which do not evaluate null values. Remember to save all changes! (ctrl+s)
- Right click the new Table in “Tables” and Edit. The field types will need to be reassigned to their proper type. If the new Table has an ID or primary index field, it must be type INTEGER, and then right click just to the left of the ID field and set as “Primary Key”. A little key icon should appear next to it. (On error see last Bullitt point)
This will work fine in HSQLDB embedded context… otherwise you may have to apply these changes using the LO menu item - Tools/SQL… (Tons of help on using the SQL command tool at Ask Libre. The main command to search is “ALTER TABLE”, field type) - Remember after making any changes, complete all save prompts with “Save”, or just Ctrl+s! Sometimes the Save icon does not fire properly with first click! In such case at least two (2) more repeated clicks are necessary before the odb is actually saved! (bug?) This is why I prefer to Ctrl+s.
- If data type errors occur while editing field types, check the integrity of the data. For example, the primary index field must have unique INTEGERS. Make sure all of these are true INTEGERS, not,for example “01” or a “1” preceded or followed by a hidden space " ". Use the SQL command tool to “clean” this up by using the appropriate TRIM() method, or manually by hand, before changing the primary key field (ID) from Text(varchar) to INTEGER. If this fails it may be necessary to fix these issues in the source spreadsheet before importing to the odb! Good luck!