Export a LibreOffice Base table as a CSV file

Previously I use Microsoft Access to manipulate and export out table data as comma separated values (CSV) files. I decided to use LibreOffice Base to do the same thing. It wasn't difficult to create CSV files in LibreOffice but the concept is different than Microsoft Office's. In LibreOffice, you have to create the text file document first before dragging the source table into the document. It took me a while to figure this out. The following steps illustrate how to do it.

1. Click Start | All Programs | LibreOffice 4.0 | LibreOffice.
   
   LibreOffice appears.
   
   
2. Click Text Document.
   
   A blank Writer document appears.
   
   
3. Press F4.
   
   The Data Source pane appears.
   
   
4. Expand the database nodes until the desired table e.g. biblio, is visible.
   
   
   
5. Drag and drop the table onto the blank document.
   
   The Insert Database Columns dialog box appears.
   
   
6. Toggle on Text.
   
7. Select a Database column to export, e.g. Booktitle. Then click >. Repeat for additional columns, e.g. Author and Publisher.
   
8. Edit the format string to include a comma or any other delimiter between the columns.
   
   
   
9. Click OK.
   
   The table data is placed on the document.
   
   
10. Select File | Save As.
    
    The Save As dialog box appears.
    
    
11. Type in a file name e.g. mycsvfile. Choose the type as Text. Click Save.
    
    The CSV file is created.

Import GeoMedia Access Warehouse features into gvSIG

I recently installed and tried out the open source Java GIS application gvSIG OADE version 2010 from Oxford Archaeology along with a free extmdb extension to read features from a GeoMedia Access warehouse from the Google Code project site. I found the process to create a gvSIG project and import GeoMedia features to be relatively straightforward. Maybe the one thing to take note of is that the loaded GeoMedia features are temporary in nature only and must be saved permanently as gvSIG compatible formats e.g. Shape file.

In this example, I am going to import in a feature from the GeoMedia sample warehouse USSampleData.mdb file, which uses the Albers Equal Area coordinate system.

Create a new gvSIG project and assign a coordinate system

1. Select Start | All Programs | gvSIG OA Digital Edition 2010 | gvSIG OA Digital Edition 2010.
   
   The gvSIG OADE 2010:Untitled application appears.
   
   
2. In the Project manager window, click View | New.
   
   An Untitled-0 view is created and added to the View list box.
   
   
3. In the View group box, select Untitled-0. Click Properties.
   
   The View properties dialog box appears.
   
   
4. Click the Current projection button.
   
   The Spatial Reference System (SRS) dialog box appears.
   
   
5. Click the Type combo box. Choose a suitable type e.g. ESRI.
6. Toggle on By name.
   
   
   
7. In the Search criteria field, type in Albers. Click Search.
   
   A list of matching results appear.
   
   
8. Select USA_Contiguous_Albers_Equal_Area. Click OK.
   
   
   
9. Click OK again.
10. In the View group box, click Open.
    
    The View:Untitled-0 window is opened.
    

Import GeoMedia features

1. Select View | Add Layer.
   
   The Add Layer dialog box appears.
   
   
2. Click the MDB tab.
   
   
   
3. Click the Search button.
   
   The Open dialog box appears.
   
   
4. Browse and select a GeoMedia MDB file e.g. C:\Warehouses\USSampleData.mdb. Click Open.
   
   The GeoMedia feature names are displayed in the Feature Classes grid.
   
   
   
   
5. Toggle on the feature(s) to add, e.g. States. Click OK.
   
   Processing messages appear and the selected features are imported.
   
   
6. Close the message box.
   
   The imported features are displayed in the view.
   

Export GeoMedia feature(s)

1. In the View legend, click the feature name to be exported e.g. States.
   
2. Select Layer | Export To | Shapefile.
   
   The Save dialog box appears.
   
   
3. Type in the file name e.g. States. Click Save.
   
   Processing messages appear.
    
   
4. Click Yes.
   
   The exported shapefile is added as a layer to the view.

Remove the GeoMedia warehouse from the view

1. In the View legend, mouse right click on the warehouse name e.g. USSampleData.
   
   A pop up menu appears.
   
   
2. Choose Delete.
   
   A prompt appears.
   
   
3. Click Yes.
   
   The GeoMedia warehouse is removed from the view.

Save the gvSIG project

1. Select File | Save project.
   
   The Save project dialog box appears.
   
   
2. Type in a file name, e.g. geomedia.gvp. Click Save.
   
   The project is saved.

Bulk update Microsoft Access database with VBA

Sometimes I want to perform a simple bulk update of records in a Microsoft Access database using plain SQL text as shown below.

UPDATE STORES SET BUILDING='EMPIRE STATE BUILDING' WHERE ID=10001;
UPDATE STORES SET BUILDING='BIG BEN' WHERE ID=100560;
UPDATE STORES SET BUILDING='EIFFEL TOWER' WHERE ID=103891;
...etc..

Previously, I wrote a Perl script that uses ODBC to do the job as described in my previous post. However, , I found myself in a situation recently without Perl and the DBI-ODBC module on my working computer. So I decided to explore bulk updating using Microsoft Access's built-in VBA engine. The following describes my method.

Create a public function in a VBA Module
First, create a function that executes the SQL statements in a series or in a loop.

1. Start up Microsoft Access and open up a database to be updated.
2. Press ALT+F11.
   
   The Microsoft Visual Basic editor appears. By default, a new module - Module1 is created.
   
