I am selecting data from a sheet with columns of mixed data types (e.g., 198765T, 864278,19-89765). I expected the column to be treated as all strings, yet the purely numeric values are being returned with a trailing decimal and zero (e.g., 864278.0) which is not in the original spreadsheet. How do I force the driver to treat these columns as type varchar and return only what was entered in the original sheet? I've tried CAST(), CONVERT(), CSTR() but the decimal and zero remain.
|
At Connecting to the Database, maxScanRows Indicates how many rows should be scanned when determining the column types. If you set maxScanRows to 0, the entire file is scanned. If you set maxScanRows to a negative value, the file won't be scanned. Default: 10 Try maxScanRows=-1.
|
I've followed your recommendation with no success. I've investigated further and found that the problem is with certain rows that were pasted in from another source. The cells apparently have some hidden "stuff" coming in with them that I can not see, but that causes them to always be selected as real numbers. The only way I was able to get the desired result was to apply the Excel "CLEAN()" function to the values and then paste them back into the cells.
I guess at this point I need to know if there is any way the hxtt driver can detect and/or strip out these extra characters in the same way that the Excel clean function does?
|
Pleaes email webmaster@hxtt.com your xls sample, and let us know what's your issue.
|