Add primary key to DB made from a spreadsheet

[Pedroski55]

Hi, I hope I can ask here, even though I use Libre Office!

I made a DB by importing the data from a spreadsheet. I did not get asked to create a primary key. Now I cannot insert new records. I asked about this on libreoffice.org here:

http://en.libreofficeforum.org/node/15672#comment-50619 but they seem to have stopped working.

I downloaded the file F3K Total supplied, followed his instructions and it worked first time, a primary key was added.

So I tried that with my file, the table is called Sheet1 and the first column is Cnumber. I went to Tools>SQL...

There I pasted

ALTER TABLE Sheet1 ADD ID INTEGER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 ) BEFORE Cnumber

but I get

1: syntax error, unexpected ALTER, expecting BETWEEN or SQL_TOKEN_IN or SQL_TOKEN_LIKE

As I mentioned above, this worked fine with F3K Total's file, so any ideas why it won't work with mine?? Thanks for any tips!

[charlie.it]

I asked about this on libreoffice.org here:

http://en.libreofficeforum.org/node/15672#comment-50619 but they seem to have stopped working.

Yestreday that forum has expired permanently.

I think so:

Code: Select all

ALTER TABLE "Sheet1" ……BEFORE "Cnumber"

[Pedroski55]

Nope, that also didn't work. Same old:

1: syntax error, unexpected ALTER, expecting BETWEEN or SQL_TOKEN_IN or SQL_TOKEN_LIKE

As I said, using the file the gentleman posted, this worked fine, but it won't work with my file!

[RPG]

Hello

Does not work for flat tables.

What is your database engine? See left down in your databasedocument window

Romke

[Pedroski55]

It says Embedded database and a bit further along it says HSQLDB Embedded

Is that what you mean??

What is a flat table??

[RPG]

Hello

It says Embedded database and a bit further along it says HSQLDB Embedded

It is good you tell you have a embedded database. I did think you have a database based on sheets.

There you have a real HSQLDB database the instruction of F3K should work. Maybe the problem in your instruction is that the variable names are not surround with double quotes.

Code: Select all

ALTER TABLE "Sheet1" ADD ID INTEGER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 ) BEFORE "Cnumber"

As long you use the embedded version make real often a backup or free your database. On this moment I have no link.

Romke

[RPG]

Hello

FreeHSQLDB v.0.3

[Tutorial] Splitting an "embedded HSQL database"

Romke

[Pedroski55]

Thanks again, but I already tried using "Sheet1" and "Cnumber", I get the same error with or without ""

Any other tricks up your sleeve??

[charlie.it]

More easily can enter the primary key when importing following the wizard.

[Villeroy]

First you should have a valid database table with correct field types, primary key, foreign keys, indices and default values.
THEN you import foreign data from spreadsheets, text files, other databases into this valid structure.
This will save you a lot of headache.

[Pedroski55]

Thanks, so I've made a DB with a Primary key and a 4 names and surnames, called namesDB.odb as a test. It has 3 columns, ID, LastName and FirstName. ID is the Primary key How do I import the data from my spreadsheet in one foul swoop please?

I cannot see 'import data from spreadsheet' when I open this DB. When I copy say the column 'name' 4 names are pasted into 1 record!

Also, if what I want to do worked with the file that the gentleman who first gave me this advice posted, and I know it did as I tried it, why will it not work with my file?

[Villeroy]

Copy the spreadsheet cells.
Select the table icon in the database window. Do not open the table.
Paste.

-------------
Copy the cells.
Hit F4 and click the [Tables] icon of your registered db.
Paste

[Pedroski55]

I'm afraid that also does not work. It just pastes 1 column, the LastName column, not the 8 cells from the spreadsheet.

Thanks for trying, I'll play with it some more, see if I can make it work.

[Villeroy]

Works for me with the LO5 shipped with Ubuntu 16.4.

[LilZebra]

Hi, I hope I can ask here, even though I use Libre Office!

I made a DB by importing the data from a spreadsheet. I did not get asked to create a primary key. Now I cannot insert new records. I asked about this on libreoffice.org here:

Instead of using the standard Base Open screen, do this instead.

In one window have your Calc spreadsheet file open on your computer.

In another window have a new Base file, with the Table tab open.

Put your mouse on the Sheet1 tab (or whatever you've named the sheet).

Left drag with your mouse onto the window of Table in Base.

Base will then ask you questions about importing that data.

I had the same problem until I learned this way is better if converting from a Calc to a Base file.

[Villeroy]

I had the same problem until I learned this way is better if converting from a Calc to a Base file.

It is impossible to convert a spreadsheet into a database. You always dump raw data from spreadsheet or elsewhere into an existing structure. With the simple routine you have outlined you may generate a badly structured table with wrong data types, no foreign keys and you never get an auto-incrementing primary key.