Importing Data into a Table
In case you managed your data using spreadsheets before, you may load this data to your database. You can even do so on a regular basis, if your partner or customer prefers to work in Excel or other program.
When you import data from an external resource to a database table, the system either creates new records in this table or updates the existing ones. During the importing process all imported data are analyzed and compared to data of a database table (to be more precise, only data of the key columns are compared).
- If key column values of imported records and key column values of database table records match each other, corresponding table records will be updated with imported data.
- If the system does not find a match for a key column value of an imported record, a new record will be created in a database table.
To import data into a table:
Click the Setup link in the top right corner of the window.
Choose a table and click a corresponding tab at the top of the window.
From the menu in the main frame select Tables > Import data into table. The system allows you to add data from two possible sources:
- Import data from a .CSV (comma-delimited format) or .TSV (tab-delimited format) file.
- Copy and paste data from another program.
Importing Data from a File
If you want to import data from a file:
Select Import data from a file and click Next.
Click the Choose File button to specify a path to the file on your computer. If the first line in the file contains column names, leave the check box checked; click Next. The system will open the following form:
The form is represented as a table. Each table row contains information on a certain column of the .CSV (.TSV) file. Also there is the Column drop-down in every row. This drop-down allows you to specify a table column, where the data should be imported.
If the file contains more columns than the table comprises, you may choose one of the following:
- If you do not want to import data from a certain file column, select Do Not Import from the Column drop-down.
- If you want to create an additional column for imported data, select Create New Column from the drop-down. The system will expand the Type and Label fields. From the Type list select a type of a new column. In the Label field enter the name of a new column.
At the end of the form you can see the following section:
This section matters, if you need to update the existing data in the table. The Match Column option allows using any unique column to update existing record data. By default, table key column is used as matching column.
When finished, click Next to import data from a file into a table. Click Previous to go back to the previous step of the importing process. Click Cancel to cancel importing.
Verify if imported data is correct and click Finish to complete import. Incorrect records will not be imported into a table. If the system displays an error message, click Previous and correct erroneous records, after what try to import them again.
Copying and Pasting Data from Another Program
If you want to paste data from a spreadsheet program or Microsoft Project:
Select Copy and paste data from another program; click Next. The following form will be displayed:
Copy data from another program and paste it into the text field. If the first line contains column names, select the check box below the field; click Next to proceed to the next step of data importing:
Check that the system analyzed imported data correctly. Information on imported data is represented in the form of a table. Each table row contains information on a certain column you pasted. Also there is the Column drop-down in every row. This drop-down allows you to specify a table column, where the data can be imported.
If you pasted more columns than the table contains, you may choose one of the following:
- If you do not want to import data from a certain pasted column, select Do Not Import from the Column drop-down.
- If you want to create an additional column for imported data, select Create New Column from the drop-down. The system will expand the Type and Label fields. From the Type list select a type of a new column. In the Label field enter the name of a new column.
At the end of the form you can see the following section:
This section matters, if you need to update the existing data in the table. The Match Column option allows using any unique column to update existing record data. By default, table key column is used as matching column.
When finished, click Next to import data from the file into a table. Click Previous to go back to the previous step of the importing process. Click Cancel to cancel importing.
Verify if imported data is correct and click Finish to complete import. Incorrect records will not be imported into the table. If the system displays an error message, click Previous and correct erroneous records, after what try to import them again.
When importing data from a file, you should take into account that data in the imported file are treated as simple text. The following notes help to solve problems that may occur while importing:
1. When you create a column while importing, it acquires default settings ( for example, if you create a simple Numeric column, it will have no decimal places by default; if you create a Currency column, it will have two decimal places by default). To avoid mismatch, you can create columns and change their settings before the data import or import data, change columns settings, after what import data once again.
2. When the system processes imported data, it takes into account regional settings of the user. This especially concerns European and American dates and figure separators. For example, after processing 01/04/2006, an English user will get the 1st of April, while an American user will get the 4th of January. Errors concerned converting text into a date format, are most probably caused by regional settings.
3. All files are supposed to be in Unicode encoding format (UTF-16 or UTF-8). There may problems occur with additional characters that appear in various languages (for example, German ü, ö, French á, é, Spanish ñ, etc.). In case the imported file is stored in other than UTF-8 or UTF-16 encodings, such characters are ignored. If there arise problems with such symbols, save the file in the appropriate encoding format (you can use Notepad or Excel to perform the conversion).