Results 1 to 7 of 7
  1. #1
    collekt is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    4

    Type Conversion Failure - Importing From Text File

    I have a pipe delimited text file that I'm trying to import into an existing table in Access. There is an account id field that contains mostly numeric account numbers, but some of them have text as well. The field it's going into on the existing table is set to a short text field, so I thought it should work fine.



    I read elsewhere some things about Access looking at the first several lines to determine the data type. I wasn't sure if this applied to importing into an existing table, but even if I make the first row's value something with text in it, it still errors on that field and all the other ones with text.

    The error I'm getting is "Type Conversion Failure" on the rows with text in this field. Any ideas on what the issue is or a possible solution? Many thanks in advance for any help.

    Examples of the field's data:
    1234567
    2345678
    ACCT12345678

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Often the only way around these sort of issues is to use a "staging" table.
    Import everything into that temporarily, then you can control what data goes into what final destination table and the datatype.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    collekt is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2013
    Posts
    4
    Well that's certainly less than ideal, but thanks for the help. Do you know why this is an issue? Just trying to understand why I can't just import into a field that's already designated as text.

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You can try using a saved import specification, but for some reason in Access they seem about as reliable as Donald Trumps golf handicap....
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    afaik, this is a problem for imports or transferspreadsheet function. Have read same, it's 8? rows or so, but what I read was about Excel sheets, not delimited text files. You can play around & test on various configurations (all numeric, all text, first numeric but lots of it then text) etc. to see what happens. Or you could link to the file as a table and use a query on it to append/update to a table you manually build first. That table's fields should be of the correct data type. In so doing, this is supposed to eliminate the problem, but this is not something I did so much as read about.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I can confirm that using a 'buffer' or staging table works for this situation but I suggest one extra step.
    If there is any possibility of a field being wrongly interpreted then also specify the datatype in your query or recordset
    e.g. CDate(DOB) ; CCur(SalePrice) ; CStr (ProductID) etc.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    Is this data you are appending to an existing table?
    Can you enter the data manually into the field? (i.e. confirm there are no limits in terms of string length, etc)

    What method are you using to import the text file? (Import Wizard, Append Query, XML ImportSpecs, DoCmd.TransferText)

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

Similar Threads

  1. Replies: 4
    Last Post: 12-08-2017, 11:30 AM
  2. Type Conversion Failure
    By jessgold in forum Access
    Replies: 5
    Last Post: 11-12-2012, 10:39 PM
  3. Type Conversion Failure When Importing txt File
    By Eka1618 in forum Import/Export Data
    Replies: 15
    Last Post: 10-29-2012, 06:45 PM
  4. Help need: Type conversion failure
    By tami in forum Access
    Replies: 1
    Last Post: 01-18-2012, 07:18 AM
  5. Type Conversion Failure
    By fpmsi in forum Access
    Replies: 7
    Last Post: 09-22-2011, 11:25 AM

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