Troubleshooting Warnings and Errors on Data & Insights Management Experience Import

The Data & Insights Management Experience includes several connectors for ease of loading data from a wide range of sources. Occasionally, however, you may encounter warnings or errors upon attempting to import data from a file, URL, or Gateway. This article lists some of the most common issues you may encounter and how to resolve them. If you are still unable to resolve the issue, please contact datainsights-support@tylertech.com and we will help you determine why the source data is not importing.

The most common import issues are related to the data format, so please make sure to read "Importing, DataTypes, and You!" before importing your data. Our article on “Using the Socrata Data Management Experience” also provides thorough explanations of the various ways to import and review your data before publishing it as a dataset.

The issues are separated into Warnings and Errors. The majority of these can occur with tabular datasets, while some are specific to geospatial data. These issues will be labeled accordingly below.

Please note: Many of these warnings and errors require manipulation or cleaning of source data in order to be resolved. Please make sure that you have a backup copy of your data before modifying it.

Warnings

DATASETS

This can happen when the number of columns in the source data does not match the number of columns in the existing data that is being replaced, or when the column names in the source file and existing data do not all match.

For example, the “Additional Column” below isn’t initially accepted when we try to upload this Excel file to a dataset that did not originally have this column.

1.png

The “1 ignored column” warning at the bottom of the screen indicates this to us, when we click on the question mark icon.

2.png

3.png

How to Proceed: Please add the column(s) that exist in the source data and are not yet in the existing data. You can do this in the Data Management Experience by navigating to “Review Data” and clicking the “+ Add Column” button, for each column you wish to add. (See the following article on Adding a New Column for more information.)

Under “Source Column,” please make sure to select the name of the column that you want to add from your new, replacement data source. This dropdown list is populated with the column names in the incoming source file you currently trying to import.

4.png

Then, click “Add Column” and “Done”; the values of the new column(s) in the source data should now appear. Click “Update” to publish the addition of the new column(s) and the associated record values.

(See our article on How to Append Data for more information.)

5.png

6.png

How to Proceed: The process for fixing an Append issue is relatively similar to what you would do in a Replace update (see above), but with some important differences. We can use the same dataset but, as indicated in the warning message instructions above, we need to discard the draft we are currently working on and create a new draft. Please go to the ellipse (…) menu at the top of the Primer page and select “Delete this draft.” Once you are back at the published dataset, click the “Edit” button to create a new draft. Then go to “Review Data” and select “+ Add Column” on the left.

Like the above remediation steps for a Replace update, we need to add the new column(s). But please note, we want to select “No Source Column“ this time. Then click “Add Column” and, after you’ve added each of the columns you need, click “Done.”

7.png

Next, click “Update” to publish the addition of these columns. We need to do this before attempting to import the data we wish to append.

From the published dataset’s Primer page, please click “Edit” again and then select Add Data->Append. Navigate to the data you wish to append.

Next, we want to go to “Review Data” and then to the "Data Transforms” section for the column(s) you have added. Once you’re there, replace “null” with “`new_column`” where new_column is the source data column name of your column. Then click “Run” and “Done.”

8.png

The source data column is now mapped to dataset and can now proceed by clicking “Update” to publish your data once again.

(See our article on Schema Mapping for more information.)

If you see an error similar to the one below, it may be because the Data Management Experience doesn’t find any columns in common between your existing dataset and the one you are importing as an append or a replace.

9.png

How to Proceed: Please verify that the dataset you selected to upload (for an append or replace) is the one you intended, and that the column names and general schema match that of the existing dataset. In certain situations, the source data you are uploading may have the same general structure and purpose as the existing one you are replacing or appending to, but have slightly different column names. If this is the case, you can map the old column names to their corresponding new names, as shown below.

10.png

 

GEOSPATIAL FILES

This often occurs for JSON files that aren’t in a geospatial format. The import tool only supports importing of JSON data whose contents are in GeoJSON format.

11.png

How to Proceed: If possible, check if the data you wish to upload is available in a different file format like tabular form (CSV, Excel, etc.) or a different geospatial format (.zip shapefile, KMZ, KML, or the GeoJSON mentioned above).

Please see our article on Importing, DataTypes, and You! for more information.

12.png

