You can import data that you get from other applications into your web
office database and export data from the database into other applications.
Importing copies existing data from a text file to your database; exporting
copies information from your database to a text file.
Importing existing data saves you the trouble of typing it in by hand.
You may need to import data only once, right before people start to use
the database. For example, if you create a Customer Relationship Management
(CRM) database, you could import existing customer information into it.
You may also need to regularly import data, for example, from a weekly
sales report.
Exporting data allows you to use information from your database in another
application outside your web office. For example, you may want to export
data so that you can open it in Microsoft ExcelŽ for charting or further
analysis. Exporting is also an easy way to create a local backup of the
data in your database.
Import and Export file formats
The Database application imports data from and exports data to text
files, using the two most common text file formats:
Comma separated values (CSV)
In this format, commas separate the field values. The names of CSV files
usually end with the extension .CSV. Many software applications, like
Microsoft ExcelŽ, open and save .CSV files.
Tab delimited
In this format, tabs separate the field values. The names of tab-delimited
files usually end with the extension .TXT. Many software applications,
like Excel and Microsoft WordŽ, open and save tab-delimited files.
Importing: Before you begin
Before you import information into your database, it's important to
step back and examine both the text file you're importing and the database
you're importing it into. You might be tempted to ignore the planning
phase and start importing right away, but remember that taking a little
time to plan your import now will probably save you a lot of time resolving
import errors later on.
Here are some things to think about before you import data from a text
file:
The file must be a CSV or tab-delimited text file,
and you must know which format it's in because you specify the format
during the import process.
The database fields must already be defined in
your web office database. Importing simply copies data from the text file
into fields in your database; it does not create any database fields for
you.
Importing is easier when the fields in the text
file are in the same order as the fields in the database; that way, there
are fewer fields to map.
If your database has required fields, the text
file must contain values for each of these fields.
If you are importing phone numbers that include
a country code, you must insert a double pipe || between the country code
and the rest of the number in your source text file. For example:
246||1-616-555-1234
The most important thing to think about prior to importing data is field mapping, which is the process
by which a column of data in the text file is associated, or mapped,
to a field in your database.
Here's an example of the contents of a simple CSV text file:
This file describes office supply purchases. The first row of the file
contains field names; this row is optional. The following rows contain
the data for each supply purchase. Suppose you want to import this file
into a database that contains the following fields:
A List field called "Supply Type"
A Date field called "Date of Purchase"
A Currency field called "Amount"
The correct field mapping associates the columns in the text file with
the fields in the database as follows:
Work out the correct field mapping before you start importing data.
You may wish to add fields to your web office database, or to reorder
the existing ones. The import process provides a simple interface that
lets you specify field mapping, if the suggested mapping is not correct.
Note that the suggested mapping assumes that the columns in the text file
are in the same order as the fields in the database.
Importing text files
To import a text file:
Open up the database into which you'd like to
import data.
On any database View page, click the Import button in the Command Bar. If
you're a database manager, you can also start from the Manage Database
page.
Type the name of the text file to import or click
the Browse... button to select
the file. If the first row of the file contains field names instead of
data, check the First row contains field
names option.
Select the format of the file you are importing
(CSV or tab delimited).
Click Next to go to the next page.
Look at the preview that shows how the data will
be imported. If data is not being mapped to the correct field, use the
drop-down lists to correct the mappings.
For example, suppose you are importing
into a CRM database. Before you map any fields, the suggested column mapping
might look like this:
The first row, across the very top of the preview, shows the field
names from the text file. This row would be blank if you did not check
the First row contains field names
option. The second row, with the drop-down lists, contains fields from
your database. Look down the columns to see the suggested mapping, along
with a preview of the first 3 rows of data using that mapping. Note that
while the preview shows only three rows of data from the text file, all
rows will be imported.
The suggested mapping in this example has three problems:
The last name in the import file is being
mapped into the middle name field in the database.
The work phone number in the import file is
being mapped into the category field in the database.
The company name in the import file is being
mapped into the first name field in the database.
You can correct these field mappings by selecting
the correct fields from the drop-down lists on the import page. Each column
on this page represents a column of data in the import file. Use the drop-down
list in each column to pick the database field this column maps to, that
is, which field it should be imported into. You can choose to not import
a column by selecting ignore from
the drop-down list. After you correct the field mapping, it looks like
this:
Click Import
to import the data.
Look carefully at the Import Results page, which
appears when importing is finished. The Database application may not have
imported all of your data because of errors that occurred during the import
process. You must resolve all import errors before the Database application
can properly import your data.
Resolving import errors
Importing is an all or nothing operation. If even one error occurs,
the Database application cancels the entire import operation. This ensures
the integrity of your data. Importing only some of the data could result
in inaccurate information in your database.
The Database application stops the import process after it encounters
10 errors; possibly leaving some errors unreported. When correcting an
import error, be sure to correct all occurrences of the error, not just
the reported ones.
To resolve import errors:
Make the appropriate change to the import file
or, occasionally, to the database design. Common error messages include:
Nothing
entered for required field. In this case, the database has a required
field, but the import file does not provide a value for that field. You
can resolve this error by providing a value in the import file, removing
the offending row from the import file, mapping this import column to
a non-required field, or changing the database design so that the field
is not required. Click here to learn
more about modifying fields.
The value
supplied is not valid for a ... field. In this case, the value
in the import file does not match the field type of the database field.
For example, trying to import non-numeric data into a number or currency
field, or trying to import dates which do not match the valid date formats
can cause this error. You can resolve this error by changing the value
in the import file to match the field type or by removing the offending
row from the import file.
The data
in this field is not unique. In this case, the field has the Unique
property turned on and the data you are trying to import has duplicate
values in it. You can resolve this error by changing the data to be unique
or by turning off the Unique property for the field.
Click here to learn more about the Unique
property.
Occasionally, import may incorrectly truncate
data being imported into a text field. Though rare, this problem can occur
in two cases: when data that appears to be numeric is imported into a
Text field, and when textual data that is less than 256 characters is
imported into a Text Multi-Line field.
Cause: This problem occurs when
import inspects the first three rows of data in the file to determine
what type of data is in the fields. If the data in these rows is misleading,
import can make an incorrect data type determination.
For example, suppose that part of your import file is a text field called
"Software Version" and that the field values in the first three
rows are:
6.01
6.03.02.05
6.01
Since two of the three rows are numeric, 6.01,
import is fooled into thinking this field is numeric. When the import
is performed, the actual numeric data, 6.01,
will import correctly and be converted into textual data, "6.01",
as desired. However, non-numeric data, 6.03.02.05,
will be truncated into numeric data and then converted into text, "6.03".
Workaround You can work around
this import limitation by changing the data in the first three rows such
that import correctly determines this field is textual and not numeric.
The easiest way to do this is by enclosing the numeric data in quotation
marks. That is, change 6.01 to
"6.01" in the text file.
If you do this for the first three rows, import will bring the data in
as text and no truncation will occur. An alternative work around is to
add at least two dummy rows of data to the start of the file, which uses
textual data for the field. If you use this approach, be sure to delete
the dummy data rows after the import is finished.
This problem can also occur when importing into a Text Multi-Line field.
If the first three rows of data for the field contain two values whose
length is less than 256 characters, import will determine that the data
type is an ordinary Text field, and truncate the data to 255 characters
on import. The same type of workarounds can be used. That is, either change
the real data in the first three rows so that they contain more than 256
characters, or insert two dummy rows with data values greater than 256
characters in length.
This type of import problem can be tricky to diagnose and fix. We encourage
you to contact the Help Desk for assistance. Support information is available
by accessing Help > Contact Us
in the Menu.
After correcting an error, check the rest of the
import file to see if other instances of the same error occur and correct
those as well. For example, provide values for required fields in all
rows.
After you correct all the reported import errors,
try importing the file again. It can be difficult to get import to work
perfectly the first time, but once you resolve the initial import errors,
subsequent import operations should go smoothly.
Importing data into compound fields
A compound field is one that consists of several parts. For example,
a Name field consists of three parts: First Name, Middle Name, and Last
Name. Importing data into a compound field requires a little planning.
The Database application only imports data into the individual parts of
a compound field. This means that you can import "Peter" into
the First Name part and "Foley" into the Last Name part. However,
you can't import "Peter Foley" into the entire Name field.
If the data you wish to import is already divided up into a separate
column for each part, simply use the import interface to map the correct
column into the correct part. If the data is not divided up into separate
columns, see if the program you used to create the text file can export
the data into individual parts or columns. Failing that, you can use a
spreadsheet program like Microsoft Excel to split up the data for you.
Open your text file in Excel, and then use the Data >> Text to Columns
command to split the data into separate columns.
Importing data into other field types
You can import data into any of the field types except for the following:
Built-In (Created, Modified)
Calculated
Contact List
File Attachment
Image
Member List
Unique ID
The following table outlines some special restrictions on imported data
for certain field types:
Field Type
Restrictions on column data in the import
file
Address
Since this is a compound field, you can import only into individual
Address parts, such as Street or City.
Click here
to learn more about importing data into compound fields.
Checkbox
The data values must be either "True" or "False".
Import will only accept either exactly "True" or "False".
Be sure that the case is correct. If the record contains anything other
than "True", the checkbox value will import as "False".
Please note that Microsoft Excel may convert values of "True"
to "TRUE", which causes a problem on import.
Currency
Must contain a valid currency value. The currency symbol is optional,
but if it is supplied, it must be the US Dollar symbol ($). The decimal
separator must be a period (.) and the thousands separator must be a comma
(,). If Min and Max Values are defined for the field, the value must be
within that range.
Database Relation
Should contain a value that exists in the source field of the Source
Database. If it does not, and the database field is not required, then
the data is imported, but the value of this field will be left blank.
If this field is required, the Database application generates an import
error and no data is imported.
Date
Must contain a valid date. The valid date formats are:
Format--------------------->Example
mm/dd/yy------------------>01/25/99
dd/mm/yy------------------>25/1/99
mm/dd/yyyy--------------->01/25/1999
dd/mm/yyyy--------------->25/1/1999
dd Mon yyyy-------------->25 Jan 1999
dd Mon--------------------->25 Jan
Month dd, yyyy-----------> January 25,
1999
Day, dd Mon yyyy--------> Monday, 25 Jan
1999
Day, Month dd, yyyy-----> Monday, January
25, 1999
For date values that are in slash formats, import examines all the data
values in the column to determine which format they are in. Note that
the display format of the field has no effect on data import. In most
cases, import is able to determine the format correctly as there is a
day value greater than 12. For example, "03/17/02" must be in
"mm/dd/yy" format (March 17, 2003), as there is no month 17.
Similarly, "27/2/87" is imported as February 27, 1987. Once
import has determined the format of the column, by detecting a day value
greater than 12, all dates in that column are interpreted the same way.
Do not mix date formats within the same column.
If import is unable to determine which slash format to use (in cases
where all the day values are 12 or less), the values are imported as month
first (mm/dd) values. If this is incorrect, you can work around the problem
by inserting a dummy row of data at the top of the import file (after
the column title row). In that row, use the "dd/mm" format with
a day value greater than 12. After the import completes, delete the dummy
record from the database.
Date and Time
Must contain a valid date and time. The valid date formats are:
Format--------------------->Example
mm/dd/yy------------------>01/25/99
dd/mm/yy------------------>25/1/99
mm/dd/yyyy--------------->01/25/1999
dd/mm/yyyy--------------->25/1/1999
dd Mon yyyy-------------->25 Jan 1999
dd Mon--------------------->25 Jan
Month dd, yyyy-----------> January 25,
1999
Day, dd Mon yyyy--------> Monday, 25 Jan
1999
Day, Month dd, yyyy-----> Monday, January
25, 1999
See the Date section above for information about importing dates in
slash format.
The valid time formats are:
12-hour (10:22 PM)
24-hour (22:22)
Email Address
Must contain a properly formatted email address, for example, feedback@yoursite.com
Group List
Should contain a valid Group name in your site. If it does not, and
the database field is not required, then the data is imported, but the
value of this field will be left blank. If this field is required, the
Database application generates an import error and no data is imported.
The comparison with existing group names is case sensitive; "sales"
will not match "Sales".
List
Should contain a value that exists in the database. For example, if
you are importing into a Category field, the import file should contain
categories that already exist in the database. If it does not, and the
database field is not required, then the data is imported, but the value
of this field is left blank. If this field is required, the Database application
generates an import error and no data is imported.
Name
Since this is a compound field, you can only import into individual
Name parts: First Name, Middle Name, or Last Name. Click
here
to learn more about importing data into compound fields.
Number
Must contain a numerical value. If Min and Max Values are defined for
the field, the value must be within that range. The decimal separator
must be a period (.) and the thousands separator must be a comma (,).
Phone Number
The U.S. phone numbers only property has no effect during data import.
Phone numbers in any format can be imported. Although the U.S. phone numbers
only property makes Phone Number fields appear to be compound fields,
they are not. Therefore, you can import any value into this field.
If you are importing phone numbers that include a country code, you
must insert a double pipe || between the country code and the rest of
the number in your source text file. For example:
246||1-616-555-1234
Text
Must not be longer than the Maximum # of Characters defined for the
field.
Text Multi-Line
Must not be longer than 8192 characters. The data you import into a
Text Multi-Line field can include special characters such as tabs, commas,
or carriage returns, but make sure that the entire value that contains
the special characters is enclosed with quotation marks ("").
Most programs that create text files, in both CSV and tab-delimited formats,
automatically place the quotation marks for you.
Time
Must contain a valid time. The valid time formats are:
12-hour (10:22 PM)
24-hour (22:22)
Web Link
Must contain a properly formatted web address such as www.microsoft.com.
You can export the data in any view by clicking the Export button at the top of the view.
The view determines which data is exported, and how that data is displayed.
If the data looks right in the view, it will look right in the exported
text file.
The export operation produces a text file of the exported data. In order
to save this file, your web browser downloads the file to your computer
and asks whether you want to open the file or save it. If you choose to
save it, the browser also asks where to save the file on your computer.
To export a view to a text file:
Select the view to export from the View
by list. Remember, only the data displayed in the view will be
exported.
Click Export.
On the Export page, verify that this is the view
you want to export.
Pick the text file format to export to.
To learn more about text file formats, click
here.
In the File Download dialog box, choose whether
to open the file or save it. In most cases, you will want to save the
file.
If you chose to save the file, in the Save As
dialog box, specify the name of the file and where on your computer you
want to save it. Then, click Save.
The web browser downloads the exported text file
to your computer. A progress window is displayed during the file download.
The export operation is complete when the progress window disappears.
The file is saved in the location you specified in step 6. If you chose
to open the file, the contents of the file appear in a window.
Click Done
to close the Export page.
Tip: Export only exports the
actual data records in the view. Export does not export:
Group header rows (the rows that display the group
values in a grouped view). If you wish to include the grouped field in
your exported file, include the field as a column in the view prior to
export.
Totals and subtotals.
Using export to back up a database
All the data on your site, including data in your databases, is backed
up daily as part of the service. However, these backups are not accessible
to you, though you can have data restored from them. If your database
contains important information, you might want to use the export functionality
to create your own local backup of the data.
You can use the export operation to back up a database by creating a
view especially for that purpose. Create a view that includes all the
fields in the database and name it something you are likely to remember,
such as Backup. Be sure that the view does not have any filters defined,
as a filter might exclude records from the backup. Then to back up, simply
select the view and click the export
button. In some cases, you may not wish to back up all the fields in the
database, as only some fields may contain critical information. In those
cases, adjust the view to only include the fields you wish to back up.
Tip: If you are backing up a
database that includes a database relation field, be sure to also back
up the related database.
Important: The Database application
does not export attached files or images, so these fields are not backed
up when you export.
