SQLite is easy to use for database import, but there are some caveats and tricks. In particular, when importing CSV files, SQLite selects TEXT as the file format for all fields. This video shows a quick method for import, shows import with different tools, then shows how to import to get the proper formats.
- [Instructor] This problem simply asks for the creation of a database. In my solution, I'll show how to use SQLite, along with three front end control programs, SQLite Manager for Firefox, DB Browser for SQLite, and SQLite3. I'll import the data set and show how to make sure the field formats are correct. Importing a database into SQLite is simple if the field formats don't matter.
When importing CSV files, SQLite assumes all fields are text fields. For example, the county code field is a number, but during import, SQLite decided it was a text field. Without manual guidance, SQLite will import all fields in a CSV file as text, even if they appear to be something else, such as integer or real. I'll show the simple, but somewhat incorrect solution of just importing the CSV file and letting SQLite select the field formats, then I'll show a more complex way of importing data that results in more accurate field formats.
To correctly import the data, it's important to know what the data looks like. I'll show how to use simple Linux commands to explore this rather large data set and select the proper field formats. When you're finished with this chapter, you'll have a better understanding of SQLite, specifically that SQLite has multiple graphic user interfaces, that SQLite imports CSV files, and that SQLite requires guidance on import formats.
- Strengths and weaknesses of SQLite
- Creating a database
- Joining data sets
- Calculations with SQLite and Python
- Searching a database
- Subqueries and queries in SQLite
- CRUD operations in SQLite with R
Skill Level Intermediate
1. SQLite in Five Minutes
2. Create a Database
3. Join Two Datasets
4. Search a Database
5. Create, Read, Update, and Delete Operations
6. Averages and Calculations
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.