Skip To Content

Prepare your Excel and CSV data

Insights for ArcGIS allows you to add data from a Microsoft Excel spreadsheet or comma-separate value (CSV) file whether your data is captured in Excel tables, named ranges, or a flat format.

A single Excel file can add multiple datasets to work with or a single dataset, depending on the format of your data in Excel. When adding data to your workbook page, each occurrence of an Excel table, or a named range is added as a single dataset. If there are multiple Excel tables or multiple named ranges on a single Excel sheet, then you will see multiple datasets for each sheet in Insights. On the other hand, if your data is in a flat format, such as a CSV, then each sheet containing data is captured as a single dataset.

Note:

CSV files only support one table per sheet. If your CSV file contains multiple tables per sheet, you should convert it to an Excel workbook or move the extra tables to their own sheets.

Supported Excel versions and file formats

  • Microsoft Excel 2007 and later
  • .xlsx or .csv files only
  • Pivot tables are not supported

Insert a table

A table can be used in an Excel workbook to ensure your data is loaded into Insights correctly. For more information on how to insert a table in an Excel workbook, see Create and format tables from Microsoft Office support.

Before you create a table, make sure your data fits the following guidelines:

  • Each column has a heading.
  • There are no empty cells between the headings and the first row of data.
  • The table does not contain calculated columns or rows. Any calculated fields must be removed before the data is added to Insights. New fields can be calculated after the data is added to Insights.
  • The data does not include any merged rows or columns.

Note:

Excel tables cannot be inserted into CSV files.

You can create more than one table on each page of an Excel workbook. Each table will be added to Insights as a separate dataset.

Excel and CSV formatting best practices

Whether your data is in an Excel table, a named range, or a flat format (for example, a CSV), keep the following in mind:

  • Excel and CSV files can be no larger than 100 MB. If you add an Excel or CSV file that is larger than 100 MB you will receive an error message.
  • Remove rows that provide an aggregated total (for example, GrandTotal). Otherwise, the aggregated total is imported as a data record that will lead to inaccurate analysis results.
    Note:

    #VALUE! errors in calculated fields are assigned null values when added to Insights.

  • Remove unnecessary text and empty rows above your headers.
  • Avoid merged cells in your headers, and limit headers to a single row.
  • If your Excel or CSV file has more than one sheet, provide a unique name for each sheet. If your Excel sheet has more than one table, name each table. Sheet and table names help you recognize your data more easily in Insights. If you don't name your sheets and tables, datasets are provided with a default name indicating the sheet number and table number. For example, Sheet1.Table1.
    Note:

    CSV files only support one table per sheet. If your CSV file contains multiple tables per sheet, you should convert it to an Excel workbook or move the extra tables to their own sheets.

  • Make sure your columns have headers. Otherwise, default headers will be used, making it difficult to recognize fields in Insights.
  • Apply the appropriate cell formatting to columns to ensure that Insights appropriately recognizes numbers, percentages, strings, and date/time fields. For example, a column formatted as a percentage in Excel is identified as a rate/ratio field in Insights.
  • Make sure the records in a column are valid for the cell formatting you apply in Excel. Otherwise, Insights may assign the wrong field role. For example, if you applied the Date format to a column in Excel, but the cells in the column contain invalid date format values, the field likely will be assigned the string field role.

Next steps

Now that you have prepared your Excel and CSV data, you are ready to use it in Insights. The following resources will help you to get started: