Excel, CSVs and JSONs

Import Microsft Excel, Comma Seperated Value and JSON files to Zing

Excel (XLS, XLSX) and CSV files can be read into Zing Data directly OR can be imported into a Google Sheet:

  • Direct Import is fast, good for small files, and useful if you don’t have a Google account or can’t use Google Sheets at your organization.
  • Google Sheets connection allows for dynamic alerts and updates to the data which will be immediately reflected in Zing.

Direct Import

Directly import CSVs and Excel files in Zing’s mobile apps (iOS, Android) and the web.

File types supported are:

  • csv
  • dbf
  • geojson
  • json
  • ndjson
  • xls
  • xlsx

Files up to 10 megabytes can be uploaded on the basic (free) plan, and larger upload sizes are available on the Standard, Pro and Enterprise plans.

To import a file, select the Excel or CSV option under ‘Add Datasource’ tab on mobile + web, or from the data sources tab on the Zing web console.

Formatting the file

In order to successfully import a file, the correct formatting should be applied to the data in the file before uploading.

Excel Files:

  • The first row must contain field names
  • Field names should not include special characters such as ()./\[\]\{\}?!@#$%^&\*()\_+-=.
  • There should not be any extra empty columns as these can result in the import to not work correctly.

JSON Files:

  • The file should contain a list of objects.
  • The property name should be enclosed in double quotes.
  • If the value is a string or a date, that should be enclosed in double quotes too.

An example of a correctly formatted JSON file would be as follows:

[
  { "Mean": 315.97, "Uncertainty": 0.12, "Year": "1959-11-01" },
  { "Mean": 316.91, "Uncertainty": 0.12, "Year": "1960-11-01" },
  { "Mean": 317.64, "Uncertainty": 0.12, "Year": "1961-11-01" },
  { "Mean": 318.45, "Uncertainty": 0.12, "Year": "1962-11-01" },
  { "Mean": 318.99, "Uncertainty": 0.12, "Year": "1963-11-01" },
  { "Mean": 319.62, "Uncertainty": 0.12, "Year": "1964-11-01" },
  { "Mean": 320.04, "Uncertainty": 0.12, "Year": "1965-11-01" }
]

We’ve added the ability to use Zing with an unlimited number of CSV files, join across them, and add/replace/delete them in much more flexible ways:

Append: Add new data to an existing table

  • Add new rows to an existing table
  • If the new CSV has columns that aren’t present in the existing table, those new columns will be added
  • This is the  equivalent of UNION ALL in SQL

Overwrite: Keep the same table name, but replace the data

  • All existing questions and dashboards connected to that table will work how you expect, but use the newly-uploaded data.
  • The previous data in the table is deleted

Multi-file upload: Add multiple CSV or Excel files at once

  • Upload up to 10 MB per file (free plan) or 100 MB per file (paid plans)

  • Optionally define join relationships between them to create a full queryable, multi-table data environment without needing to stand up a database

CSV and Excel files can co-exist and all live within a single data source

  • Previously, each CSV or Excel file imported was its own ‘data source’
  • Now, you can choose if you want to add a new file to an EXISTING data source, or instead create a NEW data source. Adding a file to an existing data source can make joining tables and appending / updating data easier.

Google Sheets Connection

Excel and comma separated value files are common ways to store data. You can import these files to Zing via the Google Sheet importer.

All of these file types are supported:

  • .xls (if newer than Microsoft® Office 95)
  • .xlsx
  • .xlsm
  • .xlt
  • .xltx
  • .xltm
  • .ods
  • .csv
  • .txt
  • .tsv
  • .tab

Import Steps

  1. First, import your (non-password-protected) file to Google Sheets:

    • Ensure the first row contains headers.
    • Every column to be imported must contain a header
    • Header names must be unique within your sheet
    • Exclude images, macros, tick-boxes, etc from your sheets prior to import

  2. Tap ‘share’ and set link visiblity set to ‘anyone with the link’. Once the sheet is imported you can revoke this permission if you wish.

    Note that unlike all other data sources in Zing, Google Sheets imports are not ’live’ connections. That is, Zing takes a ‘snapshot’ of the sheet at the time of import but subsequent changes to the sheet will not automatically update in Zing. If you’d really like it to be a live connection, let us know.

  3. Then follow the steps in the next section to add the Google Sheet to Zing.

Web Console

Add a Google Sheet as a data source from the web:

  1. Go to your Zing Data Web Console and ensure you are logged in

  2. Tap the ‘data sources’ tab

  3. Tap ‘Google Sheets’

  4. Enter your Google Sheet URL. Your sheet’s link visiblity must be set to ‘anyone with the link’. Once the sheet is imported, you can revoke this permission if you wish.

  5. Add the name of the table you’d like your sheet to appear as in Zing. This will be searchable in Zing so making this reflect what the table is about will make it easier to find and use.

  6. Tap ‘Save’

  7. Check for any warning messages which might indicate the sheet was incorrectly formatted

  • The data source you just added will be available to everybody in your organization.

    Note that Google Sheets has some size limitations on imports.