Auto importing into access - forcing field type

Question

I have a table in a Microsoft Access database which I import data into every day from daily performance reports. A particular field in the imported data (referring to the site name), is a text field in the source data for most reports however is a mix between text fields (e.g. Site 00415) and numerical fields (e.g. 00415) for one report. When the data was being stored in Excel I forced the numerical sites to be text since some of them had leading zeros.

The Access table has the data type as Text (and format @ should that be relevant) but when importing the reports with numerical sites I get import errors on the lines which have text entries since it has tried to import the whole column as numbers rather than text.

Is there a way to force the field to be imported as text even when the first line of the file (which I believe is what access bases the field type on) is numerical?

Alternatively, should I change the field type to something else which can handle text and numbers but importantly leaves the number as is without formatting in any way.

For what it is worth - I have been importing the data via the saved import method and then via scripting with the following script and experienced this problem in both import methods:

Option Compare Database

Sub import_test()
  Dim strXls As String
  strXls = "C:\Users\me\Desktop\IMPORT.xlsx"

  DoCmd.TransferSpreadsheet acImport, , "ImportDB", _
    strXls, True, "A1:Z100"

End Sub

Answer 1

By Default, Access will only check the first 24 rows of data in order to determine the suggested types for the fields.

If your data is all numeric for the first 24 rows, then Access may have guessed at the incorrect data type for your field. That data-type is saved along with your Saved Import, and Access makes it hard to view the field type that was saved.

It sounds like your table is set up with Short Text and a @ Format. That is ideal for your needs.

You'll need to make sure that your Excel file has text formats for the values - particularly the values with leading zeros. You'll then need to run the Import Excel File Wizard again, but this time be sure to explicitly set the field types for each column, taking particular note of the problematic field - You may find that Access assumes the field should be Double, when it should be Short Text.

Once you've done that, you'll have to use the DoCmd.RunSavedImportExport approach, because the TransferSpreadsheet approach doesn't let you specify any field type detail for Excel files.

DoCmd.RunSavedImportExport "MyImport"

And here's some text-formatted values in a text field.

Should you have ongoing issues, you might have to export the Excel file to a text file format, and then use Access to Import the file, but I doubt you'll need to do that.