Add primary key to DB made from a spreadsheet
-
- Posts: 40
- Joined: Wed Aug 31, 2016 2:15 am
Add primary key to DB made from a spreadsheet
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!
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!
Libre Office 5 on Ubuntu 16.04
- charlie.it
- Volunteer
- Posts: 417
- Joined: Wed Aug 21, 2013 2:12 pm
- Location: Italy
Re: Add primary key to DB made from a spreadsheet
Yestreday that forum has expired permanently.Pedroski55 wrote: I asked about this on libreoffice.org here:
http://en.libreofficeforum.org/node/15672#comment-50619 but they seem to have stopped working.
I think so:
Code: Select all
ALTER TABLE "Sheet1" ……BEFORE "Cnumber"
charlie
Italian AOO Admin
macOS 14 Sonoma M1: Open Office 4.1.15 - LibreOffice 7.5.7.1
http://www.charlieopenoffice.altervista.org
Italian AOO Admin
macOS 14 Sonoma M1: Open Office 4.1.15 - LibreOffice 7.5.7.1
http://www.charlieopenoffice.altervista.org
-
- Posts: 40
- Joined: Wed Aug 31, 2016 2:15 am
Re: Add primary key to DB made from a spreadsheet
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!
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!
Libre Office 5 on Ubuntu 16.04
Re: Add primary key to DB made from a spreadsheet
Hello
Does not work for flat tables.
What is your database engine? See left down in your databasedocument window
Romke
Does not work for flat tables.
What is your database engine? See left down in your databasedocument window
Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
-
- Posts: 40
- Joined: Wed Aug 31, 2016 2:15 am
Re: Add primary key to DB made from a spreadsheet
It says Embedded database and a bit further along it says HSQLDB Embedded
Is that what you mean??
What is a flat table??
Is that what you mean??
What is a flat table??
Libre Office 5 on Ubuntu 16.04
Re: Add primary key to DB made from a spreadsheet
Hello
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.
As long you use the embedded version make real often a backup or free your database. On this moment I have no link.
Romke
It is good you tell you have a embedded database. I did think you have a database based on sheets.Pedroski55 wrote:It says Embedded database and a bit further along it says HSQLDB Embedded
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
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Re: Add primary key to DB made from a spreadsheet
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
-
- Posts: 40
- Joined: Wed Aug 31, 2016 2:15 am
Re: Add primary key to DB made from a spreadsheet
Thanks again, but I already tried using "Sheet1" and "Cnumber", I get the same error with or without ""
Any other tricks up your sleeve??
Any other tricks up your sleeve??
Libre Office 5 on Ubuntu 16.04
- charlie.it
- Volunteer
- Posts: 417
- Joined: Wed Aug 21, 2013 2:12 pm
- Location: Italy
Re: Add primary key to DB made from a spreadsheet
More easily can enter the primary key when importing following the wizard.
charlie
Italian AOO Admin
macOS 14 Sonoma M1: Open Office 4.1.15 - LibreOffice 7.5.7.1
http://www.charlieopenoffice.altervista.org
Italian AOO Admin
macOS 14 Sonoma M1: Open Office 4.1.15 - LibreOffice 7.5.7.1
http://www.charlieopenoffice.altervista.org
Re: Add primary key to DB made from a spreadsheet
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.
THEN you import foreign data from spreadsheets, text files, other databases into this valid structure.
This will save you a lot of headache.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 40
- Joined: Wed Aug 31, 2016 2:15 am
Re: Add primary key to DB made from a spreadsheet
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?
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?
Last edited by Pedroski55 on Wed Aug 31, 2016 4:03 pm, edited 1 time in total.
Libre Office 5 on Ubuntu 16.04
Re: Add primary key to DB made from a spreadsheet
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
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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 40
- Joined: Wed Aug 31, 2016 2:15 am
Re: Add primary key to DB made from a spreadsheet
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.
Thanks for trying, I'll play with it some more, see if I can make it work.
Libre Office 5 on Ubuntu 16.04
Re: Add primary key to DB made from a spreadsheet
Works for me with the LO5 shipped with Ubuntu 16.4.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Add primary key to DB made from a spreadsheet
Instead of using the standard Base Open screen, do this instead.Pedroski55 wrote: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:
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.
LibreOffice 6.1.3.2 (Linux Fedora 29 on x86_64) with 'Notebookbar'
OpenOffice.org since 2002-12. LibreOffice user since 2013-current.
HSQLDB 2.5.0
OpenOffice.org since 2002-12. LibreOffice user since 2013-current.
HSQLDB 2.5.0
Re: Add primary key to DB made from a spreadsheet
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.LilZebra wrote: I had the same problem until I learned this way is better if converting from a Calc to a Base file.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice