Help Moving Data from Spreadsheet into Base Table
-
- Posts: 7
- Joined: Sun Aug 01, 2021 9:45 pm
Help Moving Data from Spreadsheet into Base Table
Am attempting to enter data into the Table OtherStudbookID from a Spreadsheet – a first time to enter data after creating the Table
Attempt to insert null into a non-nullable column: column: OtherStudbookID table: Other Studbooks in statement [INSERT INTO “Other Studbooks” (HorseID HorseName StudbookAbvID FoalYear ColorCodeID SexCodeID Sire Name SireRegNumber DamName DamRegNumber, etc etd) VALUES (??????????] Continue anyway?
I have tried anything and everything could possible think of – all the tables agree with the entries – all have the same value ex SireRegNumber is a Numeric
all the other entries which are not a date or a number are Text (VARCHAR)
I KNOW states inserting null into non-nullable column I DO NOT UNDERSTAND so please do not say Obvious
I have been reading for hours/trying
I cannot set an integer because of the way the data is set up to be copied and pasted from a spreadsheet – one is huge over 800,000 records, very small in size for each record but still that is a lot of records
Attempt to insert null into a non-nullable column: column: OtherStudbookID table: Other Studbooks in statement [INSERT INTO “Other Studbooks” (HorseID HorseName StudbookAbvID FoalYear ColorCodeID SexCodeID Sire Name SireRegNumber DamName DamRegNumber, etc etd) VALUES (??????????] Continue anyway?
I have tried anything and everything could possible think of – all the tables agree with the entries – all have the same value ex SireRegNumber is a Numeric
all the other entries which are not a date or a number are Text (VARCHAR)
I KNOW states inserting null into non-nullable column I DO NOT UNDERSTAND so please do not say Obvious
I have been reading for hours/trying
I cannot set an integer because of the way the data is set up to be copied and pasted from a spreadsheet – one is huge over 800,000 records, very small in size for each record but still that is a lot of records
- Attachments
-
- USAHRdata.odb
- (6.45 KiB) Downloaded 232 times
OpenOffice 4.1.9 on Windows
Re: Help Moving Data from Spreadsheet into Base Table
See if Error inserting a new record (Null) helps; it seems both problems are very similar.
Note that in your case the error points to column: OtherStudbookID
Note that in your case the error points to column: OtherStudbookID
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
-
- Posts: 7
- Joined: Sun Aug 01, 2021 9:45 pm
Re: Help Moving Data from Spreadsheet into Base Table
The Database will not accept Horse ID being changed from Text (VARCHAR) to Integer - even tried small integer. If it would accept Integer then the Auto Value could be changed from No to Yes. There are no records YET in the Table. The Primary Key OtherStudbookID cannot be an integer because as data is loaded into the Table the registration numbers cannot be changed - so they cannot automatically be increased by one
I tried this in Tools - Sql - ALTER TABLE "OtherStudbooks" ALTER "OtherStudbookID" GENERATED ALWAYS AS IDENTITY (start with XXX) ; response is: 1: Table not found: OtherStudbooks in statement [ALTER TABLE "OtherStudbooks"]
I do not understand what is being said. OtherStudbookID is an important table as it takes in data from all over the world but is not the main Horse ID Table which does have OtherStudbookID as a field
should not have (start with XXX) but what should it be 1? highest value for what? all that is used is TEXT, NUMERIC, and DATE
I did create all my tables in DESIGN VIEW, if that helps at all
*****
Tried this: ALTER TABLE "OtherStudbookID" ALTER "OtherStudbookID" GENERATED ALWAYS AS IDENTITY (start with 001) ;response: 2: Wrong data type: GENERATED in statement [ALTER TABLE "OtherStudbookID" ALTER "OtherStudbookID" GENERATED]
What can I name the OtherStudbook Table? Tried different wording but it all gets rejected.
I tried this in Tools - Sql - ALTER TABLE "OtherStudbooks" ALTER "OtherStudbookID" GENERATED ALWAYS AS IDENTITY (start with XXX) ; response is: 1: Table not found: OtherStudbooks in statement [ALTER TABLE "OtherStudbooks"]
I do not understand what is being said. OtherStudbookID is an important table as it takes in data from all over the world but is not the main Horse ID Table which does have OtherStudbookID as a field
should not have (start with XXX) but what should it be 1? highest value for what? all that is used is TEXT, NUMERIC, and DATE
I did create all my tables in DESIGN VIEW, if that helps at all
*****
Tried this: ALTER TABLE "OtherStudbookID" ALTER "OtherStudbookID" GENERATED ALWAYS AS IDENTITY (start with 001) ;response: 2: Wrong data type: GENERATED in statement [ALTER TABLE "OtherStudbookID" ALTER "OtherStudbookID" GENERATED]
What can I name the OtherStudbook Table? Tried different wording but it all gets rejected.
Edit: Merged here from Error inserting a new record (Null) to avoid fragmentation of discussion. Please keep discussion on one problem in one topic [robleyd, mod] |
Edit: Also, another post today from Needing Help in Error inserting a new record (Null)
-- MrProgrammer, forum moderator |
OpenOffice 4.1.9 on Windows
Re: Help Moving Data from Spreadsheet into Base Table
ALTER TABLE "OtherStudbookID" ALTER "OtherStudbookID" INTEGER GENERATED ALWAYS AS IDENTITY
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: 7
- Joined: Sun Aug 01, 2021 9:45 pm
Re: Help Moving Data from Spreadsheet into Base Table
I am no expert/grasping at straws - using the Integer I redid the entire set of Tables using CAPS for everything - the spreadsheet has all the data in numeric and CAPS so I did the same - changed the first row on the spreadsheet to all CAPS as well - OtherStudbookID and HorseID were both changed to Integers - cannot see where I can attach so you can see the new set of Tables.
When I placed in the SQL Command box the above Villeroy sent received this message: 1: Table not found: OtherStudbookID in statement [ALTER TABLE "OtherStudbookID"]
Tried just now to load data to the OtherStudbookID and here is the WARNING! Wrong data type: java.lang.IllegalArgumentException
What does this mean? Reading Villeroy - the dates given on the spreadsheet 00/00/0000 should move over to the Base Table "Base tries to "translate" the incoming data from its fellow spreadsheet application into something that can be understood by the target database application." I have no way of knowing how the spreadsheet was designed - if it was text?? or if date?? was used. Tried: SELECT ALL EXPAND VIEW COALESCE ( '(' || TO_CHAR( "tHorse"."DateOfBirth", 'DD/MM/YY' ) ')', '' )
response: : Unexpected token COALESCE, requires FROM in statement [SELECT ALL EXPAND VIEW COALESCE]
When I placed in the SQL Command box the above Villeroy sent received this message: 1: Table not found: OtherStudbookID in statement [ALTER TABLE "OtherStudbookID"]
Tried just now to load data to the OtherStudbookID and here is the WARNING! Wrong data type: java.lang.IllegalArgumentException
What does this mean? Reading Villeroy - the dates given on the spreadsheet 00/00/0000 should move over to the Base Table "Base tries to "translate" the incoming data from its fellow spreadsheet application into something that can be understood by the target database application." I have no way of knowing how the spreadsheet was designed - if it was text?? or if date?? was used. Tried: SELECT ALL EXPAND VIEW COALESCE ( '(' || TO_CHAR( "tHorse"."DateOfBirth", 'DD/MM/YY' ) ')', '' )
response: : Unexpected token COALESCE, requires FROM in statement [SELECT ALL EXPAND VIEW COALESCE]
OpenOffice 4.1.9 on Windows
Re: Help Moving Data from Spreadsheet into Base Table
ALTER TABLE "Table Name" ALTER COLUMN "Column Name" INTEGER GENERATED ALWAYS AS IDENTITY
http://www.hsqldb.org/doc/1.8/guide/ch0 ... le-section
http://www.hsqldb.org/doc/1.8/guide/ch0 ... le-section
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: 7
- Joined: Sun Aug 01, 2021 9:45 pm
Re: Help Moving Data from Spreadsheet into Base Table
ALTER TABLE "OTHERSTUDBOOK" ALTER COLUMN "FOALYEAR" INTEGER GENERATED BY ALWAYS AS IDENTITY
first response stated had to have BY
second response after including BY
: Unexpected token ALWAYS, requires DEFAULT in statement [ALTER TABLE "OTHERSTUDBOOK" ALTER COLUMN "FOALYEAR" INTEGER GENERATED BY ALWAYS]
The tables are all empty! I am trying to load data into just the one table now
FOALYEAR table has this as primary key as VARCHAR; then comes HORSE ID, OTHERSTUDBOOKID and now FOALYEAR with all valued as Integer
first response stated had to have BY
second response after including BY
: Unexpected token ALWAYS, requires DEFAULT in statement [ALTER TABLE "OTHERSTUDBOOK" ALTER COLUMN "FOALYEAR" INTEGER GENERATED BY ALWAYS]
The tables are all empty! I am trying to load data into just the one table now
FOALYEAR table has this as primary key as VARCHAR; then comes HORSE ID, OTHERSTUDBOOKID and now FOALYEAR with all valued as Integer
OpenOffice 4.1.9 on Windows
-
- Posts: 7
- Joined: Sun Aug 01, 2021 9:45 pm
Re: Help Moving Data from Spreadsheet into Base Table
I am not getting anywhere - nothing works! Tried moving entire spreadsheet drag and drop - no way! Tried moving one column at a time nothing!
A TIME OUT - the whole thing has corrupted with all the hit and miss SQL commands to have to re-create again, fourth time, the entire Tables
A TIME OUT - the whole thing has corrupted with all the hit and miss SQL commands to have to re-create again, fourth time, the entire Tables
OpenOffice 4.1.9 on Windows
-
- Posts: 7
- Joined: Sun Aug 01, 2021 9:45 pm
Re: Help Moving Data from Spreadsheet into Base Table
Desperation Ville
After fighting/reading/fighting etc all day
Took first 3 rows which includes Column Heading
Copy
Paste - up comes the copy table box and what I see is column and the entire first row ending with column 1
does not matter if I uncheck Source which is the copy table box or not nothing happens and again attempting to insert null into non-nullable columns
Tried doing this with the Sex Table and got 3 rows for mare, stallion, gelding and across the table the columns where there should be a horse for whichever sex it was - nothing, blank
After fighting/reading/fighting etc all day
Took first 3 rows which includes Column Heading
Copy
Paste - up comes the copy table box and what I see is column and the entire first row ending with column 1
does not matter if I uncheck Source which is the copy table box or not nothing happens and again attempting to insert null into non-nullable columns
Tried doing this with the Sex Table and got 3 rows for mare, stallion, gelding and across the table the columns where there should be a horse for whichever sex it was - nothing, blank
OpenOffice 4.1.9 on Windows
Re: Help Moving Data from Spreadsheet into Base Table
Since no one else has answered your ultimate question here, and, I think you are in need of help, I would like to suggest you send me a Private Message, ( AKA PM ), and, perhaps we can communicate in a way to help resolve the issues you are having with the database ( definition, design, inserting data, data manipulation - that is Queries, etc ).
If you are interested, please send me a Private Message , and, perhaps we can work together to get you on your way.
Sliderule
If you are interested, please send me a Private Message , and, perhaps we can work together to get you on your way.
Sliderule
-
- Posts: 7
- Joined: Sun Aug 01, 2021 9:45 pm
Re: Help Moving Data from Spreadsheet into Base Table
Thank you so much - first to find the way to send a Private Message
OpenOffice 4.1.9 on Windows
Re: Help Moving Data from Spreadsheet into Base Table
I will send you a Private Message . . . and . . . you can reply there. One moment.
Re: Help Moving Data from Spreadsheet into Base Table
I sent you a private message, after you sent me one.
To see it,
To see it,
- go to the TOP of the Forum web site
- toward the left, click on User Control Panel
- click on the Private Messages tab
- click on Inbox