Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tokyobaer
Frequent Visitor

ODBC SQLite3: Large integers are truncated to Int32

In an approach similar to Sqlite - Wrong integer imports I am trying to import data from an SQLite3 database (concrete usecase: Anki) using the SQLite ODBC Driver (v0.9996, 64bit like matching my Power BI) and in particular some columns with Unix epoch milliseconds used as keys in the tables. A workaround described in Sqlite - Unix Epoch Millis does not work for me as I cannot ignore the milliseconds. When I import the data directly, all values are truncated to 2147483647 (apparently the Int32.MaxValue).

image.png

When I change the setting of the driver (in the ODBC Data Source Administrator) to "Always BIGINT",

image.png

the data appears in Power BI as "Binary" and I cannot convert it to "Whole Number". I can actually see single values (as text) when drilling down on the Binary and thereby invoking Lines.FromBinary - but this would not give me the complete column(s).

 

Any idea on how to proceed?

5 REPLIES 5
v-juanli-msft
Community Support
Community Support

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.

 

image.png

 

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
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.

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: 

image.png

 

In Power BI, after Get Data / ODBC / SQLite3 / database=(...)BIGINT-Test.anki2 / Default Credentials / Connect, I get

image.png

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors