➜ $ sqlite3 postal_code.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> .databases
main: /Users/xxx/simple_procedure_flutter/assets/data/postal_code.db
sqlite>
Putting a .csv file in your SQLite database
To add your .csv file as a table in the database you just created, your .csv must meet the requirement of being a tabular flat file. There are likely ways to input .csv files that don’t meet this requirement, but that is beyond the scope of this simple example.
You also need to know what delimiter is used in your .csv file. SQLite assumes it is a comma (,) but this is not always the case, and thus you can change that if necessary.
Before you add your .csv file to the database, it is best to create an empty table for your .csv file to go into. This is done using the CREATE TABLE function as demonstrated below:
sqlite> CREATE TABLE behaviour(
...> strain CHAR(8) NOT NULL,
...> plate CHAR(15) NOT NULL,
...> speed REAL
...> );
Using CREATE TABLE you should specify the name of each column in your .csv file, as well as the type expected and whether or not NULL values are allowed for that column. The code above creates an empty table called behaviour, with columns strain, plate and speed. To see the tables that exist in the database we created, we can type:
sqlite> .table
behaviour
To enter our data from our .csv file into this empty table, we first must change the mode to csv:
sqlite> .mode csv
And then specify the delimiter (my file had \t spacing instead of ,’s):
sqlite> .separator '\t'
Finally we can add our data to the database using the import command and passing it the name of the file to import and the name of the table.
sqlite> .import behaviourdata.csv behaviour
And now the data is in a queryable SQL database. My next post will be how to access slices of such a database from R.
macOS自带!
Putting a .csv file in your SQLite database
To add your
.csvfile as a table in the database you just created, your.csvmust meet the requirement of being a tabular flat file. There are likely ways to input.csvfiles that don’t meet this requirement, but that is beyond the scope of this simple example.You also need to know what delimiter is used in your
.csvfile. SQLite assumes it is a comma (,) but this is not always the case, and thus you can change that if necessary.Before you add your
.csvfile to the database, it is best to create an empty table for your.csvfile to go into. This is done using the CREATE TABLE function as demonstrated below:Using CREATE TABLE you should specify the name of each column in your .csv file, as well as the type expected and whether or not NULL values are allowed for that column. The code above creates an empty table called behaviour, with columns strain, plate and speed. To see the tables that exist in the database we created, we can type:
To enter our data from our .csv file into this empty table, we first must change the mode to csv:
And then specify the delimiter (my file had
\tspacing instead of,’s):Finally we can add our data to the database using the import command and passing it the name of the file to import and the name of the table.
And now the data is in a queryable SQL database. My next post will be how to access slices of such a database from R.
原文: Building a Basic Database From .csv Files Using SQLite3