Documenting the Import-Export Specification System Tables

At the end of my article about creating and editing import specifications, I teased that there was an alternative to relying on the built-in import wizard, especially for editing existing specifications.

Creating and Editing Import Specifications via the Wizard

For one-off text file data imports, it’s hard to beat the simplicity of Microsoft Access’s built-in wizard.

No Longer SetMike Wolfe

Here's the relevant bit from that article's conclusion:

In summary, the Text Import Wizard provides an easy way to bring text file data into Access.

Saving the spec lets you reuse the mappings. But changing an existing spec is cumbersome. You have to abandon your code and restart the wizard even to make minor changes.

There is another way that involves direct editing of system tables, but that is a topic for another day...

As you might expect, Microsoft does not want to encourage direct editing of system tables, so these tables and their contents are not documented.  Let's fix that.

The System Tables

There are two system tables that hold all the import and export specifications for an Access application:

• MSysIMEXSpecs: (parent table) contains information and settings that apply to saved specifications as a whole
• MSysIMEXColumns: (child table) contains import/export settings that apply to the individual columns of data being imported or exported

To view these tables in the Navigation Pane, you need to right click in the navigation pane, choose "Navigation Options...", check the box for [√] Show System Objects, then click [OK].

I describe the purpose of each field below, but I don't give the field types, as that information is trivially easy to discover by opening each table in Design View.

MSysIMEXSpecs

This table has thirteen fields:

1. DateDelim: the character used to delimit dates, such as - or /
2. DateFourDigitYear: Yes, if dates have four-digit years; No, if dates use two-digit years
3. DateLeadingZeros: Yes, if single-digit months and days include a leading zero (e.g., Yes: 2023-11-08; No: 2023-11-8)
4. DateOrder: a number that represents the date order as shown here:
   DMY = 0
DYM = 1
MDY = 2
MYD = 3
YDM = 4
YMD = 5
5. DecimalPoint: the character used as a decimal point, such as . or ,
6. FieldSeparator: the character used to separate fields in delimited text files (e.g., ,, |)
7. FileType: corresponds to the "Code Page" setting, including these common English-language settings:
   437: OEM US
-535: UTF8
1200: UCS2 LE BOM
8. SpecID: surrogate primary key field generated by Access (not visible in spec setup dialog)
9. SpecName: saved name of the specification
10. SpecType: a number that represents the file format as shown here:
    1: Delimited
2: Fixed Width
11. StartRow: the number of rows to skip before beginning an import:
    0: First row contains data, not field names
1: Most likely, the "First Row Contains Field Names"
2: Begin the import at row 3 (i.e., skip the first 2 rows)
3: Begin the import at row 4 (i.e., skip the first 3 rows)
12. TextDelim: character used to qualify text, such as " or '
13. TimeDelim: character used to separate time values, such as :

Here's a screenshot of the saved import specification from my Seinfeld example.  Note the SpecID (8) and StartRow (11) properties do not appear on the import specification settings form.

MSysIMEXColumns

This table has eight fields:

1. Attributes: a number (likely a bit mask) that presumably tracks attributes of the column; may be reserved for future use (a pure guess on my part); in my experience, this number has always been 0
2. DataType: a number that represents the data type, as enumerated by DAO.DateTypeEnum, including:
   5: dbCurrency (Currency)
8: dbDate (Date with Time)
10: dbText (Short Text)
3. FieldName: the name of the field
4. IndexType: a number that represents the index type as shown here:
   0: No Index
1: Yes (Duplicates OK)
2: Yes (No Duplicates)
5. SkipColumn: Yes, if the column should be skipped and not imported; otherwise, No
6. SpecID: foreign key to the MSysIMEXSpecs table
7. Start: 1-based index of character column where field begins (Fixed Width imports only)
8. Width: number of characters to include in the field (Fixed Width imports only)

Delimited File Format

Fixed Width File Format

Proceed with Caution

Editing these system tables directly is well outside the bounds of what Microsoft intends for you to do.  

In other words, you're on your own if you screw something up.

With this in mind, I recommend using functions, subroutines, or–better yet–class modules to add a layer of safety and reliability to your interactions with these system tables.

Once again, that is a topic for another day...