Results 1 to 7 of 7
  1. #1
    deekadelic is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    23

    Interpreting "Row" in the Import Errors table.


    I'm doing an Import to Access from Excel, and some of the field values are throwing import errors. Access sticks these values into a table called ImportErrors; but I don't know how to interpret this table. It gives you the error type, the field, and the Row (not the offending value, though). So I go into my source data and look for the error by Row; but it's not clear what Access means by "Row". Row where? I've checked the source data, and there is no data in the field in question for the Row given, except in a few cases. In the absence of clarifying information, it would seem logical to conclude that "Row" is referring to the location of the record in question in the source table; but as I said, that doesn't seem to lead me to the problem record(s).

    Does this "Row" refer to something different than what I'm thinking? I don't see why the third field in the ImportErrors table wouldn't just show you the offending value itself.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    it usu imports exactly as it is in the spreadsheet. The 2nd row enters the table as 2nd row, as is, no sort.
    because its an error , it cant show the field.
    NOW, what happens is, Access gets confused on what data type it is. The 1st 10 values of a field may be a number so it assumes number, but then 11th is a string, which is an error since access already created the field as numeric.

    i correct this by setting EVERY item in the excel field to string. Before import i run a macro that scans the field putting a single quote in front of every value. Save, then import.
    This fixed 99.99% of my errors.

    Code:
    'this is ONLY for items in every row
    Sub Cvt2Text()
    
    While ActiveCell.value<> ""
       if left(activecell.value,1)<>"'" then    ActiveCell.value= "'" & ActiveCell.value
       ActiveCell.Offset(1, 0).Select
    Wend
    End Sub

  3. #3
    deekadelic is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    23
    Can that be done with conditional formatting (something along the lines of: "For any column on this matrix with data type TEXT, concatenate a single quote to the beginning of each value")?

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Can that be done with conditional formatting (something along the lines of: "For any column on this matrix with data type TEXT, concatenate a single quote to the beginning of each value")?
    No. Formatting/conditional formatting just controls how the data looks, NOT how Access interprets the data type.
    Formatting data for import did not work for me when I tried it......

  5. #5
    deekadelic is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    23
    Okay, thank you for the clarification. A question about the ImportErrors table, though:

    I've attached a screenshot of the first 11 rows of the ImportErrors table, and another screenshot of the first 20 rows of the source data table, for the field of interest. As you can see, ImportErrors says that records 11 and 18 contained the first two problem values; but when you look at the screenshot of the source data, there is nothing about the values in either row 11 or 18 that would explain why those two rows in particular should have thrown the errors.

    I'd love to understand this.

    Thank you
    Attached Thumbnails Attached Thumbnails First_20.png   Untitled.png  

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, it looks like the source data is a spreadsheet and the "numbers" are TEXT (because they are left justified). I see the error indicator also.

    I used to try and import spreadsheets into Access - now I arrange the data in the spreadsheet to what I require, then save it as a CSV file and import the CSV file using an "Import Spec".

    Try saving the spreadsheet as a CSV file, then open the CSV file with something like NotePad++ (not MS Notepad). There is a setting that will show all characters - you will be able to see if there is a strange char in the spreadsheet.
    Or post the offending spreadsheet for testing.

    Maybe some of the rows have NULLs and other rows have empty strings. Importing is a tricky business - I spent hours getting a spreadsheet to import.


    Might also try importing the spreadsheet to a table created by Access, then use queries/code to move the data to the proper table/fields..

  7. #7
    deekadelic is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    23
    I agree that text files are far preferable to Excel files when trying to import to Access; but in this case I tried CSV, but some of the data has commas in it, so that failed. And for some reason neither Tab or Pipe delimited files would import properly.

    Maybe I just need to do a more granular data cleanse before saving to text format. As you pointed out, there are the issues of strange characters,as well as NULLS vs zero-length strings, etc.

    I'll give it a go and see what happens.

    Thanks for the help.

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

Similar Threads

  1. Replies: 11
    Last Post: 07-20-2015, 06:07 PM
  2. Replies: 14
    Last Post: 06-29-2015, 06:29 PM
  3. Replies: 4
    Last Post: 08-11-2014, 01:18 PM
  4. Replies: 4
    Last Post: 02-13-2013, 02:35 PM
  5. Replies: 1
    Last Post: 08-23-2012, 08:32 AM

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