One of the joys of Microsoft is interoperability between applications such as Access and Excel. A stunning example of this is the Import Excel spreadsheet function for importing Excel data from a worksheet which offers an easy-to-follow wizard-style feature on the External Data menu tab.

Column/field naming conventions

One of the “gotchas” however, is possible problems stemming from column headings in Excel. I am busy with some testing to see exactly what characters are and are not allowed and how Access handles them.

Spaces

In Excel, I commonly use headings that contain spaces. In Access I follow strict naming conventions for fields, including the rule: no spaces allowed. Access does however allow spaces in the names of table fields and this will not cause any problems during import.

It does not interestingly enough accept leading spaces — one would think it would simply run a CLEAN-type function to remove leading, trailing and other extra spaces in a string. Having said that, it drops trailing spaces with no problem and simply accepts multiple spaces between characters with no problem. See my post on cleaning data in Excel for more insight into these issues.

Question marks

Question marks are allowed, even as a leading character. When I tried to use a question mark on its own as a name for a field, Access completed the import, but then crashed and restarted when I tried to reopen the table.

Underscores

Underscores are accepted before, between and after characters. Underscore and space combinations as well as consecutive underscores are also accepted.

Exclamation Marks

Interesting! A heading with an exclamation mark in any position results in a warning and a conversion to the generic name, Fieldn