How to Proceed: Please check if the data you wish to import is available from a URL endpoint that is in one of the listed parsable formats shown above.

 

Errors

DATASETS

(Please see the Import Errors section in our article on Using the Socrata Data Management Experience for more information.)

13.png

This is perhaps the most-common type of error encountered with the Data Management Experience. Please notice above that the second column has “Number” selected as the datatype, but rows 5, 6, and 7 contain text. Similarly, the third column has “Point” chosen as the datatype, but the same rows don’t contain content that can be converted to a Point. This is why they give data validation errors.

How to proceed - two options:
1) To fix the source data (recommended)
A) Depending on the nature and quantity of the type mismatches, you may be able to fix the errors manually. In the example above, since there are only three records with errors, and they contain meaningful (albeit incorrectly formatted) data, we can update the values by double-clicking on each erroneous cell and typing the correct values.

14.png

15.png

B) In other cases, there will be too many errors to fix manually. You’ll want to reach out to the person or team who provided you with the dataset. It may be helpful to share with them the rows that failed to import. You can export these to a file as shown in the following image.

17.png

2) To proceed with the import without fixing the erroneous cells

A) Discard the entirety of the rows that had errors -If you click “Done” and proceed to publish the dataset, these rows will be removed entirely.

B) Discard just the cells that had the error (and keep the rest of the cells in the row that imported correctly)

As shown below, you can do this by hovering over the “# Errors total” message in red at the top of the column, and clicking “ignore errors.”

fcdd4d96-1b00-4ba2-8e4e-33872bd3b0d7.png

This error often occurs if the URL you entered does not point to a dataset that the platform is able to import.

(In the example below, the URL erroneously ends in “.cv”; instead, it should end in “.csv”.)

19.png

How to Proceed: Please confirm that the URL points to a valid API or web-hosted dataset.

Please also read the Importing, DataTypes, and You! article for more information about which data formats the platform supports.

(Please see our article on Creating Georeference Columns in the Socrata Data Management Experience for more information.)

This usually occurs when a value in the location column is not correctly formatted or recognized as a U.S. address.

18.png

How to Proceed:Here are a couple of things you can check.
- Are your ZIP codes formatted correctly? We accept 5- or 9-digit zip codes.
- If you used the “Address (separated)” input form, please confirm that you selected the proper columns for each of the fields (Street Address, City, etc.).

Sometimes some of your rows will have no address and the import tool will give the error, but as long as you do not expect to have an address there you can safely ignore this error. If you want to keep the rows, however, please make sure to select “Treat value as empty” instead of “Skip Row” in the “Add georeference” screen.

21.png

This will occur on a CSV file when one or more rows are missing some of the commas that are used to delimit them. Since CSV files use commas between their values as separators, the delineation between their columns may become corrupted if commas are added or removed.

How to Proceed: Please check if your source data contains any special characters. If so, these may need to be escaped (having a quotation mark or backslash before them, to indicate that you wish to render this character in the dataset). This will be a bigger issue when the line that is incorrectly escaped has a comma or a line break in it.

22.png

This error can occur for a handful of reasons, but often the issue is that the source data has multiple layers of column headings and some of the columns are merged together.

23.png

In the Excel source table, we can see that row 1, with “2020” and “Plate Type,” is not necessary, since we know the entire table is for 2020 and all the column headings starting with “Amateur Radio” are license-plate types.

24.png

How to Proceed: In this example, and in similar situations, we recommend removing row 1 from the source data in Excel prior to importing. Then the proper headers will be used instead and the data will import correctly.

Before deleting the row, please consider saving a separate copy of the original source table if you need to refer to it later.

25.png

26.png

Excel can sometimes format data so that what is displayed in the cell is different from what the actual value is. When you import that Excel file into Socrata, we try to account for most data types, but there may be an error due to different versions of Excel.

How to Proceed: If there is a column that isn’t importing correctly, check what the data type is in Excel and switch to a more standard format such as Plain Text or Number.

 

GEOSPATIAL FILES

Check to ensure that the shapefile follows our specified data type format.

This can occasionally be a result of a temporary error on Socrata’s end; try the import later in the day, and if it still is not working, contact datainsights-support@tylertech.com.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

0 comments

Article is closed for comments.