So you'd like to pull data into the Numetric platform. You've come to the right place. This article will give you everything you need to get your data coming into your Numetric Warehouse.
(If this is your first time using Warehouse and you'd like some background on why Warehouse is setup the way it is, please check out this article.)
Bringing data into Warehouse happens in one of three ways, and we'll address each in turn:
- Uploading (or connecting via Dropbox or Google Drive) a "flat file" such as a CSV or Microsoft Excel file.
- Using one of our pre-built data connectors to connect to common business systems (including many popular database systems).
- Pushing data directly to the Numetric platform using our secure API.
Using CSV or Excel Files
CSV and Excel files are a common format for data you've exported from another system. It's simple to bring these types of files into Warehouse. You can do this either manually by uploading the file, or by placing the file in a Dropbox or Google Drive folder and then telling Warehouse to watch that file for changes. Before we go over these two approaches, we'll first provide a quick overview of the required format for these flat files.
Required format for CSV and Excel Files
The file structure for flat files is very simple. There are just three requirements, and these apply to both CSV and Excel files. If you've exported your data from another system, it's very likely to already adhere to these requirements, but we'll state them just in case:
- The file is in tabular format. This means the data is arranged in columns and rows.
- The file's first line contains the column or field names.
- Each subsequent line contains a separate record or row of data.
With each of these three properties, your to-be-imported data should look something like this (if it's a CSV file):
Note: A manual CSV or Excel file upload requires the imported file to be smaller than 1 GB.
Manually Uploading a Flat File
There are two ways to manually upload a flat file. Within Warehouse, navigate to the "Uploaded Files" Source. If you have not yet manually uploaded any files, you'll see a big area where you can drop your first file or click to be presented with a file browser for you to locate your file on your computer.
Alternatively, you'll see a list of files that you've already uploaded, along with a blue button in the top right the screen to add a new file. (At any time, you can drag a CSV or Excel file onto the Upload Files source page, and this will automatically initiate the file upload process.)
When you add a file, you'll be presented with a "New Table" box, where you can adjust the name of the source if you'd like, and where you'll be required to specify the primary key in the file being imported. If the file you're importing contains a multi-column key, you can click the "Add Column to Primary Key" button to the appropriate columns to the key.
If you are uploading an Excel file, you'll be asked for some additional information, such as the sheet name (leave blank if the data is in the first sheet). You can also specify the number of rows before and/or after the header row on the selected sheet, if such spacing applies. You'll still need to indicate a primary key as with CSV files.
After you're done, you can click "Create Table" and your new table will show up in the list of Uploaded Files in Warehouse.
Importing Flat Files from Cloud Storage
Manual upload of flat files is simple, but it's sometimes inconvenient to repeat that manual process every time you need to update the data from your external data source. A simple alternative that reduces the amount of manual work involved is to place the flat file on either Dropbox or Google Drive, and add the file as a source within Warehouse. This approach still requires you to manually place a new file inside the cloud folder each time it needs to be updated, but then Numetric will watch that file for changes, and bring in the new data when it sees an update.
(Please note that the files you add from cloud storage need to remain in the same place with the same filename as you update their data in order for Numetric to correctly import the changes.)
To add a cloud storage source to Warehouse, click the "+" sign above the list of sources.
Select either Dropbox or Google Drive from the "Add Source" window. You can then give your new source a name, and finish the connection process by clicking "Add Source" in the upper right.
It's now time to link your cloud storage account with Numetric. You can click on the Dropbox (or Google Drive) button, as shown below:
You will then be asked to login to the cloud storage service with the appropriate credentials. Once you have done so, you will be able to browse to the file you'd like to add to Warehouse. After you've found the file, you can click "Choose" to bring the file in as a table inside of the cloud storage source.
You'll then be asked to complete the same import steps as if you were manually uploading a flat file (e.g., designating a primary key), after which your new table will be listed as a table within the Dropbox source.
Using the Numetric API
Manual uploads provide a quick, convenient method for bringing your data into Numetric, but they do have some limitations, as discussed. One way to overcome these limitations is to instead us our secure, public API. This method allows you to push data to Numetric Warehouse as frequently as you'd like using a regular process (e.g., a scheduled script that pushes new data to Numetric's servers each night). It also allows you to import datasets that are larger than the 1GB limit imposed on manual file uploads.
Tables pushed to Numetric via the API will show up in Warehouse under the "Numetric API" source, and will behave in the same way as any other table added using other methods.
APIs are the most customizable option of importing a data set and keeping it updated. An API connection is most often used when data is stored in a SQL, Oracle or other database, and is the most popular method when a constant connection to the data is important. In summary, the Numetric API does require some technical know-how, but if this is your preferred method, we can help you get there.
The Numetric API is carefully documented at our developer portal, and rather than restating the instructions here, we'll simply direct you (or your favorite developer-friend) to the getting started page, where you'll go from zero to connected in just a few simple steps. And of course, if you don't have a developer or analyst handy or can't spare the resources, we'll be glad to help.
Option 3: Numetric Data Connectors
Numetric has developed automated Data Connectors that integrate with many popular business services. Examples include Zoho, Stripe, Google Analytics, and Quickbooks, and we're adding more all the time.
A connector is not as configurable as using the Numetric API, but they do allow a dataset to be automatically updated from an external data source. If you use one of these common business applications or services, connectors provide a simple way to ensure that your Numetric datasets always have the most current data.
Don't Stop Now!
Adding Sources of data to Warehouse is just the beginning of the data management process. Once you've added all of the sources of data from your various systems, databases, and files, the next step is to filter, clean, and transform the raw data available from those sources.