OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Importing From Calc to Base;Primary Key Problems

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Malik
General User
General User


Joined: 23 Mar 2011
Posts: 8

PostPosted: Sat Apr 23, 2011 10:33 am    Post subject: Importing From Calc to Base;Primary Key Problems Reply with quote

By using Select All (windows Ctrl A) and then Copy (Windows Ctrl C) from a Calc Sheet. Then opening Base and and selecting "Tables" then Paste (Windows Ctrl V) and using the Wizard to create the Table. When the Wizard makes the "ID" Primary Key. It makes it non-auto incrementing? When I try to make a new primary key field (one that does auto increment) Base doesn't seam to want to let me. Even if I make a table without a key then try to add one. or by deleting the key the wizard made then make a new key. The options for changing the "ID" field (under Field Properties) to be Auto Value are greyed out and set to No.
I get this error when i try to add a key that is Auto Value,
"Error while saving Table design
Column constraints are not acceptable in statement [ALTER TABLE "Table"
ADD "ID2" INTEGER IS NOT NULL] "
I've tried different integer types and formats to no avail. Am I doing something wrong or is there a better way to import a Calc sheet?

Running as Admin on WinXP SP3 with OOo 3.3 (can't wait till 3.4 Smile )
_________________
OOo 3.3 on Win XP Pro SP3
Back to top
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Sat Apr 23, 2011 3:02 pm    Post subject: Reply with quote

This strange way works for me.

File > New > Database > Finish and name it.
Table selected > Create in Design View. Add any field and save ignoring the complaint.
Copy from your spreadsheet including the field names.
Right click the table > Paste.
At the top change Table1 to Table2. Bullet Definition and Data, Append and check only Use First Line...
Next and insert the field names.
Create and this time let it create the key.
You should now be able to right click Table2 and Edit to set the ID to auto increment.
Back to top
View user's profile Send private message
sainttomn
OOo Enthusiast
OOo Enthusiast


Joined: 18 Feb 2011
Posts: 131
Location: Two gangster planets and a cowboy world

PostPosted: Mon Apr 25, 2011 3:54 pm    Post subject: Reply with quote

Malik, when you try to save, do you have null data in the ID field? That's caused my trouble in the past.
_________________
"I'm a loner, Dottie. A rebel."
[Windows 7] [OO.o 3.3] [HSQL 1.8]

The Lazer Cat
Back to top
View user's profile Send private message Visit poster's website
orlywho
Newbie
Newbie


Joined: 27 Apr 2011
Posts: 2
Location: Illinois

PostPosted: Wed Apr 27, 2011 11:41 am    Post subject: Reply with quote

I have the Database Table set up and brought the first set of data in from Calc no problems. Very easy. But I am having the same issues related to appending the next set of data from Calc after the table has been created. Every month I need to add new data to the existing table from a Calc spreadsheet and the Key (set to Auto increment) in the table appears to be messing it up. I tested different things and found if I go back to the table and delete the Key completely it will append the data no problems. But with the Key it won't. On another similar thread someone brought this up and they said when appending they had to put a new column in the Calc sheet and put in the next sequence of numbers that follow from the Key in the Database in that column in order for it append . . . seems like a crazy work around. Is there any other way?
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Thu Apr 28, 2011 3:34 am    Post subject: Reply with quote

Quote:
Every month I need to add new data to the existing table from a Calc spreadsheet and the Key (set to Auto increment) in the table appears to be messing it up.

Welcome to the forum.
It should be possible to copy and append data from the same Calc layout as that previously used when the table was created in Base. I suspect you will have added an auto increment Primary Key field when creating that initial table. Appending subsequent rows copied from Calc requires that the fields are correctly aligned between the two sets of data.

The second dialog screen of the append process will list the columns of the 'Source table' on the left and the 'Destination table' on the right. Not only must the columns in the Source table be selected but they must also be correctly aligned with those in the Destination table. The latter may well have an extra field - the added primary key - and this can throw out the alignment if this is anywhere other than at the bottom of the Destination table. The method used to achieve alignment is not immediately obvious because it does not permit you to move directly the position of an unmatched field. It is a case of selecting a potential match in the right hand Destination table and using the right hand arrows to move it up to match the correct item in the left or Source table. Once all the columns are aligned the auto increment primary key will probably be isolated without a match at the bottom of the Destination table. That is on no concern because it is the match of fields and data types which ensures the correct append operation: the order of the fields in this dialog is not relevant.

Please come back to let us know how it goes - particularly, if my explanation needs clarification.
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu
Back to top
View user's profile Send private message
orlywho
Newbie
Newbie


Joined: 27 Apr 2011
Posts: 2
Location: Illinois

PostPosted: Thu Apr 28, 2011 1:04 pm    Post subject: add new data to the existing table ID errors Reply with quote

thank you Arineckaig - in trying to append I was doing it from the calc spreadsheet using the window to the Database and I got error msgs.I had been mapping the fields previously and still had error msgs or the data just didn't go. Then I had success by making a new temp table with the data to be appended in that table. Then I appended it from that table to the table with the past data that had the primary key. It worked with no error msgs. Then I got your reply and tried it again from the calc spreadsheet and it worked too this time. As long as it keeps working one way or the other I will be happy! Thanks for your reply.[/quote]
Back to top
View user's profile Send private message
Malik
General User
General User


Joined: 23 Mar 2011
Posts: 8

PostPosted: Thu May 05, 2011 12:48 pm    Post subject: Reply with quote

Yes, the ID field is 'NULL' in fact i'm having the wizard create it. it wasn't a existing field from the calc sheet. i found a painful workaround, it requires installing OOo 3.2 then it works correctly and when i upgrade back to 3.3 it is no longer greyed out ? Odd huh?
_________________
OOo 3.3 on Win XP Pro SP3
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group