Handle empty values in CSV files

[kaklakariada]

When reading CSV files using schema-auto-inference, importing may fail for empty values:

VM error: F-UDF-CL-LIB-1127: F-UDF-CL-SL-JAVA-1002: F-UDF-CL-SL-JAVA-1013:
com.exasol.ExaUDFException: F-UDF-CL-SL-JAVA-1080: Exception during run
java.lang.IllegalArgumentException: E-VSDF-67: Error converting value '' using converter com.exasol.adapter.document.documentnode.csv.converter.CsvValueConverters$Builder$$Lambda$888/0x00000008403ee040@b0fc838 (file 'medicare/prescription-drugs-claims/life/Drug Pricing/Medicare Prescription Drug Plans/Medicare Prescription Drugs Claims/data_preview.csv', row 2, column 'Benefit Phase'). Please fix the value in the CSV file or choose a different mapping for converting the value.
com.exasol.adapter.document.documentnode.csv.NamedCsvObjectNode.convert(NamedCsvObjectNode.java:94)
com.exasol.adapter.document.documentnode.csv.NamedCsvObjectNode.get(NamedCsvObjectNode.java:77)
com.exasol.adapter.document.documentpath.DocumentPathWalker$WalkVisitor.lambda$visit$0(DocumentPathWalker.java:83)
com.exasol.adapter.document.documentpath.DocumentPathWalker.runTraverseStepper(DocumentPathWalker.java:53)
com.exasol.adapter.document.documentpath.DocumentPathWalker.performStep(DocumentPathWalker.java:47)
com.exasol.adapter.document.documentpath.DocumentPathWalker.walkThroughDocument(DocumentPathWalker.java:38)
com.exasol.adapter.document.mapping.AbstractPropertyToColumnValueExtractor.extractColumnValue(AbstractPropertyToColumnValueExtractor.java:37)
com.exasol.adapter.document.mapping.SchemaMapper.mapColumns(SchemaMapper.java:48)
com.exasol.adapter.document.mapping.SchemaMapper.lambda$mapRow$0(SchemaMapper.java:39)
java.base/java.lang.Iterable.forEach(Iterable.java:75)
com.exasol.adapter.document.mapping.SchemaMapper.mapRow(SchemaMapper.java:39)
com.exasol.adapter.document.DataProcessingPipeline.run(DataProcessingPipeline.java:42)
com.exasol.adapter.document.GenericUdfCallHandler.run(GenericUdfCallHandler.java:97)
com.exasol.adapter.document.UdfEntryPoint.run(UdfEntryPoint.java:29)
com.exasol.ExaWrapper.run(ExaWrapper.java:197)
Caused by: java.lang.NumberFormatException: empty String
java.base/jdk.internal.math.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:1842)
java.base/jdk.internal.math.FloatingDecimal.parseDouble(FloatingDecimal.java:110)
java.base/java.lang.Double.parseDouble(Double.java:543)
com.exasol.adapter.document.documentnode.csv.converter.CsvValueConverters$Builder.doubleConverter(CsvValueConverters.java:166)
com.exasol.adapter.document.documentnode.csv.NamedCsvObjectNode.convert(NamedCsvObjectNode.java:83)
... 14 more
(Session: 1797769393118576642)

Importing of numbers with leading or trailing spaces also fails:

