10 Replies Latest reply on May 10, 2018 2:22 PM by Dan Cory

    Connect to a .db file

    Diego Spamer

      HI,

       

      I'm trying to connect to a database which extension is ".db", it was created in r with sqldf package and has several information I need to use in the dashboard.

       

      I have tried using the ODBC connection using SQLite 3 and SQLite ODBC Driver in the Controler option, I have selected the location of the file in the browser but I get no access to any table within the file.

      I also tried the conect to a file option but the format is not recognized by tableau.

       

      I was succesfull doing this connection in Power BI and R. How does tablau read a ".db" file?

        • 1. Re: Connect to a .db file
          Dan Cory

          It looks like SQLDF can write to several backends: 'RSQLite', 'RH2', 'RMySQL' and 'RPostgreSQL'. You'd have to know which of these was used to create this file. I don't see in the documentation any discussion of the ".db" extension.

           

          If it's SQLite (which is the default), then the SQLite ODBC driver is the solution. You will want to set up a TDC following instructions at Customize and Tune ODBC Connections.

           

          Dan

          • 2. Re: Connect to a .db file
            Diego Spamer

            It is an SQLite, it is only a set of tables, with headers and several rows with data. It's hard to believe why it is such a pain to connect to the ".db"  file

            • 3. Re: Connect to a .db file
              Dan Cory

              We've never had enough demand to ship a built in connector to SQLite. Tableau does read several native R files, though. Statistical File

               

              Dan

              • 4. Re: Connect to a .db file
                Diego Spamer

                You are suggesting to run an R script insie tableau to access the .db File?

                 

                I have installed the following SQLite ODBC driver: SQLite ODBC Driver  Im trying to connect with the biult in ODBC driver and still doesn't work...

                • 5. Re: Connect to a .db file
                  Dan Cory

                  You could open the DB file in R and write it back out as an R file (or a CSV for that matter).

                   

                  If you are trying the SQLite ODBC driver, make sure you read the article pointed to earlier about how to set up a TDC for it.

                   

                  Dan

                  • 6. Re: Connect to a .db file
                    Diego Spamer

                    Of course I could have a ".csv", but then I would have to manually updated the ".csv" every time that the database is updated, eventually the .csv file will be big and slower than the sqlite database.

                     

                    The TDC article is misleading because I don't know what customization need to be explicitly added to the TDC. The .db file has 4 tables inside and each table has character columns and numeric columns. Why something so simple gets so confusing?

                    • 7. Re: Connect to a .db file
                      Dan Cory

                      The TDC article includes a sample TDC file for SQLite. Use exactly that what it has. I attached it for you.

                       

                      Dan

                      • 8. Re: Connect to a .db file
                        Diego Spamer

                        Hi Dan,

                         

                        Thanks for your support, but it is still not working. As a round-up here is what I did:

                         

                        1.- got the .tdc file you attached.
                        2.- Read the Customize and Tune ODBC....

                        3.-Place the .tdc file in the path the article describres (.../Tableau repository/data sources)

                        4.- Restarted my computer

                        5.-Opened tableau, new data source, odbc, SQLite ODBC Driver, browsed my file.

                        6.-The tables are there.

                        7.- Error Message: "its not possible to create extracion
                                                                SELECT .....
                                                                FROM
                                                                LIMIT"

                         

                         

                        The message error displays the header's in my table and the table name :/

                         

                        The . db file was created in R using the SQLDF package by reading several csv's, process them and write them to a .db file. Wondering around this Tableau-SQLite  connection headache, I'm testing between tableau and power bi, accessing to this DB is crucial for me in order to get my employer to buy the license. Even though power bi sucks compared to tableau (in a personal opinion), it is frustrating that a simple connection to a database in SQLite can't be done easily.

                         

                        How does the data has to be arranged in a database in order for tableau to work?

                         

                        The csv work around is like having an excel file with data in one tab and formulated graphics in another tab where you have to manually update the data everytime you want to use the dashboard. Doing this there is no point at paying the Tableau license.

                        • 9. Re: Connect to a .db file
                          Dan Cory

                          I can't really help further without looking at your file. Tableau doesn't support SQLite. We try to help users connecting to unsupported databases but it's very hard to do so.

                           

                          We support 60+ other databases, but not that one. I would change your R script to write out a different format.

                           

                          Dan

                          • 10. Re: Connect to a .db file
                            Dan Cory

                            FWIW I tested the steps given in our article. They are a little bit out of date but work reasonably well. They are specifically for SQLite 3, but SQLite 2 is from 2004 so I doubt that's the issue.

                             

                            Dan