Prepare Excel and CSV data

Insights in ArcGIS Online
Insights in ArcGIS Enterprise
Insights desktop

ArcGIS Insights allows you to add tabular data from a Microsoft Excel spreadsheet or comma-separate value (.csv) file.

For Excel files, one dataset is added for each sheet, Excel table, or named range. Each Excel table or named range will be added as a separate dataset in Insights, including tables and named ranges on the same sheet.

For .csv files, one dataset is added for each sheet. .csv files do not support multiple tables per sheet. If your .csv file contains multiple tables per sheet, you should convert it to an Excel workbook or move the tables to their own sheets.

Note:

Commas must be used as the delimiters in .csv files. Other delimiters, such as tabs and semicolons, are not currently supported.

Supported Excel versions and file formats

The following versions and formats are supported:

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

Excel and CSV formatting best practices

Use the following guidelines to format your data in an Excel or .csv file:

  • Assign a header name to each column. Tables without headers will be assigned the column label as the header (A, B, C, and so on).
  • Remove unnecessary text and empty rows above your headers.
  • Remove empty rows between headings and the first row of data.
  • Remove or split all merged columns or rows, including merged headers.
  • Do not include multilevel headers.
  • Remove calculated columns or rows. New fields can be calculated after the data is added to Insights.
  • 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.
  • Format data from Excel workbooks in Excel tables. An Excel table will help ensure your data is loaded into Insights correctly.
  • Include the header row in all Excel tables and named ranges.
  • Provide a name for each sheet, Excel table, and named range. The sheet name and table name are used as the default dataset names in Insights.
  • Apply the appropriate cell formatting to columns to ensure that Insights appropriately recognizes numbers, percentages, strings, and date and time fields.
  • Ensure all the data in a column is a single type. For example, do not mix string and date values in a single column.
    Tip:

    Use find and replace to search for and remove values like No Data from number and date fields.

  • Ensure all dates in a column use a single format and the format is supported in Excel.

Limitations

The following limitations apply to Excel and .csv files:

  • In Insights in ArcGIS Online, 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.
  • #VALUE! errors in calculated fields are assigned null values when added to Insights.
  • .csv files do not support date or time formatting. If you have date or time fields in a .csv file, the fields will be added to Insights as a string field. You can change the field type from String to Date/Time from the data pane.

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: