Skip to content

Instantly share code, notes, and snippets.

@jbrown123
Last active October 10, 2022 01:01
Show Gist options
  • Save jbrown123/6f20a6d6d3778949e8a6091d786e309e to your computer and use it in GitHub Desktop.
Save jbrown123/6f20a6d6d3778949e8a6091d786e309e to your computer and use it in GitHub Desktop.
Include SQLite commands at the top of a data file

Merge SQLITE commands and data in one text file

Frequently it's handy to keep the instructions for importing data and the data itself in the same physical file. The attached script & data is one example. The first line is a SQL comment (starts with --) telling you how to load the file itself. On unix-like systems this could be replaced with a 'shebang' (#!) which SQLite also, conveniently, ignores.

There are a few things to keep in mind.

  • First, you must match the embedded names in the file with the actual file name. Renaming the file will break the SQL at the top that is trying to load the data.
  • Second, you must edit the --skip directive to adjust for any change in commands that you need. For example, if you have more or less fields in your create table command or if you need to add other SQL commands. Note that if you were to import the 'header' line (by doing --skip 11 in this case) it would appear as part of the data since we are importing into an existing table. If you are planning to use the header line, reduce your skip command by 1 line so they get included.
  • Third, note the use of the .q at the end of the SQL commands. This tells SQLite to stop interpreting the file as commands.

You can add any valid SQL commands or SQLite meta commands (dot commands like .help, etc.) to the script. For example, you could create indexes or views. Or you could manipulate the data in any way necessary.

The data for this example was retrieved from the US Census Bureau at https://data.census.gov/cedsci/table?tid=PEPPOP2021.NST_EST2021_POP&hidePreview=false

We can make this file beautiful and searchable if this error is corrected: Illegal quoting in line 1.
-- sqlite3 -init "NST_EST2021_POP.csv"
CREATE TABLE pop (
name text unique,
pop2020 int,
pop2021 int);
.import "NST_EST2021_POP.csv" pop --csv --skip 12
.mode list
.schema
.headers on
select count(*) from pop;
.q
"Geographic Area Name (NAME)","Population Estimate, July 1, 2020 (POP_2020)","Population Estimate, July 1, 2021 (POP_2021)"
"United States",331501080,331893745
"Northeast Region",57525633,57159838
"Midwest Region",68935174,68841444
"South Region",126409007,127225329
"West Region",78631266,78667134
"Oklahoma",3962031,3986639
"Nebraska",1961455,1963692
"Hawaii",1451911,1441553
"South Dakota",887099,895376
"Tennessee",6920119,6975218
"Nevada",3114071,3143991
"New Mexico",2117566,2115877
"Iowa",3188669,3193079
"Kansas",2935880,2934582
"District of Columbia",690093,670050
"Texas",29217653,29527941
"Missouri",6154481,6168187
"Arkansas",3012232,3025891
"Michigan",10067664,10050811
"New Hampshire",1377848,1388992
"North Carolina",10457177,10551162
"Ohio",11790587,11780017
"South Carolina",5130729,5190705
"Wyoming",577267,578803
"California",39499738,39237836
"North Dakota",778962,774948
"Louisiana",4651203,4624047
"Maryland",6172679,6165129
"Delaware",991886,1003384
"Pennsylvania",12989625,12964056
"Georgia",10725800,10799566
"Oregon",4241544,4246155
"Minnesota",5707165,5707390
"Colorado",5784308,5812069
"New Jersey",9279743,9267130
"Kentucky",4503958,4509394
"Washington",7718785,7738692
"Maine",1362280,1372247
"Vermont",642495,645570
"Idaho",1847772,1900923
"Indiana",6785644,6805985
"Montana",1086193,1104271
"New York",20154933,19835913
"Puerto Rico",3281538,3263584
"Connecticut",3600260,3605597
"Florida",21569932,21781128
"Virginia",8632044,8642274
"Massachusetts",7022220,6984723
"Illinois",12785245,12671469
"Mississippi",2956870,2949965
"Arizona",7177986,7276316
"Utah",3281684,3337975
"Wisconsin",5892323,5895908
"Alabama",5024803,5039877
"West Virginia",1789798,1782959
"Rhode Island",1096229,1095610
"Alaska",732441,732673
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment