Working with CSV’s in SQLITE

I needed to manipulate a largish csv but Excel’s performance was slowing me down. SQLite is a powerful and portable tool that saved the day and made my life a lot easier.

Download SQLite here
run sqlite3 from a terminal / commandline. 
#sqlite3 mytest.db

Import the CSV file:
sqlite> .mode csv
sqlite> .import users-sql.csv users

Check its been imported ok:
sqlite> .schema
CREATE TABLE IF NOT EXISTS "users"(
  "samaccountname" TEXT,
  "DistiguishedName" TEXT,
  "whenCreated" TEXT,
  "lastLogonDate" TEXT,
  "pwdLastSet" TEXT,
  "accountExpires" TEXT,
  "userAccountControl" TEXT,
  "Lookup " TEXT,
  "Enabled" TEXT
);

example query
sqlite> select samaccountName from users;

Now you can go wild and do left joins against other data as well as do fast sql searching and report.

To create a new table:
CREATE TABLE filtered_users AS
select * from users where DistiguishedName NOT LIKE '%OU=Disabled%'
AND DistiguishedName NOT LIKE '%OU=Disabled%'
AND DistiguishedName NOT LIKE '%OU=Groups%'

This will create a new table called filtered_users from the users table.


To Export your SQL query to a csv file:
sqlite> .headers on
sqlite> .mode csv
sqlite> .output export_data.csv
sqlite> SELECT *
   ...>   FROM filtered_users;
sqlite> .quit