| View previous topic :: View next topic |
| Author |
Message |
lecmac
General User
Joined: 06 Jul 2010
Posts: 12
Location: Carlsbad, California
|
 Posted: Tue Aug 31, 2010 3:10 pm Post subject: Importing a spreadsheet into Base |
 |
|
| 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 |
|
 |
dacm
Super User
Joined: 07 Jan 2010
Posts: 734
|
| Posted: Tue Aug 31, 2010 9:32 pm Post subject: |
|
|
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 + Migrating 'Embedded databases' |
|
| Back to top |
|
|
lecmac
General User
Joined: 06 Jul 2010
Posts: 12
Location: Carlsbad, California
|
| Posted: Wed Sep 01, 2010 5:19 am Post subject: Importing a Spreadsheet into base |
|
|
| 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 |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2473
Location: 3rd Rock From The Sun
|
| Posted: Wed Sep 01, 2010 6:54 am Post subject: |
|
|
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  ) 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.
If so, follow these directions:
- Open your database file ( *.odb )
- Press the Table icon on the left
- Open your Calc spreadsheet
- 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 )
- Right click on the database table ( *.odb ) that you want to INSERT rows
- From the drop-down menu . . . choose . . . Paste
- 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
- Press the Next> button
- In the Assign Columns dialog . . . make sure the correct columns of the Spreadsheet 'align' to the correct columns of the database table
- Press the Create button
- 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 |
|
|
lecmac
General User
Joined: 06 Jul 2010
Posts: 12
Location: Carlsbad, California
|
| Posted: Sun Sep 12, 2010 9:09 am Post subject: Not successful but will keep trying |
|
|
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 |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2473
Location: 3rd Rock From The Sun
|
| Posted: Sun Sep 12, 2010 10:35 am Post subject: |
|
|
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 |
|
|
lecmac
General User
Joined: 06 Jul 2010
Posts: 12
Location: Carlsbad, California
|
| Posted: Sun Sep 12, 2010 1:58 pm Post subject: |
|
|
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.  |
|
| Back to top |
|
|
|
