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.