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 a spreadsheet into Base

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


Joined: 06 Jul 2010
Posts: 12
Location: Carlsbad, California

PostPosted: Tue Aug 31, 2010 3:10 pm    Post subject: Importing a spreadsheet into Base Reply with quote

I have a TABLE and FORM made up like I want it in BASE. How can I import a spreadsheet into this TABLE that I have in BASE?
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Tue Aug 31, 2010 9:32 pm    Post subject: Reply with quote

I don't think you can import a spreadsheet directly into a pre-configured table.
I presume your form relies on the name of your pre-configured table, so I would make backup copies of everything and proceed to:
1) rename the existing table in Base
2) open the spreadsheet in Calc, side-by-side with Base still running
3) "Select All" by clicking on the upper left-corner of your spreadsheet in Calc
4) Click & drag the spreadsheet selection onto the "Tables" icon in Base
5) follow the popup wizard in Base to create a new table from the spreadsheet data
6) rename the new table using the original table's name so it will work with your form
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
lecmac
General User
General User


Joined: 06 Jul 2010
Posts: 12
Location: Carlsbad, California

PostPosted: Wed Sep 01, 2010 5:19 am    Post subject: Importing a Spreadsheet into base Reply with quote

Your instructions 1-4 worked perfectly. When I got to #5 the table popup wizard in Base is gray and would not let me create a new table from the spreadsheet data. What am I doing wrong?
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Sep 01, 2010 6:54 am    Post subject: Reply with quote

lecmac:

You asked:

lecmac wrote:
import a spreadsheet into this TABLE

I will assume, what you want to do is ONLY import ( your word, not mine Smile ) the SPREADSHEET DATA, that is, so the SPREADSHEET DATA will be 'inserted' into NEW ROWS of your PRE-EXISTING DATABASE TABLE. Remember, database referential integrity will have to be maintained . . . that is . . . it will NOT allow you to insert DUPLICATE rows . . . based on your Primary Key. Smile

If so, follow these directions:
  1. Open your database file ( *.odb )
  2. Press the Table icon on the left
  3. Open your Calc spreadsheet
  4. Copy to the clipboard, the rows you want to INSERT in the database table ( this means you have to select the Calc rows INCLUDING a row title for each column )
  5. Right click on the database table ( *.odb ) that you want to INSERT rows
  6. From the drop-down menu . . . choose . . . Paste
  7. In the Copy Table dialog ( screen ) . . . the Table name should be the same as the table that will receive the new data AND in the Options section . . . choose Append data radio button
  8. Press the Next> button
  9. In the Assign Columns dialog . . . make sure the correct columns of the Spreadsheet 'align' to the correct columns of the database table
  10. Press the Create button
  11. Smile and say: "Gee Sliderule, that was easy. Now, all I have to do is let the forum know this worked, and, add the word [Solved] to the Title of this forum post ( see instructions below ).
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
lecmac
General User
General User


Joined: 06 Jul 2010
Posts: 12
Location: Carlsbad, California

PostPosted: Sun Sep 12, 2010 9:09 am    Post subject: Not successful but will keep trying Reply with quote

I have tried to follow your instructions and haven't been successful. Either I am too dumb to make it work or I don't understand what you mean by "Remember, database referential integrity will have to be maintained . . . that is . . . it will NOT allow you to insert DUPLICATE rows . . . based on your Primary Key. "
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sun Sep 12, 2010 10:35 am    Post subject: Reply with quote

lecmac:

You said:

lecmac wrote:
I have tried to follow your instructions and haven't been successful. Either I am too dumb to make it work or I don't understand what you mean by "Remember, database referential integrity will have to be maintained . . . that is . . . it will NOT allow you to insert DUPLICATE rows . . . based on your Primary Key. "


A quick explanation. A database table MUST contain a Primary Key.

A Primary Key means, the field or fields that comprise the Primary Key MUST make the rows UNIQUE. If, for example, you have a table of Books, and, the primary key is "TITLE" . . . if you attempt to INSERT ( append ) data to a table, any record with a DUPLICATE value for "TITLE" . . . will result in an error, and, not be able to INSERT ( append ) the data.

I hope that is clear.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
lecmac
General User
General User


Joined: 06 Jul 2010
Posts: 12
Location: Carlsbad, California

PostPosted: Sun Sep 12, 2010 1:58 pm    Post subject: Reply with quote

Hi Sliderrule,

Thanks for the explanation. I thought I had followed that rule with my importing data into my file but I will continue to try as I get time. I do appreciate all you have done to help. I cannot say resolved but hope I can soon. Razz Razz Razz
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
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