Unparsable error when importing excel data to Microsoft Access

Question

Working on importing data from an excel file into Microsoft Access (2007-2010), the import has gone smoothly, being able to properly have the appropriate check boxes checked or unchecked and other fields that has no discrete data populating as they should.

However, in the Access data base, we have a few columns that are from pick lists (a table referring to another table for what options are available), to limit the options to those filling out a form. A few of the pick lists are single select while others are multi-select. When the import runs, for those that have pick lists, an error of “Unparsable Record” occurs for those columns and no data is entered. This error occurs even if the employer is a perfect match to one of the options. As a result, manual entry has to be done. Is there a work around to this error while not changing the pick lists or how the form is configured?

I found this, but hope there is something that can be done to solve this issue to save some very tedious manual data entry. https://community.spiceworks.com/topic/287861-getting-unparsable-record-when-importing-to-access-2010

Answer 1

I think I understand your issue.

Your Access table ("table1") has a field named "FK1" say, which is used on a single select combo box on a form. This will probably have a numeric data type of Long.

The number stored in the FK1 field contains a value that is in another table ("table2") and is the primary key of this table.

As you are loading data from excel into table1, the values in the excel column you are trying to load into the field FK1 must be numbers not text.

If they are not numbers, then you need to convert the text into numbers.

To do this you need to: 1. take the data in table2 and add it into a new sheet of your spreadsheet. 2. in the existing excel table add a new column and use a VLOOKUP formula to convert all the text to numbers.

Then load these numbers. (You will obviously need to remove the column that has the text values)

With regards to multiple select combo boxes, it is a similar problem but it is much harder to solve. It comes down to what format your excel data is in!

=============================================================

PART 2. To extract the cells with multiple value in them.

Here's some ideas:

1. See answer #6 on this page

Which uses VBA and will help you.

Prior to reading this I was thinking along these lines:

2. you load all the data from excel into you access table except for the multivalued column

you then load the multi value column by

3. use text to columns to split the values in single cell into multiple columns.

4. You then need to normalise the data. You can use the MS powerquery addin to do this - powerquery refers to this as pivoting the data. A youtube video I have done might help you see here

5. You then use an insert statement to just insert the multi valued items (probably using VBA)

Note that the normalised data is required to allow the primary key to be accessed

This link is also useful How to insert data into a multi valued field

Note the benefit of working with data in excel is that you can use powerpivot to normalise it. If you load all the data into access (as suggested in another answer) you will need to write VBA to normalise it - which might be tricky for you. However, using the data in access could work as well. You simply load all the excel data into a temp table. The multi value columsn will be stored as text in one column. You use VBA to work with these values.

Answer 2

In general, I find it easiest to import Excel data into a temporary table. You can then run a query within Access to validate the values and link up lookups. This can all be coordinated from VBA so that the temp table is cleaned up afterwards.