Type guessing in 0.12 is less robust

[ThrawnCA]

The new type guessing system in 0.12+ encounters errors that the old one didn't. For example,
QOLSVC-1280_current-program-funding-dtis_1_2_2_2_1.csv results in an error due to cells not being recognised as any type, even string:

Error: File "/mnt/local_data/ckan_venv/src/ckanext-xloader/ckanext/xloader/utils.py", line 221, in type_guess _columns.append(max(guesses_tuples, key=lambda t_n: t_n[1])[0]) ValueError('max() arg is an empty sequence') 

Or when a field is recognised as numeric or timestamp, and then a later row leaves that field blank, as per
QOLSVC-1280_current-program-funding-dtis_1_2_2_2_2.csv:

Error: Validation error writing rows to db: None - {'message': 'The data was invalid: invalid input syntax for type timestamp: ""'} 

The old messytables-based type guessing had no trouble parsing these.

[ThrawnCA]

It appears that the first problem arises when data is numeric on some rows but free text on other rows. The numeric rows cause str to be removed from the set of guessed types, and the string rows cause Decimal to be removed, leaving no guessed types at all. However, the at_least_one_value flag is still set to True, so the fallback str type isn't applied.

[ThrawnCA]

The "invalid input syntax for type timestamp" error appears to occur because datetime.datetime accepts an empty string as a valid timestamp value, but the SQL timestamp type does not.

[ThrawnCA]

Found another issue, where specific data combinations will result in the parser ignoring double quotes and just splitting rows on every comma. I'm not sure exactly of the exact root cause yet, continuing to investigate.
blp_dashboard_dataset_31-mar-2023.csv
The attached file contains 2 rows and will trigger the error, but if you upload a file with just one of the rows - either one, it doesn't matter - then it succeeds.

0.11 has no trouble with this file.

[ThrawnCA]

Ah, found the cause. The input file uses double quotes, but it encloses so many single quotes that the csv module assumes single quotes must be the quote character.

I'm not sure whether it's better to override this and always use double quotes, or set it up to try both.

[ThrawnCA]

One option would be to reduce parser.CSV_SAMPLE_LINES to 1, so it only samples the header, not the body, when determining the dialect to use. That way has less risk of weird edge cases in the data. But there may be better approaches.

[ThrawnCA]

It appears that Messytables was sampling 1000 lines to sniff the CSV dialect, rather than 100, which is why it behaves differently on the attached file.