Results 1 to 9 of 9
  1. #1
    jawinterton is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    5

    Importing from excel - Unparsable record error

    I'm trying to import Excel data and append it to my table in Access 2007. Whenever I try to do this I get an "unparsable record" error. The items that are the problem are all the same field for each record.

    A little bit of information about the field... this field is designed in my database to allow me to select multiple number (key) values from another table via a combo box.

    My data in Excel is listed out with the multiple key values for this field. e.g. 19, 20, 56.

    Any ideas for how to get it to import these fields??? There is way to much information to manually select each value for every record.

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    the first response here is pretty revealing:

    http://www.pcreview.co.uk/forums/imp...-t1675069.html

  3. #3
    jawinterton is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    5
    Thanks for your reply ajetrumpet. I had seen this post in my search for a solution. I don't feel it applies since I'm well under the limit of characters per record, I don't have any hidden columns, nor any formulas in the cells, or special characters.

    It seems as if Access is okay with storing multiple number values under this field, but not importing multiple number values. I've looked into copying and pasting but I'm getting paste errors. After removing the problem field it copies no problem. I really need this field to import/copy into the database to save myself hundreds of hours of data entry.

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    size of field you're targeting for import? vs. size of data to be pasted?

    type of field you're targeting for import?

    what do paste errors say? or do you know how to read that table?

  5. #5
    jawinterton is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    5
    Size of field I'm targeting for import: On average probably 2-3 number keys from the other table.

    Size of data to be pasted: On average no more than 150 characters per record.

    I'm importing numbers separated by comas (e.g. 25, 26, 49) from excel. These numbers are the unique keys. Field size is set to long integer on the database and uses a multi value lookup so I can select the proper items from the referenced table.

    I've attached a paste error for you to look at. Also included in the zip file is a copy of the data I'm looking to import. It's the field titled "scopes" that isn't coming over. By looking at the errors for "scopes" in column F8 it appears that for records that should have more than one number listed and separated by comas as in my example above, it's not listing anything. Still can't paste single records if it has even one number. It'll pate with no errors if no number for that field is listed.
    Last edited by jawinterton; 12-08-2011 at 02:16 PM.

  6. #6
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    that doesn't mean much to me without the accompanying access file.

    but you don't need it.

    notice the patterns in the xls file PASTE ERRORS:

    1) F7 => every record value is in error.
    2) F8 => NONE of the numbers that are seperated by commas are in error. That's because Access sees those as TEXT, and they are accepted.
    3) F9/F10 => all the data is being rejected.

    so if #2 is the problem field ur talking about, the commas are NOT the problem here. They are being accepted, because the acc field is probably TEXT and acc is reading #s without commas as numbers. Hence, TYPE MISMATCH error in paste errors as REASON.

    attached is what I see when I import them to a new table. Only 2 records are in error.

    are you importing to an existing tbl? if the # fld is the issue, change the cell format of the column to TEXT using Ctrl+1 on keypad. then import. If that doesn't work, loop the column and change the cell values to:

    Code:
    "'" & cell.value
    in xl, preceeding cell vals with a (') apostrophe gives the val a LITERAL meaning for a text val.

    by the way, if you've got lookup field types in your acc tables, ditch them. They seriously conflict with acc's execution. this is well documented.

  7. #7
    jawinterton is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    5
    Here's the database I'm working in. I would have provided it sooner but I thought this would be resolved sooner. I see what you're saying and I'm wondering where to go from here.

    The field within this database that I'm having the importing issue with is the "scopes" field in the "Contractors" table. The field is set to do a lookup as explained before and it uses the primary keys from the "Scopes" table. It doesn't show the primary key in the "Contractors" table because I typically do my data entry in here (I know... I should be using the forms. I just find it easier in the table.) It's easier to input data by not referring to the primary keys directly.

    It seems to me that using the text data type for the "Scopes" field would make it much less easy to use since I won't be able to select from a large list of values for each record. Am I incorrect in this assumption? Also, if I change it to text won't it be read only or cause me to loose all of my existing data in that field.

    Maybe you can take a look at my DB (I deleted tons of records to decrease the file size for uploading to this forum) and give me your input and recommendations for how to fix it so it's more functional and follows better database design/etiquette.

    You can make the changes and send it back if you'd like, but I'd like to know what you did and how you did it so I can learn.

    Thanks so much for all your help so far.
    Last edited by jawinterton; 12-08-2011 at 02:17 PM.

  8. #8
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by jawinterton View Post
    The field is set to do a lookup as explained before and it uses the primary keys from the "Scopes" table. It doesn't show the primary key in the "Contractors" table because I typically do my data entry in here (I know... I should be using the forms. I just find it easier in the table.) It's easier to input data by not referring to the primary keys directly.
    this is WAY out of whack. You're talking about the relationships that are set up, NOT lookup fields (which is the only other thing I can discern from your talk).

    Your contractors table is not at all compatible with the excel data. The table has serious problems, from an experienced developers point of view.

    But for an immediate fix:

    • ditch the lookup statements
    • quit entering data into tables
    • use forms instead (refer to previous)
    • NEVER put more than one field of data into another table field


    What you're doing is fine if all you ever do is work with Tables in Access. I kinda thought this has gone by the wayside somewhat, but apparently not. Up until a few years ago, this type of usage was classic among hard-core excel users. Hence, they would switch to Access but it would end up being a pointless effort because they continually used a different program and did the same kind of excel work that you need Excel FOR.

    Bottom line - ya got a learning curve: Either learn how to operate Access forms and understand their importance in their relationships to tables and the relationships that tables need to operate through. Or...stay in Excel and work with tables.

    Quote Originally Posted by jawinterton View Post
    It seems to me that using the text data type for the "Scopes" field would make it much less easy to use since I won't be able to select from a large list of values for each record. Am I incorrect in this assumption? Also, if I change it to text won't it be read only or cause me to loose all of my existing data in that field.
    "Read only" has nothing to do with anything. Have no idea what you're talking about.

    I'm not going to change anything. Honestly, there are many things wrong with the table. According to the import I ran, you have 5 fields that are unparseable, these:

    *Classification(s)
    *NAICS Codes
    *Scopes
    *Performs at
    *Past Jobs Bid


    All of those fields have one (or more) of the following problems associated with them, that may or may not be causing the 'unparseable' error:

    • Value List as lookup criteria
    • the fact that you HAVE a lookup statement associated with the field
    • "limit to list" property is "YES"
    • more than one column in field
    • data read from xl (by acc) is read as different data type than acc field specification (eg - SCOPE)


    none of those issues are acceptable to developers. Furthermore, none of them have any use at the table level in Acc.

    Start by eliminating all those problems. Obviously I don't have time to help you fix it, but get started and if you want me to assist, I may be able to depending on my schedule. That's enough to get you started.

    By the way, there is no change in xl that will fully complement your setup in acc as it is now. If you were thinking of trying to change the xl data, that is.

  9. #9
    jawinterton is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    5
    Thanks for explaining this to me. Still learning.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Importing from Excel
    By itm in forum Import/Export Data
    Replies: 1
    Last Post: 07-20-2011, 07:12 PM
  2. Importing an Excel file
    By bvtterflygirl in forum Import/Export Data
    Replies: 5
    Last Post: 02-24-2011, 11:54 PM
  3. Importing the second excel worksheet
    By geoffwbailey in forum Programming
    Replies: 1
    Last Post: 06-25-2010, 12:16 AM
  4. Importing from Excel
    By joer in forum Forms
    Replies: 0
    Last Post: 03-14-2007, 02:01 PM
  5. Type error when importing Excel spreadsheet
    By jlmnjem in forum Database Design
    Replies: 2
    Last Post: 08-27-2006, 07:26 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums