Page 2 of 2 FirstFirst 12
Results 16 to 19 of 19
  1. #16
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That is exactly what I did, with the same results and is how I knew the rows were text. What I find odd is that you find this surprising and abnormal. How on earth would you expect Access to convert text to any type of number when the text doesn't resemble a number? 123 as text will covert to a number, but what number is "apple"?

  2. #17
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with Micron. I do not understand how /why you think that adding text to a numeric makes sense or is logical.
    For example: Suppose you were asked to add "Happy Birthday" to your current salary--what would you expect as an answer?

    Also, in your sample data, Invoice number was imported as a Double. Would anybody expect to be doing arithmetic with an Invoice Number?


    If I were in your situation, I would create a table structure in Access to store the data in the desired datatype and use it as a template for other imports. Then there would seem to be 2 choices:
    1- adjust the Excel structure to use appropriate data type (no defaults, be explicit)
    2- if no adjustment could be made, then put a consistent process between Excel and Access to do a conversion
    In 2, I'm thinking things like make Invoice Number a text field, telephone number etc. Things we refer to as "numbers" but are never used with arithmetic. If you really need to handle Text ina numeric field, then you are going to have to read a record, check if it IsNumeric, and handle the result accordingly. However, if the received data for import is consistent, then this should be a one-time set up. If the incoming data is not consistent, then automation is not the answer. Someone will have to use "eyeballs" to vet the data, the logic and take proper action.

    Good luck.

    Also: If you are getting data from a Report (as the name of your test files seem to indicate), you have got formatted data. Depending on the use and expectations of the data in Access (or any database), you may want to get the original data closer to source before it has been "prettied up" and formatted as a report.

  3. #18
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What I see as options:
    - Get External Data tool (once to create the table from one file and again to append to that table from the other). Any import errors will be identified in an 'import errors table'. With that information, you can deal with the faulty rows however you see fit. One option might be to find the reported row numbers, edit the Excel data then copy/paste append those rows into your table.

    - Automation (VBA). From Access, locate the file (file dialog), specify the sheet, create the objects, outer loop the rows and inner loop the columns, getting the cell content. When in certain columns, if the data doesn't look like a number (IsNumeric) overwrite the contents with a number (0?) first or at least import a number instead. The former would at least ensure that the table and spreadsheet records match.

    I hope you can get some time to get this straightened out rather than experience the consequences you mentioned. After all, if the info supplied is not your doing, it's not your fault because as you may know, they say "G-I-G-O" (garbage in, garbage out). You'd need time to arrive at a reliable solution.

  4. #19
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Micron,

    I think we need to hear more of the requirements, and more detail on the process(es) involved from the source data to the desired output. Some of the expectations are not realistic, so I'm wondering if we can get past the "text should be converted to numbers" to a logical set of detailed requirements.

    We have indicated we are willing to help, so it is time for the OP to comment/provide more info.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Importing Excel Workbook with multiple Worksheets
    By MTSPEER in forum Programming
    Replies: 4
    Last Post: 04-21-2015, 01:50 PM
  2. Replies: 2
    Last Post: 06-04-2014, 11:12 AM
  3. Replies: 3
    Last Post: 05-01-2014, 02:27 PM
  4. Excel Import Errors-Type Conversion Failure
    By ejm2163 in forum Import/Export Data
    Replies: 5
    Last Post: 04-28-2013, 09:17 PM
  5. Type Conversion Failure When Importing txt File
    By Eka1618 in forum Import/Export Data
    Replies: 15
    Last Post: 10-29-2012, 06:45 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