Cell Character Limits
When downloading data from Data & Insights, one of the most common tools used for analyzing the data is Microsoft Excel. Both .xlsx and .csv files have a limit of 32,767 characters per cell. Excel has a limit of 1,048,576 rows and 16,384 columns per sheet. CSV files can hold many more rows. You can read more about these limits and others from this Microsoft support article here.
However, Data & Insights datasets sometimes will contain parameters that exceed these limits. In the case that a cell exceeds the limit, Excel wraps the text down to a new line rather than losing the data. This can make it appear that the download of your data is corrupted. Rows beyond 1,048,576 will not be displayed, making the download appear incomplete.
In these cases, it is recommended that you open the file using a text editor or another program (such as Sublime Text or Notepad++) that does not have these limits. Another option is to filter the dataset before downloading to select the rows you are interested in, either in the user interface or by adding a filter to the export query (see below).
Limits on the Number of Rows
Data & Insights's export API by default limits an export to 1000 rows. If you would like to export more rows than 1000 from a dataset into CSV format, you can modify the URL that makes the API call to add a limit parameter. This limit can be smaller or larger than 1000.
Example: https://soda.demo.socrata.com/resource/4tka-6guv.csv?$limit=50
The key elements are https://{domain}/resource/{4X4 ID}.csv?$limit={number without punctuation]
You can be over-generous with your limit - so if your file has somewhere between 7000 and 8000 rows, you can just use $limit=10000. If you only want the first row of column headers to look at the schema, use $limit=1.
Find Out More
For more information on APIs, see How to Query More than 1000 Rows of a Dataset. Also, our API simple filters work with csv downloads as well! This page explains simple filters for SODA Consumer API calls. Just replace json with csv in the call URL to get the requested data in a csv file download.
Comments
Article is closed for comments.