Ask Your Question

Base: import table preserving primary key

asked 2019-12-31 16:26:45 +0200

w_hairst gravatar image

I am trying to transfer a series of tables from an old OOBase file to a new LOBase one. I've set up the tables, forms, queries, and reports in the new file, so I'm (finally!) ready to transfer the data itself. I have the old tables exported into Calc. I can easily paste them into the new tables, but it won't let me copy over the primary key (probably because it's an autonumber field). As the primary keys of each table are used as foreign keys in the other tables, I can't simply allow the new file to assign new keys. I can't seem to edit my new tables, probably because I'm using a split database (, so turning off autonumber temporarily doesn't seem to be an option.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-12-31 20:08:29 +0200

Ratslinger gravatar image


Have just tested this and do not find any problem. Used Ubuntu 18.04 Mate with LO v6.3.4.2 and created a new split file with the v2.3.2 HSQLDB from the link. (You provide no specifics on the system you are using).

Performed a very simple test but here is result:

image description

This shows the Table with auto increment set, the data in the Calc file and the table result after copy/paste of data from Calc to the table.

In the paste used Append data and selected Use first line as column names.

With the split file you cannot edit existing columns using the graphic interface once the table is saved. You can delete fields and create new ones. SQL can be used to modify existing items.

edit flag offensive delete link more


You never actually state what if any error you are getting. Make certain the data is in fact numeric and of the type you are using. This is seen during the paste process when going through the Next steps.

Ratslinger gravatar imageRatslinger ( 2020-01-01 04:08:06 +0200 )edit

You are correct. When I pasted, and got to the "Assign columns" screen, it wouldn't let me select my primary key in the destination table. I wrongly assumed this meant it wouldn't copy over. It copied the primary key field just fine.

I am getting a warning "data exception: invalid character value for cast" for each paste. It doesn't seem to matter which fields I copy over, or how much data. The data was copied from a OOBase table with identical fields (types, sizes, etc.) to the new LOBase table. Any ideas why this would happen?

Ubuntu 18.04, LO, HSQLDB 2.3.2 (I think)

w_hairst gravatar imagew_hairst ( 2020-01-02 02:11:41 +0200 )edit

Glad the key field worked out OK.

As for the 'warning' would need sample data causing this. Don't recall that particular message. If you wish to proceed, this is best as a new question. If so be specific on the process.

Seems to me that type of message states data was not transferred correctly/completely. Would check there first. If all the data is correctly copied, sample is probably only way to test.

Also should mention that it is not clear as to why in the original question you were using Calc. Should be able to go directly from one DB table to another - have done this with many DB's and tables. Calc can change the data type if you are not careful.

Ratslinger gravatar imageRatslinger ( 2020-01-02 02:39:54 +0200 )edit

Thanks for the info. I used Calc because OOBase was being run under a VM, and (originally) LOBase wouldn't open the old OOBase file. Tried it again just now, and it looks like I'll be able to try copying directly. Thank you again.

w_hairst gravatar imagew_hairst ( 2020-01-02 12:06:07 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-12-31 16:26:45 +0200

Seen: 96 times

Last updated: Dec 31 '19