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