Creating a Table From a Spreadsheet

From NeoWiki

(Difference between revisions)
Jump to: navigation, search
Revision as of 20:27, 22 July 2007 (edit)
Lorinda (Talk | contribs)
m (fix redbox template typo)
← Previous diff
Revision as of 01:17, 26 July 2007 (edit) (undo)
Lorinda (Talk | contribs)
m (fix some typos)
Next diff →
Line 1: Line 1:
{{redbox|This article is part of the [[To Base and Back Again]] tutorial series}} {{redbox|This article is part of the [[To Base and Back Again]] tutorial series}}
-For the birding journal database, we need a table listing all of the birds we might see in our widing birding area. Creating a table from scratch would be tedious and time consuming. Fortunately, the data we need is already available if a format we can access and use. The {http://www.aou.org/checklist/index.php3 American Ornithologists' Union} provides their checklist of birds in the AOU area (North and Middle America) in an Excel file. This article describes how to get this data from the xls file into our Birding Journal database.+For the birding journal database, we need a table listing all of the birds we might see in our birding area. Creating a table from scratch would be tedious and time consuming. Fortunately, the data we need is already available if a format we can access and use. The {http://www.aou.org/checklist/index.php3 American Ornithologists' Union} provides their checklist of birds in the AOU area (North and Middle America) in an Excel file. This article describes how to get this data from the xls file into our Birding Journal database.
==Importing Spreadsheets vs. Connecting to Spreadsheets== ==Importing Spreadsheets vs. Connecting to Spreadsheets==
NeoOffice Base can use data from spreadsheets in two different ways. One option is to '''connect''' to an existing spreadsheet. This method is described in the [[Using a Calc spreadsheet as a data source]] article. When using this method, the data can only be edited within the source spreadsheet; data cannot be edited from within Base. NeoOffice Base can use data from spreadsheets in two different ways. One option is to '''connect''' to an existing spreadsheet. This method is described in the [[Using a Calc spreadsheet as a data source]] article. When using this method, the data can only be edited within the source spreadsheet; data cannot be edited from within Base.
-The other option is to import the data from the spreadsheet into the Base document. The data can then be edited in Base. (The original spreadsheet will not be altered when changes are made in base.). In our case, we want to import the data, but first the spreadsheet needs some work.+The other option is to import the data from the spreadsheet into the Base document. The data can then be edited in Base. (The original spreadsheet will not be altered when changes are made in Base.) In our case, we want to import the data, but first the spreadsheet needs some work.
==Formatting the Spreadsheet== ==Formatting the Spreadsheet==
Line 16: Line 16:
In the case of the AOU list, a careful inspection will show that the Order, Family, Subfamily, and genus fields only contain data for the first record with that order, family, subfamily, or genus. In our database, this information needs to be in each record. To fix this: In the case of the AOU list, a careful inspection will show that the Order, Family, Subfamily, and genus fields only contain data for the first record with that order, family, subfamily, or genus. In our database, this information needs to be in each record. To fix this:
:Click in a cell that contains an Order (or Family, etc.) name. The cell border becomes black, and a small back square is visible in the lower right hand corner of the cell. :Click in a cell that contains an Order (or Family, etc.) name. The cell border becomes black, and a small back square is visible in the lower right hand corner of the cell.
-:Drag this square downward; as you do so, a red boarder drags with you. Keep dragging until the boarder encompasses all of the empty cells, up to but not including the next cell with text in it, then release the mouse button. The empty cells with the text content of the first cell.+:Drag this square downward; as you do so, a red border drags with you. Keep dragging until the border encompasses all of the empty cells, up to but not including the next cell with text in it, then release the mouse button. The empty cells with the text content of the first cell.
-The order of the AOU checklist a standard order, used in birding books and checklists. In order to make sure we can reproduce this order in the database, it would be wisest to add an ID field, with the first record given the number 1, the next 2, etc. To do so:+The order of the AOU checklist a standard order, used in birding books and checklists. To ensure that we can reproduce this order in the database, it would be wisest to add an ID field, with the first record given the number 1, the next 2, etc. To do so:
:Insert a new column to the left of Column A (click on column A and then choose {{Menu|Columns}} from the {{Menu|Insert}} menu. :Insert a new column to the left of Column A (click on column A and then choose {{Menu|Columns}} from the {{Menu|Insert}} menu.
:Type <<tt>>ID<</tt>> into the cell in new column A just to the left of the cell labeled <<tt>>order<</tt>> :Type <<tt>>ID<</tt>> into the cell in new column A just to the left of the cell labeled <<tt>>order<</tt>>
:Type a <<tt>>1<</tt>> into the first cell under <<tt>>ID<</tt>>: :Type a <<tt>>1<</tt>> into the first cell under <<tt>>ID<</tt>>:
:Follow the instructions on the [[Selecting and Filling a Cell Range]] wiki page to fill the column so that each cell contains a number one greater than the one above it. (i.e. 1, 2, 3, etc.) :Follow the instructions on the [[Selecting and Filling a Cell Range]] wiki page to fill the column so that each cell contains a number one greater than the one above it. (i.e. 1, 2, 3, etc.)

Revision as of 01:17, 26 July 2007

This article is part of the To Base and Back Again tutorial series

For the birding journal database, we need a table listing all of the birds we might see in our birding area. Creating a table from scratch would be tedious and time consuming. Fortunately, the data we need is already available if a format we can access and use. The {http://www.aou.org/checklist/index.php3 American Ornithologists' Union} provides their checklist of birds in the AOU area (North and Middle America) in an Excel file. This article describes how to get this data from the xls file into our Birding Journal database.

Importing Spreadsheets vs. Connecting to Spreadsheets

NeoOffice Base can use data from spreadsheets in two different ways. One option is to connect to an existing spreadsheet. This method is described in the Using a Calc spreadsheet as a data source article. When using this method, the data can only be edited within the source spreadsheet; data cannot be edited from within Base.

The other option is to import the data from the spreadsheet into the Base document. The data can then be edited in Base. (The original spreadsheet will not be altered when changes are made in Base.) In our case, we want to import the data, but first the spreadsheet needs some work.

Formatting the Spreadsheet

Before importing the data, we need to make sure that it is correctly formatted.

The first row of the data range (not necessarily of the document) should contain the field names. Any fields that must not be empty need to have data in them. In the case of the AOU list, a careful inspection will show that the Order, Family, Subfamily, and genus fields only contain data for the first record with that order, family, subfamily, or genus. In our database, this information needs to be in each record. To fix this:

Click in a cell that contains an Order (or Family, etc.) name. The cell border becomes black, and a small back square is visible in the lower right hand corner of the cell.
Drag this square downward; as you do so, a red border drags with you. Keep dragging until the border encompasses all of the empty cells, up to but not including the next cell with text in it, then release the mouse button. The empty cells with the text content of the first cell.

The order of the AOU checklist a standard order, used in birding books and checklists. To ensure that we can reproduce this order in the database, it would be wisest to add an ID field, with the first record given the number 1, the next 2, etc. To do so:

Insert a new column to the left of Column A (click on column A and then choose Columns from the Insert menu.
Type <>ID<> into the cell in new column A just to the left of the cell labeled <>order<>
Type a <>1<> into the first cell under <>ID<>:
Follow the instructions on the Selecting and Filling a Cell Range wiki page to fill the column so that each cell contains a number one greater than the one above it. (i.e. 1, 2, 3, etc.)
Personal tools