Microsoft Access Export Truncates Numbers

Introduction

I wanted to export all tables from an Access database into a folder of CSV formatted text files. For most data types, such as Text or Integer, all data was exported without any loss. However, floating point numbers (Float or Double) were exported with only two decimal places.

Approach

You can export a Microsoft Access table to a text file using either the menu item File / Export or this Access SQL statement from KB208408 …

SELECT * INTO [Text;FMT=Delimited;HDR=Yes;Database=<Path>].[<Table>.csv] FROM <Table>

Solutions

Here's three solutions with varying advantages:

• KB153364 article suggests formatting the number column to define the required decimal places. This solution requires writing a SQL statement for each table you're exporting.
• You can add a schema.ini into the destination export folder and specify the number of decimal places for all floating point numbers in a table using the NumberDigits keyword. This solution requires creating or copying a schema.ini file into the export directory before exporting the tables.
• You can change the No. of digits after decimal in your Regional and Language Options of your computer. This solution means you or your user has to remember to the change that setting on their computer before exporting the tables.

About Schema.ini File

A slight digression: schema.ini is a file used by the Text Driver to determine the characteristics of tables and columns of a database. You can find the syntax and properties of the schema.ini file in Schema.ini File (Text File Driver).

When exporting tables, Access creates a schema.ini file in the export folder if that file does not exist. If there is an existing schema.ini file and the section for the output CSV file does not exist, Access will add a new section for the output CSV file. Otherwise, Access does not change the schema.ini file.

Conclusion

None of the solutions are very satisfactory for writing a general program to export tables from an Access database into a folder of text files. The simplest solution is to define the required number of decimal places in the database connection string (the part between the square brackets in the SQL statement). However, I couldn't find any formal specification of this string on the Web.

Edit Base Calendar in Project 2000

How to edit a base calendar in Microsoft Project 2000:

1. Ensure that no or all rows in the Resource Sheet are selected.
2. Select menu item Tools / Change Working Time.
3. In Change Working Time dialog, select the required calendar from the drop down list, then edit the calendar.

Note that if some rows are selected in the Resource Sheet is selected, you can only change the calendar for the first resource in the selection, not a base calendar.

Hide Recycle Bin in Windows Desktop

While noodling around Windows' Group Policy Editor, I noticed that you can hide the Recycle Bin in the desktop:

1. Start Group Policy Editor using gpedit.msc.
2. Select User Configuration / Administrative Templates / Desktop.
3. Select Remove Recycle Bin icon from desktop.
4. Select Enabled radio button.

Note that Windows' Group Policy Editor is generally used to turn off features, so enabling a setting generally means to disable a function.

OpenGL for ATI Mobility Radeon in Vista

This is way annoying. I installed an OpenGL game on my Asus notebook and it ran abysmally. When I tried to test the OpenGL interface with glview, that program crashed. The ATI's Catalyst Control Center (CCC) reported that OpenGL Version was Not available.

It turns out that I have to update the driver for my notebook's ATI Mobility Radeon X1700 card and enable OpenGL support. ATI does not support any Mobility Radeon cards but I found an updated driver on the Asus site. Then I used Mobility Modder tool and it enabled OpenGL. Now my system has OpenGL version 6.14.10.7275 and glview runs to completion.

Whew! Thank goodness Mobility Modder worked since I wasn't looking forward to hacking .INF files without knowing anything about configuring video adapters.

Microsoft Excel Remove Empty Lines

If you get an Excel document that has empty rows, you can remove those empty rows by using the sort function (Data / Sort). Of course, you lose the original row order.

Another method is to use the "Go To" (F5) dialog to select all blank cells, then delete them (found in Joseph Rubin's Excel Tips).

Copy-Paste Image from CHM to Microsoft Word

If you copy-paste an image from a CHM file to Microsoft Word, you end up with a blank rectangle in your Word document. The workaround is to paste the image to a drawing program (e.g. MSPaint), then copy-paste from the drawing program into Word. Why you need to do this two-step process?

Microsoft Outlook Rules and Duplicated E-mail

If you use Microsoft Outlook's rules to move incoming messages into folders, you could end up with duplicate e-mail messages if two or more rules can be applied.

Recently, I made new rules to move bug reports for some products into special product folders and I also had a "catch-all" rule to move all remaining bug reports into a general bug report folder. In other words, I had the following rules:

From bugsystem, Subject "product_1", move to "product_1" folder.
From bugsystem, Subject "product_2", move to "product_2" folder.
From bugsystem, move to "general" folder.

I thought once a rule was "fired" for a message, Outlook would process the next message. However when new bug reports arrived, Outlook created two copies of each message, one in the product's folder and one in the general bug report folder. It appears that Outlook processes all rules for a message before proceeding to the next message. Select the stop processing more rules action for each rule to make Outlook stop processing further rules for that message:

From bugsystem, Subject "product_1", move to "product_1" folder and "stop processing more rules".
From bugsystem, Subject "product_2", move to "product_2" folder and "stop processing more rules".
From bugsystem, move to "general" folder.

In Outlook 2003, Rules and Alerts dialog, you can see a hammer and spanner icon for each rule containing the stop processing more rules action.

AutoIt Include Directory

Note to myself to remember to set the local Include directory value for AutoIt when I use another computer. The registry key is HKEY_CURRENT_USER\Software\AutoIt v3\AutoIt.

Firefox It's All Text Extension

The Firefox add-in It's All Text is a badly named but somewhat useful extension. After installing it, it didn't become useful until I had to read long bug descriptions in small textarea fields. Using this extension, I can open a text editor window with the contents of a textarea field, edit, print, save, etc., and even write the changes back into the field.

For a while, I used Resizable Form Fields to enlarge the bug description fields but sometimes I accidentally resize small controls such as drop-down lists, so I stopped using that extension.

Excel Print Preview Cannot Repeat Row or Column

Quirk in MS-Excel: You can't select rows or columns to repeat if you open the Page Setup dialog in Print Preview (see knowledge base article 912069). The work around is simple: close Print Preview and select the menu item File / Page Setup.

Workaround Gmail Continuously Loading Problem

A note on how to work around the problem when Google's Gmail continuously loads in Firefox but never completes, and reloading the page or restarting Firefox does not fix the problem. There's three URLs for your Gmail account, so use the second or third alternative listed below:

• HTTP: http://mail.google.com/mail/
• HTTPS: https://mail.google.com/mail/
• Plain HTML interface: https://mail.google.com/mail/?ui=html&zy=a

Failing that, try:

• Clearing your Firefox cache in Tools / Options / Advanced / Network / Cache.
• Older Gmail interface: http://mail.google.com/mail/?ui=1.

Some days later, either Firefox or Gmail seems to recover. It's only happened to me twice and I haven't had time to investigate too deeply.

2008-05-01: Added couple more work-arounds for older version of Gmail and clearing Firefox cache.

Prune Directories with Python

I converted my earlier PowerShell script to prune directories to Python:

     1  from os import listdir, rmdir
     2  from os.path import isdir, exists, join
     3
     4  def prune_directory(path):
     5    if len(path) < 1:
     6      print "Empty path"
     7      return
     8    if not exists(path):
     9      print "Invalid path:", path
    10      return
    11    if not isdir(path):
    12      return
    13    if len(listdir(path)) <= 0:
    14      rmdir(path)
    15      return
    16    for elem in listdir(path):
    17      prune_directory(join(path, elem))
    18    if len(listdir(path)) <= 0:
    19      rmdir(path)

It's almost a one-to-one translation from the initial PowerShell version. The difference is that in Python, you have to ensure that path is a directory before you call listdir(path).

Python 2.5 has a new generator function os.walk() for traversing a directory tree. Below, the recursive function call in lines 13-19 is replaced by a for loop in lines 13-14.

     1  from os import listdir, rmdir, walk
     2  from os.path import isdir, exists
     3
     4  def prune_directory_walk(path):
     5    if len(path) < 1:
     6      print "Empty path"
     7      return
     8    if not exists(path):
     9      print "Invalid path:", path
    10      return
    11    if not isdir(path):
    12      return
    13    for curr, dirs, files in walk(path, topdown=False):
    14      if len(listdir(curr)) < 1: rmdir(curr)

In this sample, os.walk() returns a tuple (curr, dirs, files) for each directory it visits. curr is the current directory being traversed, and dirs and files are the directories and files in that directory. Using the parameter topdown=False, os.walk() starts producing these tuples from the lowest descendant directory and working up to the start directory, path.

Note that the loop's conditional statement uses len(listdir(curr)) instead of just len(dirs). os.walk() generates the dirs list before it visits each of the directories in dirs; if all child directories in dirs have been deleted, dirs would still contain an unchanged list and the parent directory, curr, would not be deleted. At least, that's what I think happens; the Python help doesn't say so explicitly.

In earlier versions of Python, there is a similar function called os.path.walk() but os.walk() is much easier to use.