Creating a Table From a Spreadsheet

From NeoWiki

(Difference between revisions)

Current revision

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 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.

1 Importing Spreadsheets vs. Connecting to Spreadsheets
2 Formatting the Spreadsheet
3 Importing the Data
4 Related Wiki Articles
5 Related External Links

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.
Field names should not include spaces. Change the cell containing the text English Name to EnglishName
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 fill with the text content of the first cell.
Repeat this procedure until the empty fields are filled. (Note that in some cases, Subfamily will remain empty)

Any rows with empty (null) cells in the English Name column must be deleted.

A careful examination of the spreadsheet will now show that there are some rows that contain Order, Family, Subfamily, etc. names, but do not include an entry in the English Name column, nor in any column to the right of English Name. These are the rows that originally formed the "headers" for the various sections. To delete these rows, right-click or control-click in the rectangle to the far left of the "empty" row to select the whole row and choose Delete Rows from the contextual menu.

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 AOU_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.)

I also changed some of the field names, especially those that were originally just one letter; adjust these names as you see fit.

Importing the Data

We are now ready to import the data.

Select the entire range of the data, including the row with column names. The easiest way to do this is probably to click in the upper left hand cell of the range (probably the cell labeled ID), then scroll down to the last row of data and Shift-click in the far right hand cell of the range.

Copy this selected range

Open the Birder's journal database

Click on the Tables icon in the Database column on the right.

In the lower rectangular area, labeled Tables, right-click or control-click in the white space. (If there is an existing table, make sure not to click on it).

In the contextual menu that comes up, choose Paste. The Copy Table window appears.

Give the table a name in the Table Name field; I chose AOU_Birdlist.

Make sure the Definition and Data radio button is marked

Because we added an ID field while prepping the spreadsheet, we want to leave the Create primary key checkbox unchecked. If you were importing data that did not already include a primary key field, you would want to check this box.

Click Next

The Apply columns that appears allows you to choose which fields you want to import. In our case we want to import all of them, so you can click on the >> button.

Then click Next

The Type Formatting window appears. Highlight each field in turn in the vertical column to the left, and adjust the Field Name, Field Type, Entry Required, and Length settings as needed.

In this table, all the fields are TEXT except for the ID field, whose field type should be INTEGER.

Most of the Lengths are ok, although the Hawaii, Introduced, Accidental, and Nonbreeding could be reduced to a length of 5 or so, since the data in those columns is generally only one character.

The ID and English Name fields should be marked for Entry Required.

Right-click or control-click on the "ID" field name in the left hand column. Select Primary Key from the contextual menu (it is the only option.) A yellow key appears to the left of the field name.

Once the fields are properly defined, click on Create

Note: Make certain that all the field definitions are correct before clicking Create. Attempting to change field types, lengths, etc. later will destroy data in those fields.

If you forget to set the primary key, a No primary key warning window will appear. You can click Cancel and set the primary key as above, or you can click No and identify the primary key in as follows:

Once the new table has appeared in the Tables Section of the document window, right-click or control-click on the new table. Select Edit from the contextual menu. The table opens in design view.

Click on the line containing the ID field. A green flippy triangle appears in the "cell" to the left of the word ID

control-click or right-click on the green triangle and choose Primary Key from the contextual menu. A key icon appears in the box

Click on the disk-drive icon in the toolbar to save your changes, then close the window.

You have successfully imported data from a spreadsheet.

 {{TBBA Header}}
-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.
+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://checklist.aou.org/ 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==
 *The first row of the data range (not necessarily of the document) should contain the field names.
+*Field names should not include spaces.  Change the cell containing the text <tt>English Name</tt> to <tt>EnglishName</tt>
 *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.
+:*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 fill with the text content of the first cell.
+:*Repeat this procedure until the empty fields are filled. (Note that in some cases, Subfamily will remain empty)
 *Any rows with empty (null) cells in the English Name column must be deleted.
 *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.
-:*Type <<tt>>ID<</tt>> into the cell in new column A just to the left of the cell labeled <<tt>>order<</tt>>
+:*Type <tt>AOU_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.)
 :Open the Birder's journal database
 :Click on the {{prefName|Tables}} icon in the {{Section|Database}} column on the right.
-:In the lower rectangular area, labled {{Section|Tables}}, right-click or control-click in the white space.  (If there is an existing table, make sure not to click on it).
+:In the lower rectangular area, labeled {{Section|Tables}}, right-click or control-click in the white space.  (If there is an existing table, make sure not to click on it).
 :In the contextual menu that comes up, choose {{Menu|Paste}}.  The {{Window|Copy Table}} window appears.
-:Give the table a name in the {{prefName|Table Name}} field; I chose AOU Birdlist.
+:Give the table a name in the {{prefName|Table Name}} field; I chose AOU_Birdlist.
 :Make sure the {{prefName|Definition and Data}} radio button is marked
 :Because we added an ID field while prepping the spreadsheet, we want to leave the {{prefName|Create primary key}} checkbox ''unchecked.''  If you were importing data that did not already include a primary key field, you would want to check this box.
 :The {{Window|Type Formatting}} window appears.  Highlight each field in turn in the vertical column to the left, and adjust the {{prefName|Field Name}}, {{prefName|Field Type}}, {{prefName|Entry Required}}, and {{prefName|Length}} settings as needed.
 ::In this table, all the fields are TEXT ''except'' for the ID field, whose field type should be INTEGER.
-::Most of the Lengths are ok, although the Hawaii, Introduced, Accidental, and Nonbreeding could be reduced to a length of 5 or so, since the data in those columns is genrally only one character.
+::Most of the Lengths are ok, although the Hawaii, Introduced, Accidental, and Nonbreeding could be reduced to a length of 5 or so, since the data in those columns is generally only one character.
 ::The ID and English Name fields should be marked for Entry Required.
+::Right-click or control-click on the "ID" field name in the left hand column.  Select {{Menu|Primary Key}} from the contextual menu (it is the only option.)  A yellow key appears to the left of the field name.
 ::Once the fields are properly defined, click on {{Button|Create}}
 ''Note:  Make certain that all the field definitions are correct before clicking {{Button|Create}}.  Attempting to change field types, lengths, etc. later will destroy data in those fields.''
-:A {{Window|No primary key}} warning window will appear.  Click {{Button|No}}.  We will identify the primary key in a moment.
+:If you forget to set the primary key, a {{Window|No primary key}} warning window will appear.  You can click {{Button|Cancel}} and set the primary key as above, or you can click {{Button|No}} and identify the primary key in as follows:
-Once the new table has appeared in the {{Section|Tables}} Section of the document window, right-click or control-click on the new table.  Select {{Menu|Edit}} from the contextual menu.  The table opens in design view.
+::Once the new table has appeared in the {{Section|Tables}} Section of the document window, right-click or control-click on the new table.  Select {{Menu|Edit}} from the contextual menu.  The table opens in design view.
-:Click on the line containing the ID field.  A green flippy triangle appears in the "cell" to the left of the word ID
+::Click on the line containing the ID field.  A green flippy triangle appears in the "cell" to the left of the word ID
-:control-click or right-click on the green triangle and choose {{Menu|Primary Key}} from the contextual menu.  A key icon appears in the box
+::control-click or right-click on the green triangle and choose {{Menu|Primary Key}} from the contextual menu.  A key icon appears in the box
-:Click on the disk-drive icon in the toolbar to save your changes, then close the window.
+::Click on the disk-drive icon in the toolbar to save your changes, then close the window.
 You have successfully imported data from a spreadsheet.

Creating a Table From a Spreadsheet

From NeoWiki

Current revision

Contents

Importing Spreadsheets vs. Connecting to Spreadsheets

Formatting the Spreadsheet

Importing the Data

Related Wiki Articles

Related External Links

Views

Personal tools

Navigation

Search

Toolbox