type_guess incorrectly classifies mixed numeric/text columns as numeric when cells are pre-converted by tabulator

[tsila30]

When use_type_guessing = true and strict_type_guessing = false, the type_guess function in utils.py still classifies columns containing mixed values (e.g. "1213", "0164A") as numeric, causing a PostgreSQL error during COPY/insert:
invalid input syntax for type numeric: "0164A"
Root cause:

The Stream object pre-converts cell values via tabulator before type_guess is called. Numeric-looking values like "1213" become Decimal('1213'), while non-numeric values like "0164A" remain as str.

In type_guess (with strict=False), the logic counts isinstance(cell, type) votes. Since 850+ cells are already Decimal objects and only 2-3 remain as str, Decimal wins the vote — even though the column contains non-numeric data.

The strict=False parameter is supposed to mean "if any value doesn't match, don't use that type", but it doesn't work as expected because:

The pre-conversion already turned most values into Decimal
isinstance(Decimal('1213'), str) is False, so str gets almost no votes
The few remaining str values (like "0164A") are outvoted
Additionally: _save_type_overrides then persists type_override: numeric in the datastore metadata, making the problem permanent even on subsequent reloads.

Steps to reproduce:

Set ckanext.xloader.use_type_guessing = true and ckanext.xloader.strict_type_guessing = false i try to change this but also doesnt work
Upload a CSV where a column has mostly numeric values but a one alphanumeric ones (e.g. flight numbers: 1213, 1549, 0164A)
xloader will fail with invalid input syntax for type numeric

Environment:

CKAN 2.10.3
ckanext-xloader (latest from pip)
tabulator library with sample_size=1000

do you have a issue for this i write something but i prefer ti know what about this

[ThrawnCA]

The strict=False parameter is supposed to mean "if any value doesn't match, don't use that type",

No, that's what strict=True would mean.

The behaviour you've identified, of following the majority of rows, is exactly how the README describes strict=False.

[tsila30]

so the file doesnt work i hav ethis error :Validation error writing rows to db: None - {'message': 'The data was invalid: invalid input syntax for type numeric: "0164A"'} because is not numeric

[ThrawnCA]

so the file doesnt work i hav ethis error :Validation error writing rows to db: None - {'message': 'The data was invalid: invalid input syntax for type numeric: "0164A"'} because is not numeric

What results do you get if you instead set strict=True?

[tsila30]

and the CKANEXT__XLOADER__USE_TYPE_GUESSING to true also?

[ThrawnCA]

and the CKANEXT__XLOADER__USE_TYPE_GUESSING to true also?

Yes.

[JanneVuorinen]

similar issue here.

ckanext.xloader.use_type_guessing = True
ckanext.xloader.strict_type_guessing = True

and still

ckanext.xloader.job_exceptions.LoaderError: Validation error writing rows to db: None - {'records': ['invalid input syntax for type numeric: "16,5"'], 'records_row': 249}

[JanneVuorinen]

some additional info: I converted all commas to periods in csv. like 16,5 -> 16.5.
Now works. But should it be possible to define locale / decimal separator in xloader? Postgres does not accept commas, but should xloader be able to fix this?

[ThrawnCA]

What happens if the entry with a comma is within the first hundred rows?

[JanneVuorinen]

It seems that column stays as "text" and is read correctly to database if column has comma value in the beginning of csv file.

Then I created mock data with one column value with comma "315245000,10". Before that there is 1000 rows without decimals in that column, ""315245000".

Xloader logs:

Field has changed; new type for XXXXX, eur field is numeric but existing type is text. (this does not happen if that row having comma decimals is in the beginning of csv)

and voilá, here comes the error:

['invalid input syntax for type numeric: "315245000,10"']

My current config:

ckanext.xloader.use_type_guessing = False
ckanext.xloader.strict_type_guessing = True
ckanext.xloader.max_type_guessing_length = 1000000

[ThrawnCA]

XLoader uses the first 100 rows to do its type guessing. If a column contains different data after that point, it may result in such an error. That is a known issue and unrelated to the original post by tsila30. If you would like to make suggestions about improving it, please open a new issue.

[tsila30]

When ckanext.xloader.use_type_guessing = true is set, the first load of a new resource (one that doesn't already exist in the datastore) falls back to the slow tabulator/chunk-by-chunk insertion path instead of using PostgreSQL COPY.

This is because the routing logic in jobs.py calls tabulator_load() as the primary path when use_type_guessing is True:

if use_type_guessing:
try:
tabulator_load() # slow - inserts 250 rows at a time
except JobError as e:
direct_load(allow_type_guessing=True)
However, direct_load(allow_type_guessing=True) already performs type guessing on a sample inside load_csv, then uses the fast COPY path for the actual data insertion. So there is no reason to use tabulator_load() as the primary path.

Impact:

For large CSV files being loaded for the first time (no existing datastore schema), the load takes hours instead of minutes. Once the resource exists in the datastore, subsequent loads correctly use the COPY path because datastore_resource_exists() returns True, making use_type_guessing evaluate to False.

Expected behavior:

When use_type_guessing = true, xloader should use direct_load(allow_type_guessing=True) as the primary path (type guessing on a sample + COPY), falling back to tabulator_load() only if COPY fails.

Proposed fix:

if use_type_guessing:
try:
direct_load(allow_type_guessing=True)
except (JobError, LoaderError) as e:
logger.warning('Load using COPY with type guessing failed: %s', e)
logger.info('Trying again with tabulator')
tabulator_load()
This preserves type guessing while using the fast COPY path for insertion.

[ThrawnCA]

When ckanext.xloader.use_type_guessing = true is set, the first load of a new resource (one that doesn't already exist in the datastore) falls back to the slow tabulator/chunk-by-chunk insertion path instead of using PostgreSQL COPY.

This is unrelated to the original issue and should be raised separately.

This is because the routing logic in jobs.py calls tabulator_load() as the primary path when use_type_guessing is True:

That is the meaning of the use_type_guessing flag.

However, direct_load(allow_type_guessing=True) already performs type guessing on a sample inside load_csv

I don't think this is the case. When you raise an issue for this, can you identify the lines you're referring to?