Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    120

    Unanswered: Importing Data from Text/Excel

    Hi.
    I am having some issues with Data Types on an Import of data from a Text or Excel File.

    I do the first Import and create the table from that first import. I select the field types I want etc and that imports fine. When I attempt to import the next file into the same table I get all kinds of Type COnversion Errors but the data is teh same in the fields.

    The fields I am having issues with are:

    US Zip Code and Healthcare Diagnosis Codes.

    The US Zip field has the 5 digit zip and some have the Zip + Four code with a dash in between. 19606 or 19606-9920 etc.

    The Diagnosis code is either an all Numeric Code or an Alpha Numeric. IE 789.1 or V78.1.

    I've attempt various field types for these but the issue is the First Import to create the table works fine. But when I go to import secondary files into the same table I get Type Conversion Errors on these fields.

    I've tried, Text, Number and even Memo, but with memo I can't use it in a Cross Tab later.

    Any suggestions would be great.

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    You should be able to import the first file, then go to the table design view and make sure both of these field are text. Both the Zip and Diagnosis need to be text because of the dashes and letters contained in some records. Also make sure the text fields are long enough to hold the data you are importing.

    Then import the second file to the existing table. If the second import still fails looks at the import errors table that should be created during the process and it will tell you the exact row and field that had the issue. Go back and look at the raw data for this row and see if you spot the problem.

    If this doesn't get you anywhere try importing the second file first into a new table and compare the structure of the two.

    If this still doesn't resolve the problem, try temporarily linking the second file and running an append query off it to add the records to the table.

    Steve

  3. #3
    Join Date
    Mar 2009
    Posts
    120
    Thank you sps.

    I've tried all of that with the exception of the linking and appending.

    They first file works fine but thent he second file the Zip and Diagnosis codes that have the dashes and the letters in them error out. The import errors table shows all as "Type Conversion Failure". But it is only the ones with the Dashes and the Letters in them that are failing even though the field is a text field and is 255 characters long.

  4. #4
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Try the link and append. Access is more forgiving this way and it's solved many import problems for me.

    If you can't even append the data when linked then try converting it to text in the append query. If your field name is zip use zipcode:cstr([zip]) in the query.

    The issue is in the data somewhere, it's just a matter of finding it and fixing it. As you mentioned, a text field can handle a zip code with dashes, so the field data type isn't the issue.

    Steve

  5. #5
    Join Date
    Mar 2009
    Posts
    120
    Thanks. I actually got it to work. Instead of checking the box for Headers I leave it unchecked and the import, imports the headers as well but the actual data imports clean with no errors. I just have to go and delete the Header row since it is the only row that errors out on import.

    Thanks everyone for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •