Magento Business Intelligence Help Center

Using the File Uploader

BROUGHT TO YOU BY

Avatar

Magento BI is powerful not only because of its visualization features, but because it gives you the ability to put all of your data into a single data warehouse. Even data that lives outside your databases and integrations can be brought into Magento BI by using the File Upload tool in the Data Warehouse Manager. Note that you need Admin permissions to access use the File Uploader.

Let’s use ad campaigns as an example. If you’re running both online and offline campaigns, you can’t get the whole picture if you’re only analyzing data from an online integration. Uploading a spreadsheet with the offline campaign data allows you to analyze both sets of data and get a more robust understanding of your campaign performance.

In this article, we’ll cover the following:

  1. Restrictions and requirements
  2. Formatting data for upload
  3. Uploading data
  4. Updating/appending data to an existing table
  5. Data availability

Let’s get started! 

Restrictions and requirements

  1. The only supported format for file uploads is CSV, or comma separated values. If you’re working in Excel, you can use the Save As function to save the file in CSV format.
  2. CSV files must use UTF-8 encoding. The majority of the time, you won’t have to worry (or even think) about this. If you encounter this error while uploading a file, use these steps to troubleshoot.
  3. Files can’t be larger than 100MB. If the file is larger than this, separate the table into chunks and save them as individual files. You can use append the data after the initial file is loaded.
  4. All tables must have a primary key. There needs to be at least one column in your table that can be used as a primary key, or a unique identifier for each row in the table. A primary key can be as simple as adding a column that gives a number to each row, or can be two columns concatenated to make a column of unique values (e.g.: campaign name + date).

    If a column (or columns) are designated as unique but there are duplicates, the duplicate rows won’t be imported.

Formatting data for upload

Before you can upload your data into Magento BI, check that it's formatted according to the guidelines in this section.

To ensure that columns are labeled and imported properly, make sure the first row of your spreadsheet is a header that describes the data in each column.

Column names must be unique and contain only letters, numbers, spaces and these symbols: $%#/. If a column name contains a comma, it will be split into two columns when the file uploads. Additionally, we recommend that there be less than 85 columns in the file to optimize update speed.

Data with commas

Because files have to be in CSV format, the use of commas can cause problems with uploading data. CSV files use commas to indicate new values, therefore a column with a name like Campaigns, August will be read as two columns (Campaigns and August) instead of one, shifting all of your data over one row. We recommend avoiding commas wherever possible. You can use Data Preview to see if your data is displaying correctly once an update completes.

Dates

Any dataset that includes dates must use the standard date format (YYYY-MM-DD HH:MM:SS), or MM/DD/YYYY.

Decimal Numbers

Currency values should have the datatype Decimal Number selected, and these columns will automatically round to 2 decimal places in your data warehouse. If you don't want to round your decimal numbers, or have a degree of precision greater than this, you should select the Non-Currency Decimal Number datatype. 

Percentages

Percentages must be entered as decimals. For example:

Right: Wrong:
.05
.23
5%
23

Values with leading and/or trailing zeroes

Some values in your file - like ZIP codes and IDs - may begin or end with zeroes. To ensure the zeroes are properly retained and uploaded, you can change the formatting type (i.e. from number to text) or enforce number formatting.

Let’s use US ZIP codes as an example of how to change number formatting. In Excel, highlight the column containing ZIP codes and change the number format to “ZIP code.” You can also select a custom number format, and in the Type window, enter “00000”. Keep in mind this method could present problems if some codes are formatted as 00000 and others are 00000-0000.

The Type can be formatted differently to accommodate other data types, such as IDs. If an ID is 9 digits long, for example, the Type could be “000000000” or “000-000-000”. This would change “123456” to “000-123-456”.

For Google Docs and Apple Numbers resources, refer to the Related section at the end of this article.

Uploading data

Now that your spreadsheet is correctly formatted and Magento BI-friendly, let’s add it to your data warehouse.

  1. To get started, click Data > File Uploads.
  2. Click the Upload to New Table tab.
  3. Click the Choose File button and select the file. Click Open to start the upload.
  4. After the upload completes, you’ll see a list of the columns Magento BI found in your file. Check that the column names and datatypes are correct. Specifically, check that any date columns are being read as dates and not numbers. The datatype is extremely important, so don’t skip this step! 
  5. Select the column (or columns) that will make up the primary key for the table by using the checkboxes under the key icon.
  6. Name the table.
  7. Click the Save Table button.

That’s it! A Success! message will display at the top of the screen after your table is saved.

If you need a visual, here's a look at the whole process:


Uploaded tables display under the File Uploads section of the table list (in both the All Tables and Synced Tables options) in the Data Warehouse Manager:

Updating or appending data to an existing table

Got some new data to add to a file you’ve already uploaded? No problem - you can easily update and append data in Magento BI.

  1. To get started, click Manage Data > File Uploads.
  2. Click the Edit/Upload CSV to Existing Tables tab.
  3. In the dropdown menu, click the name of the table you want to update or append.
  4. The next step is to tell Magento BI how to handle duplicate rows. Use the dropdown to select your choice:

    Overwrite old row with new row
    This will overwrite existing data with new data if a row has the same primary key in both the existing table and the new file. This is the method to use for columns with values that change over time - for example, a Status column. Existing data will be overwritten and updated with the new data. Rows with primary keys not in the existing table will be added as new rows.

    Retain old row; discard new row
    This will cause new data to be ignored if a row has the same primary key in both the existing table and the new file.

    Purge all existing rows first and ignore duplicate keys within the file
    This will delete any existing data and replace it with the new data from the file. You should only use this option if you don’t need any of the data in the existing table.
  5. Click the Choose File button and select the file. Click Open to start the upload.
  6. After the upload completes, Magento BI will validate the data structure in the file.

That’s it! A Success! message will display at the top of the screen after your table is saved.

Data availability

Just like calculated columns, data from file uploads is available after the next update cycle completes. If an update was in progress during the file upload, the data won’t be available until after the next update. Once an update cycle is completed, you can navigate to the Data Preview tab in your data warehouse to ensure that the file uploaded correctly and data is displaying as expected.

Wrapping up

This article covered only the basics for using importing data, but we’re betting you want to do something a little more advanced. Check out the Related articles for guidance on formatting and importing financial, eCommerce, ad spend, and other types of data.

Additionally, file upload isn’t the only way to get your data into Magento BI. The Data Import API allows you to push arbitrary data into your Magento BI data warehouse.

Related

Google Docs and Apple Numbers

Was this article helpful?
1 out of 1 found this helpful

Comments

Powered by Zendesk