Forum Discussion

tokyobaer's avatar
tokyobaer
Frequent Visitor
7 years ago

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

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

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?

Need Help

5 Replies

    • tokyobaer's avatar
      tokyobaer
      Frequent Visitor
      7 years ago

      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.

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        7 years ago

        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.