Apache OpenOffice Community Forum

So ... I have a spreadsheet, and I have a new database with a table containing a primary key plus a column for each column in the spreadsheet. Following the Helps, I select and copy the information I want to import from Calc into Data, then go to the table and select Edit > Paste

No sign of any dialogue, just an attempt to paste all (several hundred) spreadsheet cells into the first field of the first record. Is this enough to help someone tell me what I'm doing wrong?

Thanks in advance.

What OS and, what OOo version?

Register your database (Tools>Options...Base>Databases).
In Calc hit F4 and drag the range selection onto the "Tables" icon (no, not a particular table unfortunately) or try a normal copy&paste
You can also drag the range onto the tables icon in a database window without registration.

The import wizard pops up where you specify the name of the target table and that you want to append data.
If your input data have the right order of columns, that's all. If you want to reorder or skip columns, do so.
In OOo3.2 you can drag data without column label, in previous verions a column label is always assumed.
When some error message pops up due to invalid/incomplete/duplicate rows (quite likely with spreadsheet data) you can tell it to continue the import which will skip rows.

A set of formulas I use at the bottom of an import range: X2 =countif(P$2:P$59645;$P2) counts occurences of this rows value in P in the entire column. Should be 1 for all cells in a column of unique values (but mind the horrible Calc options when you try to count text).

I'm cursed ... software hates me ... sniff

Yes, I selected the content in Calc, hit F4 and dragged it on to the Tables icon that suddenly appeared. The import wizard appeared, as you'd said it would, and I set up the columns ‒ nothing fancy, just replacing spaces in column names with underscores and shortening the default length on a few var(char) columns.

"Insert statement too long!" said the wizard, without farther explanation. But I clicked on [Continue] anyway, and then tried to look at the data I'd just imported. When the cursor is actually in a field, then I can see just one field's worth of data, white on blue; otherwise, all I see is an empty grid. Queries that ought to return twenty or thirty entries return nothing.

Might just go back to MySQLAdmin, but I had been looking forward to integration and OpenOffice.Base :-{

Just one minute ago I finished a lengthy description of the process in the old forum: http://www.oooforum.org/forum/viewtopic ... 218#368218
As always, this is a question of data processing by means of a computer. It is completely impossible to help without the faintest idea about the data you are trying to move from a non-structure (heap of sheet cells) into the fields of a database.
Calc knows only 2 different types of columns: number or text (there are no dates nor booleans actually)
A database has some dozend of types to offer, some with different lenghts.

Using MySQLAdmin you could import the spreadsheet data from plain text(csv) facing the same problems when the data do not match the structure of your database.

I'll take a look at your new posting. However, I'm still disappointed that integration between two components of the same suite seems a little poor. It ought to be possible to tell Base "import the content of this spreadsheet, using column headers as field names and cell formats as data types", so I can then tidy things up by hand. I do have a fair idea of consistent structure; the only thing I can't tell is how the import will handle empty cells.

When Base complains "Insert statement too long!" after creating a statement itself ... this is not encouraging!

Yes, I can quite happily handle queries and exports in MySQLAdmin: but I wanted to benefit from the more user-friendly forms interface that Base seems to offer.

Pushing the round thing into the square can not be easy. Almost every "organically grown" spreadsheet with collections of data is inconsistent. A spreadsheet has number, error and text as the only 3 types of data and each single cell can take any type independent from the column or row. There are no fields, not even tables since any rectangle of cells may be organized like a table -- or not. This "anarchy" is quite the opposite of a database.
Base data do import and link easily from a database to the spreadsheet as long as the column types are numeric or textual (no binary data). Calc has no problem with the strict table design of a row set.

It ought to be possible to tell Base "import the content of this spreadsheet, using column headers as field names and cell formats as data types", so I can then tidy things up by hand.

No, you can not tidy things by hand since no database accepts untidy data. You've got to do this in a spreadsheet and I provided a set of formulas and an add-on to help you with that.

Assuming the columns in the spreadsheet match the table in the database:
1. In Calc, select the rows in the sheet and copy (Ctrl C)
2. In Database, select Tables (left top), then the table you want to copy into, to highlight it and right click to show options.
3. Select Paste and left click.

The following functions/formulas test spreadsheet data for compliance with a database structure, avoiding failures or even worse, incomplete insertion of record sets.
=COUNT(col) counts the numbers in a column.
=ROWS(col)-COUNT(col) returns 0 if a column is full of numbers.
=COUNTA(col) counts all values in a column, including (empty) strings.
=COUNTA(col)-COUNT(col) counts strings, including empty strings.
=COUNTBLANK(col) counts empty values, not including empty strings.
=MIN/MAX(col) test the value range of numeric columns.
Array formula =MAX(LEN(col)) counts the max. length within a column.
=ISNA(MATCH(FK;PKs,0)) tests if a foreign key value does not exist in a list of primary keys. Any TRUE (1) value indicates that the foreign key FK is not in the list of foreign keys (PKs). If the sum of the calculated column having this formula is zero, all values in the column are having a corresponding value in PKs.
=IF(ISBLANK(val);0;COUNTIF(col;val)-1) returns 0 if a value is unique within its column ignoring blanks. Any number above 0 counts duplicates. If the sum of the calculated column having this formula is zero, all values in col are unique.