Type Conversion Error Importing text files to Access

Posted on 2010-01-08
Last Modified: 2012-05-08
Hey All,

I have a table defined with 5 different date columns.  I have the date format set to yyyy-mm-dd.  I need to import from a pipe-delimited text file which uses the same format in the text file, however when I try to import, I get Type Conversion errors for all dates in my file.

I suspect this has to do with a difference in how access stores date values vs. displays them.  

Thoughts on overcvoming this?
Here are a few sample lines of data.

A|999999999|1900-01-15||ABCD|BASIC PRESCRIBED FIRE TRAINING|02|20|2008-12-06|2008-12-11|2012-12-31|NA|NA|0.00|05|04|48|0|05|03|03|0.00|0.00|0.00|236.00|236.00|NA
A|999999999|1900-01-15||ABCD|THE NSC 2009 CONGRESS & EXPO|02|20|2007-10-26|2007-10-27|2012-12-31|NA|NA|0.00|05|04|16|0|04|03|03|0.00|0.00|0.00|0.00|0.00|NA
Question by:mattturley
    LVL 119

    Expert Comment

    by:Rey Obrero
    you have to set data type of the date columns to Text
    just convert them to real date after import

    Author Comment

    I need to use the import to validate that the text is actually a real date.  Am I going to have to do the import first then a second process/step to validate the date?
    LVL 119

    Expert Comment

    by:Rey Obrero
    LVL 3

    Expert Comment

    Try what I did in the attached screenshot.  This was done in Access 2007, but I think 2002 and 2003 are similar if you are using those.

    When importing or linking to this text file, click the Advanced button, which allows you to create an import specification.  Change the date order to YMD, and change the date delimiter to a hyphen.  You may need to check the boxes for Four Digit Years and Leading Zeros in Dates.

    LVL 3

    Accepted Solution

    If you are importing this on a regular basis, you may need to go through these steps every time if you do this manually.  If you are using code, click the Save As button and click OK.  You should probably not change the default Specification Name it gives you.  In my example, I was importing pipe.txt, and saved the specification as Pipe Import Specification.

    When you use code to import this data, it would look like this:

    'Delete the table if it exists
    On Error Resume Next
    DoCmd.DeleteObject acTable, "Pipe"
    On Error GoTo 0

    DoCmd.TransferText acImportDelim, "Pipe Import Specification", "Pipe", "C:\Pipe.txt", False

    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    << I get Type Conversion errors for all dates in my file.>>
      Make sure you setup a import spec for the file to force the column data type or force it your self when importing.
      When Access decides on a column type, it only samples a small number of records and not the entire columns worth of data.  Depending on what data is actually in the column, it may guess wrong.
    For example, if you have US and Canadian zip codes in a file, and the US codes all appear first, Access will assign a numeric data type to the column, then choke with the error your getting when it reaches the Canadian zips (which has alpha's in them).
    LVL 119

    Expert Comment

    by:Rey Obrero
    safest way is to accept them all as Text type of data.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Suggested Solutions

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now