java.lang.IllegalStateException: Assertion query 'SELECT * FROM TEST.BOOKS' failed: VM error: F-UDF-CL-LIB-1127: F-UDF-CL-SL-JAVA-1002: F-UDF-CL-SL-JAVA-1013: 
com.exasol.ExaUDFException: F-UDF-CL-SL-JAVA-1080: Exception during run 
java.lang.IllegalArgumentException: E-VSDF-67: Error converting value ' 11' using converter com.exasol.adapter.document.documentnode.csv.converter.CsvValueConverters$Builder$$Lambda$902/0x00000008403ee840@5fbdc49b (file '1718262510375/testData-1.csv', row 2, column 'col2'). Please fix the value in the CSV file or choose a different mapping for converting the value.
com.exasol.adapter.document.documentnode.csv.NamedCsvObjectNode.convert(NamedCsvObjectNode.java:85)
com.exasol.adapter.document.documentnode.csv.NamedCsvObjectNode.get(NamedCsvObjectNode.java:77)
com.exasol.adapter.document.documentpath.DocumentPathWalker$WalkVisitor.lambda$visit$0(DocumentPathWalker.java:83)
com.exasol.adapter.document.documentpath.DocumentPathWalker.runTraverseStepper(DocumentPathWalker.java:53)
com.exasol.adapter.document.documentpath.DocumentPathWalker.performStep(DocumentPathWalker.java:47)
com.exasol.adapter.document.documentpath.DocumentPathWalker.walkThroughDocument(DocumentPathWalker.java:38)
com.exasol.adapter.document.mapping.AbstractPropertyToColumnValueExtractor.extractColumnValue(AbstractPropertyToColumnValueExtractor.java:37)
com.exasol.adapter.document.mapping.SchemaMapper.mapColumns(SchemaMapper.java:48)
com.exasol.adapter.document.mapping.SchemaMapper.lambda$mapRow$0(SchemaMapper.java:39)
java.base/java.lang.Iterable.forEach(Iterable.java:75)
com.exasol.adapter.document.mapping.SchemaMapper.mapRow(SchemaMapper.java:39)
com.exasol.adapter.document.DataProcessingPipeline.run(DataProcessingPipeline.java:42)
com.exasol.adapter.document.GenericUdfCallHandler.run(GenericUdfCallHandler.java:97)
com.exasol.adapter.document.UdfEntryPoint.run(UdfEntryPoint.java:29)
com.exasol.ExaWrapper.run(ExaWrapper.java:197)
Caused by: java.lang.NumberFormatException: Character   is neither a decimal digit number, decimal point, nor "e" notation exponential mark.
java.base/java.math.BigDecimal.[init](BigDecimal.java:522)
java.base/java.math.BigDecimal.[init](BigDecimal.java:405)
java.base/java.math.BigDecimal.[init](BigDecimal.java:838)
com.exasol.adapter.document.documentnode.csv.converter.CsvValueConverters$Builder.decimalConverter(CsvValueConverters.java:162)
com.exasol.adapter.document.documentnode.csv.NamedCsvObjectNode.convert(NamedCsvObjectNode.java:83)
 ... 14 more
 (Session: 1801728826365116416)
 at com.exasol.adapter.document.files.AbstractDocumentFilesAdapterIT.assertQuery([AbstractDocumentFilesAdapterIT.java:898](vscode-file://vscode-app/Users/chp/Applications/Visual%20Studio%20Code.app/Contents/Resources/app/out/vs/code/electron-sandbox/workbench/workbench.html))
 at com.exasol.adapter.document.files.AbstractDocumentFilesAdapterIT.testReadCsvWithTypesWithHeaderWithAutomaticInferenceWithOriginalColumnNameIllegalName([AbstractDocumentFilesAdapterIT.java:287](vscode-file://vscode-app/Users/chp/Applications/Visual%20Studio%20Code.app/Contents/Resources/app/out/vs/code/electron-sandbox/workbench/workbench.html))
 at com.exasol.adapter.document.files.S3DocumentFilesAdapterIT.testReadCsvWithTypesWithHeaderWithAutomaticInferenceWithOriginalColumnNameIllegalName([S3DocumentFilesAdapterIT.java:252](vscode-file://vscode-app/Users/chp/Applications/Visual%20Studio%20Code.app/Contents/Resources/app/out/vs/code/electron-sandbox/workbench/workbench.html))
 at java.base/java.util.ArrayList.forEach([ArrayList.java:1541](vscode-file://vscode-app/Users/chp/Applications/Visual%20Studio%20Code.app/Contents/Resources/app/out/vs/code/electron-sandbox/workbench/workbench.html))
 at java.base/java.util.ArrayList.forEach([ArrayList.java:1541](vscode-file://vscode-app/Users/chp/Applications/Visual%20Studio%20Code.app/Contents/Resources/app/out/vs/code/electron-sandbox/workbench/workbench.html))
Caused by: java.sql.SQLDataException: VM error: F-UDF-CL-LIB-1127: F-UDF-CL-SL-JAVA-1002: F-UDF-CL-SL-JAVA-1013: 
com.exasol.ExaUDFException: F-UDF-CL-SL-JAVA-1080: Exception during run 
java.lang.IllegalArgumentException: E-VSDF-67: Error converting value ' 11' using converter com.exasol.adapter.document.documentnode.csv.converter.CsvValueConverters$Builder$$Lambda$902/0x00000008403ee840@5fbdc49b (file '1718262510375/testData-1.csv', row 2, column 'col2'). Please fix the value in the CSV file or choose a different mapping for converting the value.
com.exasol.adapter.document.documentnode.csv.NamedCsvObjectNode.convert(NamedCsvObjectNode.java:85)
com.exasol.adapter.document.documentnode.csv.NamedCsvObjectNode.get(NamedCsvObjectNode.java:77)
com.exasol.adapter.document.documentpath.DocumentPathWalker$WalkVisitor.lambda$visit$0(DocumentPathWalker.java:83)
com.exasol.adapter.document.documentpath.DocumentPathWalker.runTraverseStepper(DocumentPathWalker.java:53)
com.exasol.adapter.document.documentpath.DocumentPathWalker.performStep(DocumentPathWalker.java:47)
com.exasol.adapter.document.documentpath.DocumentPathWalker.walkThroughDocument(DocumentPathWalker.java:38)
com.exasol.adapter.document.mapping.AbstractPropertyToColumnValueExtractor.extractColumnValue(AbstractPropertyToColumnValueExtractor.java:37)
com.exasol.adapter.document.mapping.SchemaMapper.mapColumns(SchemaMapper.java:48)
com.exasol.adapter.document.mapping.SchemaMapper.lambda$mapRow$0(SchemaMapper.java:39)
java.base/java.lang.Iterable.forEach(Iterable.java:75)
com.exasol.adapter.document.mapping.SchemaMapper.mapRow(SchemaMapper.java:39)
com.exasol.adapter.document.DataProcessingPipeline.run(DataProcessingPipeline.java:42)
com.exasol.adapter.document.GenericUdfCallHandler.run(GenericUdfCallHandler.java:97)
com.exasol.adapter.document.UdfEntryPoint.run(UdfEntryPoint.java:29)
com.exasol.ExaWrapper.run(ExaWrapper.java:197)
Caused by: java.lang.NumberFormatException: Character   is neither a decimal digit number, decimal point, nor "e" notation exponential mark.
java.base/java.math.BigDecimal.[init](BigDecimal.java:522)
java.base/java.math.BigDecimal.[init](BigDecimal.java:405)
java.base/java.math.BigDecimal.[init](BigDecimal.java:838)
com.exasol.adapter.document.documentnode.csv.converter.CsvValueConverters$Builder.decimalConverter(CsvValueConverters.java:162)
com.exasol.adapter.document.documentnode.csv.NamedCsvObjectNode.convert(NamedCsvObjectNode.java:83)
 ... 14 more
 (Session: 1801728826365116416)
 at com.exasol.jdbc.ExceptionFactory.createSQLException([ExceptionFactory.java:33](vscode-file://vscode-app/Users/chp/Applications/Visual%20Studio%20Code.app/Contents/Resources/app/out/vs/code/electron-sandbox/workbench/workbench.html))
 at com.exasol.jdbc.EXASQLException.getSQLExceptionIntern([EXASQLException.java:50](vscode-file://vscode-app/Users/chp/Applications/Visual%20Studio%20Code.app/Contents/Resources/app/out/vs/code/electron-sandbox/workbench/workbench.html))
 at com.exasol.jdbc.AbstractEXAStatement.execute([AbstractEXAStatement.java:462](vscode-file://vscode-app/Users/chp/Applications/Visual%20Studio%20Code.app/Contents/Resources/app/out/vs/code/electron-sandbox/workbench/workbench.html))
 at com.exasol.jdbc.EXAStatement.execute([EXAStatement.java:289](vscode-file://vscode-app/Users/chp/Applications/Visual%20Studio%20Code.app/Contents/Resources/app/out/vs/code/electron-sandbox/workbench/workbench.html))
 at com.exasol.jdbc.AbstractEXAStatement.executeQuery([AbstractEXAStatement.java:598](vscode-file://vscode-app/Users/chp/Applications/Visual%20Studio%20Code.app/Contents/Resources/app/out/vs/code/electron-sandbox/workbench/workbench.html))
 at com.exasol.adapter.document.files.AbstractDocumentFilesAdapterIT.assertQuery([AbstractDocumentFilesAdapterIT.java:895](vscode-file://vscode-app/Users/chp/Applications/Visual%20Studio%20Code.app/Contents/Resources/app/out/vs/code/electron-sandbox/workbench/workbench.html))
 ... 5 more

The VS should handle this case and return NULL instead of failing.