ORDS How-To 09: Export a database

1. About this guide

This document is one of a series of How-To guides for the Online Research Database Service (ORDS). It will tell you how to export whole databases from the ORDS.

Contents

1.1. Pre-requisites

This How-To assumes that you have already registered to use the ORDS, and have either created or been given access to at least one project containing at least one database.

If this is not the case, please see the earlier guides in this series. These can be accessed via the main ORDS documentation page.

2. Exporting an ORDS database

The ORDS allows whole databases to be exported. This allows the data to be stored or used outside the ORDS: it might be used, for example:

  • To create a series of snapshots to record how a database has evolved over time
  • To share data with a colleague who is not an ORDS user
  • To create data files for import into another system
  • At the end of a project, to create a copy for archiving

Exporting a database is done from the database page.

To reach this, log in to the ORDS via https://app.ords.ox.ac.uk, You will see a list of your ORDS projects: click the name of the project which contains the relevant database. On the project page, click the database name to go to the database page.

Towards the bottom of the database page, you will see three panes – one for each of the three possible versions. Only the main version of an ORDS database can be exported. (If for any reason it proves imperative to export a test or milestone version of a database, please contact the ORDS help desk by emailing ords@it.ox.ac.uk, who will try to help.)

Click the Export database button in the Main database version information pane.

Database version information panes with Export button highlighted

You will be taken to the export page. This features a drop-down list of export formats.

Export page showing drop-down list

2.1. Database export formats

Three options are available:

  • Multiple CSV files
  • A single CSV file
  • An SQL database dump

Which format is most appropriate will depend on the purpose for which the exported file is intended.

2.1.1. Multiple CSV files

If the multiple CSV files option is selected, each table of the database will be exported as a separate CSV (comma-separated values) file. The names of the files will reflect the ORDS table names. The files will be enclosed in a compressed (zipped) folder, which will have the same name as the database.

Multiple CSV export

CSV is a file format which allows tabular data to be saved as plain text. This makes it a very flexible format: a wide range of software packages for statistical analysis or data visualisation (including Excel, SPSS, and Stata) can read or import data from CSV files. This means that this option may be useful for creating a set of data files for use in another application.

However, the CSV files will contain only the field names in each table, plus the contents of each record. They will not record the relationships between the tables, or any specific information about individual fields (e.g. data type, maximum field size, or which field is the primary key). ORDS comments on tables or fields are also not exported.

2.1.2. Single CSV file

If the single CSV file option is selected, the tables will appear one after another in a single file. The contents can be viewed using Microsoft Excel or a similar program.

Single CSV file export

This format provides a compact way of viewing the contents of the database which is easily readable by users. However, it is less useful for importing into other software packages for analysis or visualisation - because these packages will not generally be able to interpret the data as a series of discrete tables.

The file will contain only the table names, the field names in each table, and the contents of each record. As with the multiple CSV file option, it will not record the relationships between the tables, or any specific information about individual fields (e.g. data type, maximum field size, or which field is the primary key). ORDS comments on tables or fields are also not exported.

2.1.3. SQL database dump

The SQL database dump option exports the contents of the database in a format that can be imported into other database systems which use SQL.

SQL database dump export

This is also the best format to use if you wish to re-import a database into the ORDS - for example, to create a copy of a database in a different project.

Unlike the CSV export options, an SQL database dump does contain a record of the relationships between tables, plus field information such as data type, maximum field size, and which field is the primary key. However, ORDS comments on tables or fields are not exported.

2.1.4. Summary of export format features

  Multiple CSV files Single CSV file SQL database dump
Table names included Yes - as file names Yes - inside file Yes
Field names included Yes Yes Yes
Contents of records included Yes Yes Yes
Relationships between tables included No No Yes
Field information (e.g. data type, maximum size, primary key) included No No Yes
ORDS field and table comments included No No No
Easy to import into other software Yes - a wide range No Yes - in other
systems that use SQL
Easily human-readable Yes Yes No

2.2. Viewing or saving the exported database

Select the desired export option from the drop-down list, and click Export.

What happens next depends on which browser you are using. However, in general, you will be given the option of opening and/or saving the exported file(s).

2.2.1. Internet Explorer

In Internet Explorer, a bar will appear at the bottom of the window, asking whether you would like to open or save the exported database.

Internet Explorer export dialogue bar

If you click Save, the file will be saved in your default location for downloads. You can also click the down arrow at the right-hand side of the Save button to see further options, including Save as, which allows you to select your own location to save the file.

2.2.2. Firefox

In Firefox, you will see a dialogue box inviting you to select a program with which to open the file or folder, or to save it.

Firefox export dialogue

If you choose the latter, the exported database will be saved in your Downloads folder: if you wish to save it somewhere else, it may be easier to open it, and then use the Save as function (for single file exports), or copy and paste the files elsewhere (for multiple CSV files).

2.2.3. Chrome

In Chrome, you will see a dialogue box inviting you to select a location to save the exported database.

Chrome export dialogue

Once the database has been saved, you will be able to open it.

2.3. After exporting

To return to the database page after exporting a database, click Return to database page.

3. Other ways of exporting data from the ORDS

In addition to exporting whole databases, it is also possible to export query results and datasets (which are sets of previously saved query results) as CSV files. See section 5.1. of How to query data and section 3.4. of How to view, create, and share datasets for further details.

4. What next?

You may want to have a look at some of the other guides in this series. For a full list, visit the ORDS documentation home page.

You can also find out more about the ORDS service by visiting the ORDS home page, http://ords.ox.ac.uk/. If you have specific queries, you can contact the ORDS help desk by emailing ords@it.ox.ac.uk.

Service area: 

Written by IT Services. Latest revision 15 January 2016