3. In the editor, create a new public function e.g. BulkUpdate by typing in the following VBA code:
   
   
4. Using any text editor e.g. Notepad, enclose the bulk update SQL statements with double quotes and prefix with the statements with db.Execute as shown below.
   
   
   
   
   db.Execute "UPDATE STORES SET BUILDING='EMPIRE STATE BUILDING' WHERE ID=10001;"
   db.Execute "UPDATE STORES SET BUILDING='BIG BEN' WHERE ID=100560;"
   db.Execute "UPDATE STORES SET BUILDING='EIFFEL TOWER' WHERE ID=103891;"
   ...etc..
5. In the VBA Editor, copy and paste the bulk update sql text into the public BulkUpdate function.
   
   
6. Select File | Save.
   
   The Save As dialog box appears.
   
7. Optional. In the Module Name field, type in a name e.g. Module1. Click OK.
   
   The module is saved.
8. Select File | Exit to close the VBA editor. 

Create a Macro

Next, create a Microsoft Access macro to call the previously created public function.

1. Click New as shown in the figure below.
   
   
   
   The Macros dialog box appears.
   
2. Click the Action drop down list. Choose RunCode.
   
   The Function Name field appears.
   
3. In the Function Name field, click the Build button.
   
   The Expression Builder dialog box appears.
   
4. Double click Functions.
   
   The available VBA module names appears.
   
5. Choose the current database e.g. test. Choose the previously create module e.g. Module1. Double click the previously created public function e.g. BulkUpdate.
   
   The selected function appears in the expression field.
   
6. Click OK.
   
   
7. Close the Macro dialog box.
   
   The prompt appears.
   
8. Click Yes.
   
   The Save As dialog box appears.
   
9. Type in a macro name e.g. Macro1. Click OK.
   
   The macro is saved.

Run the bulk update

1. In the Database dialog, choose Macros. Choose the previously created macro e.g. Macro1.
   
   
2. Click Run.
   
   The selected macro executes the BulkUpdate function, which performs the bulk updating of records.

The example VBA code above is quite simple; there is no error handling. If you have the inclination, it can be made more sophisticated to handle errors in the bulk update statements. But for my needs, it was sufficient. 

Batch Convert Microsoft Access MDB Files

If you have a lot of Microsoft Access database files in older MDB formats, it can get tedious having to open each MDB file individually and selecting Tools > Database Utilities > Convert To Access xxxx. I found out that you can do the conversion in the Windows Command Prompt window by passing in the name and path to the old MDB file, and the new name and path to the new MDB file as arguments to the MSACCESS.EXE executable. An example is shown below:

"C:\Program Files\Microsoft Office\Office\msaccess.exe" "C:\Path\To\yourOldFile.mdb" /convert "C:\Path\To\yourNewFile.mdb"

If you prefer, you can create a DOS batch file or VBSCRIPT and loop through all the MDB files you want to convert.

Bulk Update Microsoft Access Database

Introduction

Microsoft Access is one of the easiest to use database application software with a lot of wizards and graphical utilities to guide the users along. For these reasons, I like to use it for prototyping database designs and processing small sets of data. One of the annoyances with Microsoft Access is that the Query tool does not allow me to run bulk SQL statements at one time as shown in the figure below.

This post shows a simple method to perform a bulk update of records in a Microsoft Access database from SQL statements in a text file using Perl and ODBC. I will also provide a link to download the Perl program

Software Prerequisites

1. Download and install ActiveState Perl.
2. Download and install the ODBC Driver for Perl DBI.

Notes: To download and install the ODBC Driver, select Start > All Programs > ActiveState Perl 5.x.x Build xxx > Perl Package Manager (see the figure below). Look for the package DBI-ODBC and mark it for install. Finally, run the installation.


Define ODBC Data Source

If you know how to use the ODBC Data Source Administrator to define a data source name, then you can skip this section.

1. Select Start > Control Panel.
   The Control Panel dialog appears.
   
2. Click the following: Performance and Maintenance > Administrative Tools > Data Sources (ODBC).
   The ODBC Data Source Administrator appears.
   
   
3. Click Add.
   The Create New Data Source dialog appears.
   
   
4. Choose Microsoft Access Driver (*.mdb) and click Finish.
   The ODBC Microsoft Access Setup dialog appears.
   
   
5. Type in your Data Source Name and click Select and choose your Microsoft Access database file.
   
6. Click OK to close all the dialog boxes.

Create Bulk SQL Text File
You can use any tool or text editor to create your text file containing the bulk SQL statements. An example file is shown below.

UPDATE STORES SET BUILDINGNAME='Post Centre' WHERE ID=34;
UPDATE STORES SET BUILDINGNAME='Empire State Building' WHERE ID=3;
UPDATE STORES SET BUILDINGNAME='Twin Towers' WHERE ID=1;

Using the BulkSQL Program

1. Download and the BulkSQL.pl program from this link and uncompress the file into a folder of your choice.
2. Open a Command Prompt window.
3. At the prompt, type in the following commands to run the program.
   
   Note: Replace mydatabase with the actual name of your ODBC data source name to your Access database file. Replace mybulksql.txt with the actual name of your bulk SQL text file.
   
   C:\> perl bulksql.pl mydatabase mybulksql.txt
   
   A sample run is shown in the figure below.
   
   

Notes:

• The program will print out the number of records affected by a single SQL statement.
• The program will print out the SQL statement if it did not affect any records.