Forum Discussion

evandroportugal's avatar
evandroportugal
New Member
8 years ago

Sqlite - Wrong integer imports

Hello.

 

I'm trying to import a spiceworks database to Power BI, but I got a strange issue. While importing the devices table, there's a column named memory, which represents the amount of memory in a device in bytes. The problem is, any machine with at least 3GB is showing with the value 2,147,483,647, but since whole number in power BI is 64 bits, why is this happening? 

I'm using SQLite 64-bits ODBC Driver and Power BI 64 Bits. If I do the ODBC connection to import the data to a excel spreadsheet, it gets the right value for everyone.

 

Any help is welcome, thanks!

7 Replies

  • evandroportugal's avatar
    evandroportugal
    New Member
    8 years ago

    Hi,

    I'm trying to use Power BI to create better reports for spiceworks, but I got a strange problem.

    While trying to import the devices tables, there's a column named 'memory', which values represent the amount of RAM installed in each machine (value in bytes, 10 digits for anything above 1GB). This way, any machine with more than 2GB is getting the same value on Power BI, which is 2147483647, since the whole number data type in Power BI is 64-bits, this wasn't supposed to happen.

    I'm using 64-bits version of Power BI and SQLite ODBC driver.

    Anyone have a light on this issue?

    Thanks

  • jwaggoner's avatar
    jwaggoner
    New Member
    3 years ago

    5 years later and ran in to the same problem, so here's my workaround for anyone else coming across this thread...

     

    In your query, use the SQLlite CAST function to change the integer column's datatype to text:

    cast(my_column as text)

    This should load the number in to PowerBI exactly as-is. You can then convert it back to number format in Power Query if needed.

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi evandroportugal,

     

    There is a Binary option under data type list, are you able to convert the imported data to this format?

     

    Regards,

    Yuliana Gu 

  • markvs's avatar
    markvs
    New Member
    7 years ago

    I am having the same issue.  Have you determined a work round or a solution to this problem?

     

    It seems to be very much a Power BI issue as if I use other tools to view the data (eg SQLLite SPY) then it is displayed in its correct format (a 19 digit number).

     

    Mark

  • markvs's avatar
    markvs
    New Member
    7 years ago

    Have you found a workround or a solution to this problem?  I am having the asame issue with a field containing a 19 digit number.

     

    Using tools such as SQLite SPY shows the data correctly - so it definitely seems to be associated with Power BI.

     

    Mark

  • MichaelWebb's avatar
    MichaelWebb
    New Member
    6 years ago

    Has anyone found a solution to this? I am having the same issue with the date field from an iPhone backup. I am using the 64bit SQLite ODBC driver.