Sign in
 
Home20132010Other VersionsLibraryForumsGallery
TechCenter >
> 
> 
Access import text file fails with Type Conversion Failure

Answered Access import text file fails with Type Conversion Failure

  • Wednesday, August 01, 2012 11:23 PM
     
     
    Sign In to Vote
    0

    When importing from a csv file, I have a field that is a raw phone number.  10 digits no spaces or special characters.  No mater what I do this will not import.  If I only have 9 digits, it imports fine.  Add the extra digit, and error returns!  Very frustrated!!!  What is the issue.  Can anyone help with this?

    Rocky

    Rocky D. Massey

     

All Replies

  • Thursday, August 02, 2012 2:29 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi,

    When I tried in my PC, I didn’t meet this issue. And could you please give me what the error message is?

    And what version of Office you have?

    Try to open the Access in safe mode to check the issue.

    http://social.technet.microsoft.com/Forums/en-US/excel/thread/cd010692-aac1-4ced-9bfa-37ff85dc3e2f

    Jaynet Zhang

    TechNet Community Support

     
  • Thursday, August 02, 2012 3:15 PM
     
     
    Sign In to Vote
    0

    Jaynet,

    Office 2010

    Access version 14.0.6112.5000 (32-bit)

    I tried the import in safe mode and received the same error;

    My data set:

    Purchase Order Number,PO Date,Vendor #,Pupose Code,Currency,BillName,BillAddress ,BillCity,BillState,BillZip,Name,ShipAddress ,ShipCity,ShipState,ShipZip,FOB,SCAC Code,Routing,Terms Type code,Terms basic date code,Terms net days,3P,Divison Number,Information contact,Telephone,Information contact,Telephone,Email Address,Reseller/Customer name,ResellerAddress,ResellerCity,ResellerState,ResellerZip,ResellerCountry,Information contact,Telephone,Information contact,Telephone,Email Address,Requested Ship date,Message field,Line item,Quanity,Unit of measure,Ingram part number,Manufacturer part #,Product Description,Media,Unit cost,Extended cost,Message Text
    60N3821D,20120119,U567,Original,U.S. Dollars,Ingram Micro,1600 E. St Andrew Place,Santa Ana,CA,92799,Lela,1759 Wehrle Drive,Willamsville,NY,14221,TP Destination,ABFS,ABF Freight,14,,45,Collect,U567,Angelina Gutierrez,7145661001,Ian Olsen,7145661001,ian.olsen@ingrammicro.com,,,,,,,Ian Olsen,7145661001,Ian Olsen,7145661001,ian.olsen@ingrammicro.com,20100220,Message,LineItem1,1,EA,CH3521,ManfProd1,ProdDesc1,,1.55,1.55,Message1
    60N3821D,20120119,U567,Original,U.S. Dollars,Ingram Micro,1600 E. St Andrew Place,Santa Ana,CA,92799,Lela,1759 Wehrle Drive,Willamsville,NY,14221,TP Destination,ABFS,ABF Freight,14,,45,Collect,U567,Angelina Gutierrez,7145661000,Ian Olsen,7145661000,ian.olsen@ingrammicro.com,,,,,,,Ian Olsen,7145661000,Ian Olsen,7145661000,ian.olsen@ingrammicro.com,20100220,Message,LineItem2,1,EA,CH3522,ManfProd2,ProdDesc2,,2.56,2.56,Message2

    My error results:

    <tfoot></tfoot>
    EDI 850 sample file in drop box_ImportErrors
    Error Field Row
    Type Conversion Failure Telephone 1
    Type Conversion Failure Field27 1
    Type Conversion Failure Field36 1
    Type Conversion Failure Field38 1
    Type Conversion Failure Telephone 2
    Type Conversion Failure Field27 2
    Type Conversion Failure Field36 2
    Type Conversion Failure Field38 2

    Rocky D. Massey

     
  • Thursday, August 02, 2012 3:52 PM
     
     
    Sign In to Vote
    0

    Okay.....  I have figured out what Access is doing, now I just need to know how to stop it!!

    When running the wizard and importing into an existing data base, Access is "automatically" choosing the field types for me, and then converting to my designated file type.  In the fields that I am having issues with, it is choosing "Long Integer," which will not convert correctly.  I need Access to import this field as "text".  How do I get Access to import the fields with my field type designation?

    Also, if I convert this file to Excel and then import it, it works like a charm.  

    What is the issue with import from csv?  HELP!

    Rocky D. Massey


    • Edited by RockyM Thursday, August 02, 2012 4:13 PM
    •  
     
  • Friday, August 03, 2012 1:38 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi,

    We can change the data type when we import the .csv file.

    See the picture below:

    Select the field, then choose the data type above. The default data type is Long Integer, change it to text to check the issue.

    Jaynet Zhang

    TechNet Community Support

     
  • Friday, August 03, 2012 3:29 PM
     
     
    Sign In to Vote
    0

    You only get this screen if you do an import to a new db.  I need to update and append to an existing db.

    

    Once you go past you get the next screen where you do not have these options to change the "type"

    The advanced button also does not give you any usable options.  At this point this looks like a bug in Microsoft's software.

    Anyone out there have a solution or a workaround?  PLEASE!

    Rocky D. Massey

     
  • Monday, August 06, 2012 2:02 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi,

    Since you choose Append a copy of the records to the table, this option means that the file data will be imported to a exists table.  So the data type has defined in the table. Find the table you import into, change the data type then check the issue.

    If the table is not exists, then please try to choose the first option “Import the source data into a new table in the current database”, in this option, there is “Data type” settings in my last reply.

    Jaynet Zhang

    TechNet Community Support

     
  • Tuesday, August 07, 2012 10:59 PM
     
     
    Sign In to Vote
    0

    I am not sure how to explain so that you understand.  I am importing data from a .csv file to an existing database.  I have all the fields in the table set correctly.   When I import this, Excel decides to use "long integer" as the type on several of the fields.  This does not import into the table.  It returns the type file conversion error that I have listed earlier.  I have duplicated this issue on no less than 4 computers all with the same result.  How can we fix this, or who can I contact to find out why it is happening?

    Rocky

    Rocky D. Massey

     
  • Wednesday, August 08, 2012 3:42 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi,

    Based on my research, Access is having trouble putting the data into the fields because the field type does not match.

    For example, if you have a Number or Date field, and the data you are importing contains:
       -
       Unknown
       N/A
    these are not valid numbers or dates, so produce a "type conversion" error.

    So check the data in the csv file which you want to import. Second, you mean before import, the table of phone column has already set as Text data type? And then after import, the data type changed into Long Integer? When I tried this in my Access 2010, this issue cannot be reproduced.

    Then try to import the data in a mew table, use Import the source data into a new table in the current database to do that, if this method doesn’t meet the issue, then copy the data which in your original table and paste into the table you import.

    Jaynet Zhang

    TechNet Community Support

     
  • Wednesday, August 08, 2012 3:48 PM
     
     
    Sign In to Vote
    0

    I am sorry that I am not being clear.  Maybe there is someone else that can help with the issue.  I will do this step by step.  If you follow with me, you will see the same issues on your computer.  I have reproduced this on 6 different computers now.  This is an issue with Access.

    1. import csv file to new Access blank database letting the file create the table.

    Please use this file so we are using the same data. csvfile  (right click - save link as)

    2. Import as text file "Delimited" - click next

    3. Check "First Row Contains Field Names and choose OK to the dialog box - Click next

    4. Choose fields "Telephone, Field27, Field 36, Field38" and set the data type to "Text" - Click next twice

    5. click Finish

    You will see this action works correctly.

    View the table you just imported in design view, and you will see that the data types for the above fields are also correct.

    NOW!!!

    1. Delete the two rows that were imported

    2. Choose "External Data" & "Text file"

    3. Choose the same file BUT choose "Append a copy of the records to the table" - Click OK

    4. Choose "Delimited" - Click next

    5. Check "First Row Contains Field Names and choose OK to the dialog box - Click next

    6. You have no other options here but to choose finish

    7. You will now see the errors from the attempted import.

    This is because the import is "auto" selecting "long integer"

    Long integer will not import anything longer than 9 digits.  The fields need to import as text!

    Please, Please - follow these directions, and you will see that the import from a text file has a bug.  I need it fixed and I need a work around.  I am trying to automate a process and I cannot do it without this working correctly.

    Rocky

    Rocky D. Massey


    • Edited by RockyM Wednesday, August 08, 2012 3:49 PM
    •  
     
  • Thursday, August 09, 2012 5:18 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi,

    Follow the steps you give, I can reproduce the issue, then here’s a method to try to avoid this issue:

    Do the same above the steps “Choose the same file BUT choose "Append a copy of the records to the table" - Click OK” you listed in your last reply.

    Choose "Delimited" - Click next

    Check "First Row Contains Field Names and choose OK to the dialog box

    Click “Advanced…” button in the left bottom corner.

    In the “Code Page:” part, choose “Western European (Windows)” option.

    Then do the steps left as usual.

    Jaynet Zhang

    TechNet Community Support

    • Marked As Answer by RockyM Friday, August 10, 2012 3:53 PM
    •  
     
  • Friday, August 10, 2012 3:54 PM
     
     
    Sign In to Vote
    0

    Yea!!!  That works.  Now I can continue.  Thanks for hanging in there with me!

    Rocky D. Massey