Help and Support > Table of Contents > How to import data

Importing data into Datamartist

There are two key types of datasets

1. External Data

In this case a link is established pointing to a database table, or a file and data is imported each time datamartist refreshes the link. This is how you get most data into Datamartist- you can pull in tables from lots of different databases, text files and excel files.

2. Internal Data

In this case the data has no connection to the original source and is inside Datamartist. It is copied into Datamartist and is saved with the .DMC file. This type of data set is size limited to a quarter of a million of values per dataset, and if you have a lot of these it will increase the size of your DMC file. But it does let you create small datasets and reference sets instantly. It's a fast way to build little test sets or dimensional tables.

Importing External Data

Drag and drop

One easy way to get data from text files or Excel files is to simply drag one or more files from the Windows file explorer directly onto the Datamartist canvas. This will import the files, and create default import blocks on the canvas. To edit the configuration (change text import settings, or excel ranges for example) you can select the Data sources tab, and select the data source, or simply click on any input block using that data source, and the Data sources tab will open, and select the source for editing.

Using the Data sources tab

This will bring you to the data sources repository, and by adding a new data set you can import data from a number of different sources. To add a data source use the menu at the top.

import-data-data-sources-tab (50K)

The data sources repository holds all the data sets that are available to you on the data canvases, and lets you reuse a given data set as many times as needed by simply adding data table blocks (drag and drop the table from the Data sources tab onto the canvas and a block will be created. In this way, if you change a given data source, it will be changed throughout your Datamartist canvases.

Connecting to an ORACLE database

You can connect to an Oracle database in one of two ways- either by specifying an alias that is included in your tnsnames.ora file, or by providing the detailed information regarding the server, port, etc.

data-import-oracle-connection-dialog (63K)

To use a TNSNAMES.ORA file, a copy of the file should be placed directly into the datamartist program files folder- this is often "C:\Program Files\nModal Solutions Inc\Datamartist" Datamartist brings its own Oracle client assemblies with it, so there will not be any version issues, however it will look first in this program folder to find the tnsnames.ora file. By putting a copy there, you can use any descriptions for data sources.

SQL Server Connection format

If SQL Server Table is selected, the connection dialog for an SQL Server database will be presented;

Data-Import-SQL-Server-Connection (36K)

There are various formats that can be used in the SERVER field to specify the SQL Server database you wish to connect to.

IP Address or server name and Port

x.x.x.x,myport

myservername,myport


Local Machine- SQL Server or SQL Express

.\ for SQL server installed on the same machine.

.\SQLExpress for SQL server express edition installed on the same machine.

Database Security

You can use your windows login, or enter a specific SQL username and password. The SQL Server instance being connected to must allow SQL Server authentication for the second option to work.

Selecting a Database and Table

Once you have entered the server and specified the login, then you can select which database within the SQL Server instance using the database combobox.

Once the database is selected, you will see a dialog which allows you to select the table or view to import, or lets you pick the option of using an SQL Query to fetch the data;

import-data-table-selection (44K)

If you uncheck the "Include all columns" check box, you will be prompted for each table and view selected to specify which columns to include- this can be very useful if you have wide tables with columns that are not of interest. From the data store repository you can modify the data sources, convert data types,add calculated columns and reorder the colums. This of course can also be done at any point in the datamartist canvas by using a calculation block.

Selecting the "Use SQL Query" tab will let you define, and test an SQL Query to get the data set from the database.

use-sql-query-dialog (91K)

And finally, your block appears on the canvas ready to go. You can edit the SQL any time by selecting the source in the Data sources tab.

import-data-edit-table-sql (71K)

Creating an internal data set

Copy and paste data onto the canvas

At any point you can copy data directly onto the datamartist canvas from Excel, or from any data grid within Datamartist itself. When you do this an Internal data set block will be created. You can paste onto the canvas by right clicking, or by clicking on the canvas and using the CTRL-V

import-data-excel-copy-1 (106K) import-data-excel-copy-2 (69K)

Once the data is pasted into the canvas, an internal data set block is created, and editing this block provides the ability to modify the data set and its structure.

import-data-excel-copy-3 (63K) copy-from-excel-4-edit-right-click (49K) copy-from-excel-5-edit-cells (45K)