1

When I import an Excel worksheet into Access, it automatically assigns each column a data type based on what is in the first couple of rows and independent of what the Access table field type is.

In my case I have a worksheet I'm importing using the TransferSpreadsheet command but when it imports the data, one of the columns fails to import and yields a "Type Conversion Failure". The field is formatted as "General" in Excel and as "Short Text" in Access 2016. If I format all the data in Excel as text then it imports okay.

The first few entries are numbers so it appears Excel assumes the whole column data type is numerical and any non-numeric entries generates the import error table with the Type Conversion Failure entries.

My question is : Is there a way to prevent Excel from auto-assigning a field data type and causing data loss when importing to Access? Or do I have to manually change the data in Excel to prevent that from happening?

Improve this question
2
  • 1
    what is the exact error message for the " throw up an error on import" part?
    – p._phidot_
    Commented Apr 5, 2019 at 18:36
  • Good question, my wording was a little vague. I've edited it for clarity. What it did was generate the import error table with those entries that were type conversion failures. It imports the rest of the table okay.
    – ChrisTech
    Commented Apr 5, 2019 at 18:40

2 Answers 2

Reset to default
1

You have two choices to handle the issue:

Option 1:

  • Before Import Sheet to Access, select entire database (Excel Data Range) and set Cell format type TEXT.

Then after if you Import you find all Columns has only one Data Type is TEXT.

Option 2:

  • Make a Table in Access and then Import the Sheet using option Append Copy Of The Records To Table.

  • Select the Access Table , finish with Ok.

    Access will skip Field Type Selection.

Improve this answer
3
  • It is a shame there isn't a command to inform Excel not to auto format ranges.
    – ChrisTech
    Commented Apr 10, 2019 at 13:12
  • Took too long with my edit to my first comment. Edit: Option 1 appears to work as long as I don't need to append the data to an existing table in Access. Option 2 has the same issue as the VBA command and generates the error table.
    – ChrisTech
    Commented Apr 10, 2019 at 13:23
  • @ChrisTech,, not in fact,, Option 1 avoids to select data type while import Excel data for each field. And Option 2 simply translates in coming Excel data as the Access Table structure (data type) since user Append the Excel data and in this way it avoids Data type selection. Other wise there is no other way available to skip Data type section while importing data !!
    – Rajesh Sinha
    Commented Apr 11, 2019 at 5:53
0

If you are able to sort the file you are importing, and sort it in descending order, it will place values with text at the top of the file. The datatype will not be determined incorrectly and should succeed for you.

Improve this answer
1
  • This does work as long as there is only one range with this issue but if there are multiple ranges it may prove problematic. For my immediate purposes this will require some additional code complexity with how I'm handling the workbooks but that's okay since it works.
    – ChrisTech
    Commented Apr 10, 2019 at 13:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .