LDSql

Search this site

Using LDSql without programming

Getting Data In
 
For populating your LDSql database from the MLS files in the first place its easy to use the SQLite Manager add-on in Firefox. (That assumes you have the Firefox browser running on your computer, which of course you should.) Version 0.5.6 or higher of SQLite Manager is recommended.
 
Once you have that running in Firefox (under Tools), the user interface to import CSV tables is pretty intuitive:
 
  1. CONNECT to the MLS_Export_Ward.db database.
  2. Import each of the four CSV files -- Membership.csv, HomeTeaching.csv, VisitingTeaching,.csv and Organization.csv -- into its corresponding table.
    • Make sure you change the settings in the import wizard to match the CSV format from MLS -- 'First line contains column names' and 'Double quotes ("") always' and 'Ignore Trailing Separator/Delimiter'. 
    • Import the files using ISO-8859-1 as the character set, not the default UTF-8.  (SQLite Manager will convert ISO-8859-1 to UTF-8 for you.)
  3. Close the MLS_Export_Ward database.
  4. CONNECT to the LDSql_Ward.db database.
  5. ATTACH the MLS_Export_Ward database.
  6. Execute the script called Populate_LDSql_Ward.sql.  (You will need to open the script file in a text editor and copy/paste its contents into the Execute SQL window of the Firefox SQLite Manager.)
 
The final database will now be loaded.  You can browse and query it directly in the Firefox SQLite Manager, or use the ODBC connection to Open Office described below.
 
Even Easier -- Use a Dedicated Loading Application

The Ward Tools open-source project has built a special loading application that will accomplish the steps above automatically.  This free and user-friendly tool will let you browse to the CSV files above, perform the loading steps, and create the final LDSql_Ward.db database, fully populated with your data.  The Ward Tools MLS Converter 3.0 can be downloaded from the Ward Tools dev site.  There is a Windows version and a Mac version.


Getting Data Out
  
It is simple to browse and query the database in the Firefox SQLite Manager described above.  That tool allows export to CSV files and copy/paste into spreadsheets.  Or, if you want to browse and query the LDSql database using a general-purpose tools such as OpenOffice, you may install the SQLite engine encapsulated in an ODBC driver.
 
  1. You can download this free driver here.
  2. Install it on your computer.
  3. Using the SQLite3 ODBC Driver, define an ODBC connection (user or system) that points to your LDSQLite_Ward.db file.  Within the options for the ODBC connection, select Short Column Names.
  4. In OpenOffice Base (the database component of the OOo suite) create a new database that links to the ODBC connection that you created above.
You now can browse and search the LDSql database, and export the contents of tables or views to OpenOffice spreadsheet files.  (If you are more comfortable with spreadsheets, you can do such an export of a single table or view, then do your final filtering and editing within the spreadsheet.)
 
You can write simple queries withiin OpenOffice.  For example:
 
SELECT "Household", "Home Teacher 1", "Home Teacher 2"
FROM HomeTeaching_ext
WHERE "YearsSinceConvertBaptism" < 2
will retrieve the families with recent converts, and their home teachers.
 
(Beware of using the OpenOffice graphical query-builder tool.  It has a few issues with some of the SQLite datatypes.)
 
You also can use the LDSql views to drive the mail-merge wizards built into OpenOffice, to generate letters, printed envelopes or labels.  That is where the preformatted names in LDSql ("John & Jane Smith", "Brother and Sister Smith", etc.) come in handy.
 
Similarly, the ODBC connection can be used to connect to MS Access or Excel.
 
 

Sign in|Report Abuse|Print Page|Remove Access|Powered By Google Sites