SHARE
TWEET

Untitled

a guest Aug 22nd, 2019 77 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Sometimes, you need to quickly dump a json file into sqlite, just to run some queries on it.
  2. This is a two-step process:
  3.  
  4. # 1. convert json to csv
  5.  
  6. ```
  7. cat file.json | jq -r '.data | map([.field1, .field2, .field3] | @csv)| join("\n")' > file.csv
  8. ```
  9.  
  10. # 1a. add headers line into your csv file, i.e.
  11. ```
  12. ex -sc '1i|field1,field2,field3' -cwq file.csv
  13. ```
  14.  
  15. # 2. import csv into sqlite
  16.  
  17. ```
  18. $ sqlite3 file.sqlite
  19. SQLite version 3.24.0 2018-06-04 14:10:15
  20. Enter ".help" for usage hints.
  21. sqlite> .mode csv
  22. sqlite> .import file.csv data
  23. sqlite> .tables
  24. data
  25. sqlite> .schema dat
  26. CREATE TABLE incidents(
  27.   "field1" TEXT,
  28.   "field2" TEXT,
  29.   "field3" TEXT
  30. );
  31. sqlite> select count(*), field3 from data group by field3;
  32. 3,test3
  33. 1,test1
  34. sqlite>
  35. ```
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top