Help Moving Data from Spreadsheet into Base Table

Creating tables and queries
Post Reply
Needing Help
Posts: 7
Joined: Sun Aug 01, 2021 9:45 pm

Help Moving Data from Spreadsheet into Base Table

Post by Needing Help »

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
Attachments
USAHRdata.odb
(6.45 KiB) Downloaded 223 times
OpenOffice 4.1.9 on Windows
User avatar
robleyd
Moderator
Posts: 5035
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Help Moving Data from Spreadsheet into Base Table

Post by robleyd »

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
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Needing Help
Posts: 7
Joined: Sun Aug 01, 2021 9:45 pm

Re: Help Moving Data from Spreadsheet into Base Table

Post by Needing Help »

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. :crazy:
 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)
  • 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?
I deleted your post in the other topic and then locked it. The link robleyd gave you was so you could look at it. You should post in your own topic, not that one where your post is out-of-context and confusing.
-- MrProgrammer, forum moderator 
OpenOffice 4.1.9 on Windows
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Help Moving Data from Spreadsheet into Base Table

Post by Villeroy »

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
Needing Help
Posts: 7
Joined: Sun Aug 01, 2021 9:45 pm

Re: Help Moving Data from Spreadsheet into Base Table

Post by Needing Help »

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]
OpenOffice 4.1.9 on Windows
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Help Moving Data from Spreadsheet into Base Table

Post by Villeroy »

ALTER TABLE "Table Name" ALTER COLUMN "Column Name" INTEGER GENERATED ALWAYS AS IDENTITY

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
Needing Help
Posts: 7
Joined: Sun Aug 01, 2021 9:45 pm

Re: Help Moving Data from Spreadsheet into Base Table

Post by Needing Help »

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
OpenOffice 4.1.9 on Windows
Needing Help
Posts: 7
Joined: Sun Aug 01, 2021 9:45 pm

Re: Help Moving Data from Spreadsheet into Base Table

Post by Needing Help »

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 :knock:
OpenOffice 4.1.9 on Windows
Needing Help
Posts: 7
Joined: Sun Aug 01, 2021 9:45 pm

Re: Help Moving Data from Spreadsheet into Base Table

Post by Needing Help »

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
OpenOffice 4.1.9 on Windows
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Help Moving Data from Spreadsheet into Base Table

Post by Sliderule »

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
Needing Help
Posts: 7
Joined: Sun Aug 01, 2021 9:45 pm

Re: Help Moving Data from Spreadsheet into Base Table

Post by Needing Help »

Thank you so much - first to find the way to send a Private Message
OpenOffice 4.1.9 on Windows
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Help Moving Data from Spreadsheet into Base Table

Post by Sliderule »

I will send you a Private Message . . . and . . . you can reply there. One moment.
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Help Moving Data from Spreadsheet into Base Table

Post by Sliderule »

I sent you a private message, after you sent me one.

To see it,
  1. go to the TOP of the Forum web site
  2. toward the left, click on User Control Panel
  3. click on the Private Messages tab
  4. click on Inbox
Sliderule
Post Reply