How to Convert SQLite to Excel
SQLite, a lightweight database suitable for managing small data systems, might power some of your business' applications and websites. If it does, you might wish to share SQLite's data with your Excel spreadsheets. Excel doesn't have a "Load SQLite" button, but it does allow you to import text data. After exporting SQLite data into a comma-delimited file you can use Excel's import feature to load it into your spreadsheet.
Export SQLite Data
Open Windows Explorer and locate the file that contains your SQLite database. This file has a .db extension. Double-click that file to launch SQLite. SQLite loads the database and launches the Windows Command Prompt window. That window displays the following prompt:
Type the following command and press "Enter."
SQLite switches to comma-delimited mode and displays a new prompt.
Type the command shown below and press "Enter:"
This command tells SQLite to send its output to a file named “myfile.txt.” Change "myfile" to anything you like but keep the .txt extension. Saving it as a .txt file makes it easier for you to open it using Notepad and other text editors.
Type .tables and press “Enter” to view a list of tables in the database. Type the following command:
select * from mytable
Replace “mytable” with the name of the table you wish to load into Excel.
Press “Enter” to generate a comma-delimited file containing the table’s data.
Load Data into Excel
Launch Excel and click the ribbon’s “Data” tab. Click “From Text” from the Get External Data section and browse to the file you created.
Double-click the file to launch the Text Import Wizard. It begins by displaying a preview of the text file's contents. Click the "Delimited" radio button to select it and click "Next."
Click the "Comma" check box to put a check mark there and click “Next” to continue. Click "Finish" to view the "Import Data" dialog window. This window allows you to choose the worksheet into which you wish to import the data.
Click "Existing Worksheet" to load it into the current worksheet or click "New Worksheet” to load the data into a new one.
Click "OK" to continue. The wizard inserts the SQLite data into Excel. Click "Finish" to load the data into the spreadsheet.
- This example demonstrates selecting all data from a SQLite table using the "Select *" command. If you understand SQLite "Select" commands you can tailor them to retrieve specific data from any table.
- Type .help at the SQLite prompt to view other commands that can help you manage your database.
After majoring in physics, Kevin Lee began writing professionally in 1989 when, as a software developer, he also created technical articles for the Johnson Space Center. Today this urban Texas cowboy continues to crank out high-quality software as well as non-technical articles covering a multitude of diverse topics ranging from gaming to current affairs.