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
--skipdirective 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 11in 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
.qat 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