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.
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.
This table has thirteen fields:
- DateDelim: the character used to delimit dates, such as
- DateFourDigitYear: Yes, if dates have four-digit years; No, if dates use two-digit years
- DateLeadingZeros: Yes, if single-digit months and days include a leading zero (e.g., Yes:
- DateOrder: a number that represents the date order as shown here:
DMY = 0
DYM = 1
MDY = 2
MYD = 3
YDM = 4
YMD = 5
- DecimalPoint: the character used as a decimal point, such as
- FieldSeparator: the character used to separate fields in delimited text files (e.g.,
- FileType: corresponds to the "Code Page" setting, including these common English-language settings:
437: OEM US
1200: UCS2 LE BOM
- SpecID: surrogate primary key field generated by Access (not visible in spec setup dialog)
- SpecName: saved name of the specification
- SpecType: a number that represents the file format as shown here:
2: Fixed Width
- 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)
- TextDelim: character used to qualify text, such as
- 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.
This table has eight fields:
- 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
- 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)
- FieldName: the name of the field
- IndexType: a number that represents the index type as shown here:
0: No Index
1: Yes (Duplicates OK)
2: Yes (No Duplicates)
- SkipColumn: Yes, if the column should be skipped and not imported; otherwise, No
- SpecID: foreign key to the MSysIMEXSpecs table
- Start: 1-based index of character column where field begins (Fixed Width imports only)
- 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...