Forum Discussion
ODBC SQLite3: Large integers are truncated to Int32
Hi tokyobaer
Do you have a look at this link?
https://www.biinsight.com/visualise-sqlite-data-in-power-bi/
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Maggie v-juanli-msft ,
thanks a lot for your hint!
I believe I had already checked out the referenced site - now I just followed all the steps again to verify, you can see the result in the following screenshot: Right-hand side SQLite Browser (sorry in German...), showing large numbers for the fields id and cid, left-hand side the same table during the Get Data / Import process - clearly id / cid are truncated to Int32.Max, while other values are imported correctly.
Regards, Stephan.
Hi tokyobaer
The number "214..." is a string of your id number in SQLite 3 or not?
how many digits in your number in SQLite 3?
In Powre BI, It allows Whole Number data type for 19 digits; positive or negative whole numbers between -9,223,372,036,854,775,808 (-2^63) and 9,223,372,036,854,775,807 (2^63-1).
If the digits is more that the max limitation of the Power BI, when imported, it may be cut down.
It seems the number are not used for calculation, if so, you could change it to text in SQLite 3.
The Text type support more characters.
Text - A Unicode character data string. Can be strings, numbers, or dates represented in a text format. Maximum string length is 268,435,456 Unicode characters (256 mega characters) or 536,870,912 bytes.
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hello Maggie v-juanli-msft ,
thank you for taking the time!
You are correct to mention that the numbers in the SQLite3 database are used as ids, not for calculation - but unfortunately I cannot modify the SQLite3 database, because it is created by a third-party application (Anki with a defined database format). The numbers / ids are actually Unix epoch timestamps in milliseconds (factor 1000 !), so nowadays we are at roughly 1.5e12 (therefore 13 digits). Therefore the number "214..." only appears in Power BI and is not an id number in SQLite3.
Now I have tried to create a minimal SQLite3 database to reproduce the issue: Link
The table 'cards' contains 2 lines with the 13 digits ids:
In Power BI, after Get Data / ODBC / SQLite3 / database=(...)BIGINT-Test.anki2 / Default Credentials / Connect, I get
Maybe you can reproduce the issue?
Regards, Stephan.
Hello markvs , evandroportugal ,
it seems that in the thread Sqlite - Wrong integer imports in the Power BI community last year you had a similar issue like the one I am facing now: Large integer values are not imported correctly from SQLite3 to Power BI. As your thread does not contain an Accepted Solution: Have you been able to solve your problem otherwise or could you give me any hints on my issue? Any support would be appreciated...
Regards, Stephan.