Repository navigation
floating point data being imported as integers when small values are present #4035
Description
Details for the issue
What did you do?
"3333","12","2",1,455009,"1","11","1","ZZA","1",0.3,"3","XXY",-0.000895,0.000244,0,0.000244,"1111"
"4444","12","2",1,455009,"1","12","1","ZZA","1",0.06,"3","XXY",0.000128,0,-3.5e-05,-3.5e-05,"2222"
What did you expect to see?
The third last column should import as REAL or NUMERIC
What did you see instead?
The third last column imported as INTEGER. Despite the type assignment error, the data itself is still imported as floating point.
Ideal fix:
Allow workarounds to this kind of bug: Create a new feature to manually ovveride the automatic type detection during the import process.
Fix the bug: Have code which will not interpret very small values as zeroes.
Useful extra information
DB4S v3.13.1 [built for x86_64-little_endian-llp64] on Windows 10 Version 2009 (winnt/10.0.26200) [x86_64]
using SQLite Version 3.46.1
and Qt 5.15.2
Activity
chrisjlocke commentedon Oct 14, 2025
So good news and good news.
You can import into an existing table. You don't have to let DB4S guess field types. This means if you have 800 rows of data and 799 of them are the number 1, but the last one is 0.9, DB4S won't assume that field is in integer.
The -3.5e-05 in your file isn't interpreted as text - I thought it would be. DB4S will store this as a number.
select typeof(field16) from test
displays real and real.
I even checked with chatGPT and of all the database tools around, it picked DB4S.
This isn't a bug. And there are workarounds.
When you import data into a table, there is even a checkbox - [x] disable data type detection
Of course, that doesn't matter if you import into an existing table anyway, where you've defined the field types. Well, I guess it does, as even if you set the field type, sqlite can store what it likes in whatever cell is defined as.
DB4S is a great tool. 🤓👍