data-liberation-front / csvlint.io Goto Github PK

Part of theodi/shared#153

I just came across this in the schemas_controller:

schemas = Schema.all
@schemas = Kaminari.paginate_array(schemas).page(params[:page])

I think paginate_array is for when you have a simple array object, with a AR query I think you could have

@schemas = Schema.all.page params[:page]

Which would have the advantage of calling limit/skip on the Schema query rather than bringing in all the items each time.

If a schema is entered, but then the checkbox is unticked, the schema is still used as the form is still filled in behind the scenes.

http://www.epsrc.ac.uk/SiteCollectionDocuments/Publications/corporate/SeniorStaffAndPay31Mar2012.csv

http://csvlint.io/validation/530b111263737676e91d0000 and http://media.nhschoices.nhs.uk/data/foi/Dentists.csv

On homepage, and perhaps README on /about

e.g. take a CKAN url and validate all the CSVs in that dataset and then cross validate them.
Imagine you have a series of monthly CSVs, it would take a while to validate the whole lot. Further they may be valid individually, but as a collection the column titles are inconsistent etc.
Would be great to be able to detect errors in a dataset.

Because it's a pain in the bum.

https://codeclimate.com/repos/52e0e458e30ba02258007bdf/security/1668984cf5838d2d191d64d22507237e

CSVs that 404 currently produce a broken image in the list view. Perhaps a "not found" badge would be better?

Part of theodi/shared#155

I've had several occasion where the returned error complained about the header, I'm not sure it was always right.

An example might be:
https://directgovinnovate.s3.amazonaws.com/ext-search/200311.csv

404s are currently swallowed up: user redirected to homepage.

Found because of broken link here: http://data.gov.uk/dataset/bona-vacantia-estates-advertisements to:

https://www.gov.uk/government/uploads/system/uploads/attachment_data/file/270310/UnclaimedEstatesList.csv

I think the colours in the table have not been implemented quite right. Should be a grey background if the number is 0; otherwise the background should be the appropriate colour.

So in this example:
errors/context should be red background
warnings/structure should be grey background
warnings/schema should be grey background
messages/structure should be grey background
messages/schema should be grey background

1. Possible text:

Check your CSV files with CSVLint

CSV looks easy, but it can be hard to make a CSV file that other people can read easily. CSVLint helps you to check that your CSV file is readable. And you can use it to check whether it contains the columns and types of values that it should.

Just enter the location of the file you want to check, or upload it. If you have a schema which describes the contents of the CSV file, you can also give its URL or upload it. Read more...

1. Maybe number the steps 1 (enter CSV), 2 (enter schema if you want), 3 (hit the validate button).

Validating http://certificate.theodi.org/status.csv causes an error with a forbidden redirect to the HTTPS version.

In order to cope with big files, we need to:

• Make sure we only revalidate every so often (define a minimum time period of validity)
• Validate using a background job so we don't block the UI

http://csvlint.io/validation/530b5aa263737633f8000000 gives duplicate column name errors, as there's a title line on the first row, then a blank row, followed by headers.

It seems to be a common issue that people put information about the CSV in the first line (or few lines). Should we generate a separate error for this if the first few lines have one column? Something which in the front end would say:

"Your CSV seems to contain unstructured text at the beginning of the file, it is important that your CSV only contains structured data - any background information or metadata should be included on a referring web page or accompanying document"

If a server doesn't support if-modified-since, then it will be revalidated every time. We should make it only revalidate every few hours, perhaps, with a manual 'revalidate' button to override.

And that they can upload files if they want them to remain private.

Why

http://csvlint.io/validation/list

rather than

http://csvlint.io/validation

and why does the latter URL just give you the home page?

Need some feedback to help user identify how to fix up some of the errors/warnings.

We might be able to qualify this based on some additional information in the headers. For example, I just came across an interesting one testing the Land Registry CSV data. This file reports wrong content-type:

http://publicdata.landregistry.gov.uk/market-trend-data/price-paid-data/b/pp-monthly-update-new-version.csv

Its served as application/octet-stream. Doing a HEAD request I can see the Server is being reported as AmazonS3. We could provide some specific guidance here, e.g. that application/octet-stream is the default for S3 unless you specify content type during upload.

When viewing a validation, the revalidation form doesn't work.

The 'browse' button somehow takes over everything,

Possibly using this? https://github.com/Jahdrien/FileReader

Validating https://certificate.theodi.org/status.csv gives an invalid SSL cert error. The app probably needs updated public certs or something. I forget.

Nothing terrifying, but would be good to clean up.

