Validate CSV against a Oracle table Schema

[ssowj]

I'm looking to validate a csv file whose contents will be inserted to a Oracle table.
I want to validate this csv data against the Oracle table schema before inserting and spit out the errors.
I read the table schema in JSON as {"uniq_id": "NUMBER(38, 0)", "lat": "NUMBER(7, 5)", "lon": "NUMBER(8, 5)", "orig_val": "NUMBER(9, 3)", "orig_type": "VARCHAR(1)"}.
The CSV file is not expected to have all the columns as the Oracle table but will have significant amount of data.
Do I have to modify this json schema to use it with the library to validate?

[roll]

Hi @ssowj,

You can use Table Schema Spec for the task - https://specs.frictionlessdata.io/table-schema/. Can you share a snippet from your csv so we can help with the schema? I think it will need to have some min/max/lenght constraints.

[ssowj]

Hi @roll
Here is a sample csv:
Lat Lon Elev
46.00000 6.30000 1800.100
46.05000 6.35000 6777.800
46.10000 6.40000 9444.300

Will this suffice?

[ssowj]

assume I can get constraints from the json returned for the ORacle table?
[{'name': 'lat', 'type': NUMBER(precision=7, scale=5, asdecimal=True), 'nullable': False, 'default': None, 'autoincrement': 'auto', 'comment': None}, {'name': 'lon', 'type': NUMBER(precision=8, scale=5, asdecimal=True), 'nullable': False, 'default': None, 'autoincrement': 'auto', 'comment': None}]

Is it possible to build the table schema on the fly for validation or should it be pre defined?

[roll]

Sure, you can process the output from Oracle dynamically creating Table Schema it will be something like:

fields:
  - name: lat
    type: number
    constraints:
      required: true
      min: 0
      max: 180
  ...