How to open .SQLite files

Question

I'm trying to open a .sqlite file on Windows, but I don't know how to. Do you know a good program for it?

It contains data for statistical analysis, but I prefer having a .txt file.

I also have a .spatialite file. Can you help me?

such question should placed in Software Recommendation : http://softwarerecs.stackexchange.com/ , StackOverFlow isn't the right place — Jalal Mostafa, Sep 24 '14 at 13:17

score 99 · Answer 1 · answered Mar 11 '19 at 15:41

99

If you just want to see what's in the database without installing anything extra, you might already have SQLite CLI on your system. To check, open a command prompt and try:

sqlite3 database.sqlite

Replace database.sqlite with your database file. Then, if the database is small enough, you can view the entire contents with:

sqlite> .dump

Or you can list the tables:

sqlite> .tables

Regular SQL works here as well:

sqlite> select * from some_table;

Replace some_table as appropriate.

answered Mar 11 '19 at 15:41

Big McLargeHuge

14,841
10
80
108

5

This should be an accepted answer, no need to install heavy weight packages – yǝsʞǝla Jul 31 '19 at 02:41
2

My Windows 10 did not recognize sqlite3 as a command. – zylstra Feb 10 '23 at 19:15
1

@zylstra You'll have to install the sqlite cli first. – Dylan Cross Mar 23 '23 at 15:47
2

get it from here, use command prompt from the same folder you extracted it, https://sqlite.org/2023/sqlite-dll-win64-x64-3410200.zip – Alireza Jamali Apr 01 '23 at 13:00
Or, for the latest, https://sqlite.org/download.html – Josiah Yoder May 18 '23 at 19:21

score 72 · Answer 2 · edited Apr 10 '18 at 21:07

72

SQLite is database engine, .sqlite or .db should be a database. If you don't need to program anything, you can use a GUI like sqlitebrowser or anything like that to view the database contents.

Website: http://sqlitebrowser.org/
Project: https://github.com/sqlitebrowser/sqlitebrowser

There is also spatialite, https://www.gaia-gis.it/fossil/spatialite_gui/index

edited Apr 10 '18 at 21:07

Alaa

115
12

answered Sep 24 '14 at 11:28

Mike S.

1,995
13
25

9

I think this is closer to the "right" answer. I suspect a means to access the data via an API through `R` or another language is not as broadly helpful, even if it helped the OP. – Mike Williamson Apr 10 '18 at 22:04

score 42 · Answer 3 · answered Nov 28 '19 at 17:14

42

My favorite:

https://inloop.github.io/sqlite-viewer/

No installation needed. Just drop the file.

answered Nov 28 '19 at 17:14

Simon

1,890
19
26

You are my life saver. thanks for your input. – iBala Sep 09 '22 at 14:37
This is so simple, and no callbacks to the server. You can save the page for use offline if need be. – zylstra Feb 10 '23 at 19:18

score 1 · Accepted Answer · answered Jan 15 '17 at 20:49

1

I would suggest using R and the package RSQLite

#install.packages("RSQLite") #perhaps needed
library("RSQLite")

# connect to the sqlite file
sqlite    <- dbDriver("SQLite")
exampledb <- dbConnect(sqlite,"database.sqlite")

dbListTables(exampledb)

answered Jan 15 '17 at 20:49

emeryville

332
1
4
19

I found this tutorial on RSQLite helpful: https://blog.exploratory.io/accessing-sqlite-with-rsqlite-and-querying-with-dplyr-in-r-script-7eecf1e1b5b0 – windyvation Jan 10 '21 at 23:47

How to open .SQLite files

4 Answers4