http://csvlint.io/validation/53283a016373767dab810000
See screenshot – think we were going to add some kind of shortening magic so the urls would read:
www.gov.uk...Spend_over__25K__2013_-_CSV.csv

Need some short examples on how to write schemas.

https://codeclimate.com/repos/52e0e458e30ba02258007bdf/ValidationController

This is literally a brain dump, so apologies for the unstructured form.

Warning flags would go up if I'd find anything of the following:

• A column has less than 5% non-missing entries.
• CSV contains only one column
• Negative values for a numeric variables (some valid exceptions exist of course)
• Duplicate rows, especially if there's an identifier.
• Characters are used in suspicious ways, e.g. em-dash and en-dash in dates
• Headers appear within the data and not just in the first row
• Dates.
• What should be numeric formats are parsed as strings because they include symbols, e.g. "%" or "£" or even just ",".
• Symbols like "Â" are often encoding errors.
• Column names are not, or erroneously, recognised because of whitespaces.
• What is the indicator for missing data? Are 0s actually missings or vice versa?
• Is there more than one type of missing data? Coded as "." or perhaps ".a"?
• Are there any patterns that would indicate a column got shifted half-way down the file e.g. one column to the right?
• If there's a unique identifier is it unique?
• Is there data in a column that is a gross outlier? E.g. a table of regional population statistics that includes overall UK population in the last row, which is several magnitudes larger.
• Validate common data columns, e.g. postcodes or email addresses
• The first and last rows follow a different pattern than the rest.
• Maximum character length of a string column is suspiciously long.
• Boolean variables are not boolean, i.e. include misspelled T/F or other values than (0,1). (Though sometimes factors are coded with (1,2).)
• Large percentage of numeric data is sprinkled with a few character strings.
• Fields start with code language such as "{" or "<"

and my favourite one:

• Infinite jest: CSV-file comes from a European country where they use ";" as separator and "," for the decimal point.

Here's a terrific example:

http://data.gov.uk/data/dumps/data.gov.uk-ckan-meta-data-latest.csv.zip

Part of theodi/shared#154

While validating CSV from http://www.who.int/tb/country/data/download/en/

I got a service unavailable message

Just playing with layouts for homepage. @JeniT we talked about making it clear that you only have to fill in 1 field: do you think either of these layouts are clearer?

(Note that the 2nd layout would need some tweaking as there isn't much room for the url / filename )

Part of theodi/shared#154

The download button displays an error for fa-cloud-download.

http://www.leeds.gov.uk/opendata/Documents/StaffPayMultiples.csv

Following discussion on the CSV on the Web Working Group, I think we should downgrade issues with headers to warnings rather than errors, or at least in general. More specifically:

• give an error if there is no header line and there's no Content-Type header or the Content-Type header doesn't include the parameter header=absent
• give a warning if there is no header and the file has been uploaded rather than retrieved through HTTP
• :empty_column_name and :duplicate_column_name should be warnings rather than errors

Tasks:

· current 'File URL' column to change functionality on-click to 'view report'
· current 'view-report' becomes 'download file'
· possible to support an optional 'file name' in current 'File URL' column? will need separate column? how often is this column likely to be filled? - future functionality - consider supporting in design

We can use DataPipes for this, a la http://okfnlabs.org/bad-data/ex/gla-spending/

http://data.defra.gov.uk/env/aqfg14-13but-201105.csv

@JeniT as discussed we are proposing pulling out key information into a dashboard style.

Errors will continue to be grouped by category (probably in the form of an accordion) and colours will continue be used to indicate severity.

· include 'Messages' under errors & warnings in dashboard
· colour code the dashboard errors/warnings/messages inline with report
· change yellow of warnings to amber to bring further inline with 'traffic light' system
· create 'accordion' sections of report - structural/schema/context problem with breakdown of errors/warnings/messages
· include column confliction form under dashboard
· allow space for multiple 'badges' in dashboard render

They're a bit basic currently. Need to humanise it a bit.

I also have this problem in R that you can't read csv-files the normal way if they are served via a https link.

@JeniT As discussed we will remove the "Powered by the ODI" link in the header. Stephen will update this issue with a more high fidelity version soon.

Tasks;
· Remove ODI logo from top right of header nav
· Menu moves into header nav with same styling as ODI ( http://theodi.org )
· Add 'About' section to menu illustrated above
· Insert existing copy/edit of 'About' text
· remove 'Home' title
· Social links in footer?
· investigate possibility of cleaner solution to double tabbed 'upload/from URL
· Correct wording of validation form