Type Conversion Error Importing text files to Access

Posted on 2010-01-08
Medium Priority
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 120

Expert Comment

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

Author Comment

ID: 26211975
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 26211981

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Expert Comment

ID: 26212068
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.


Accepted Solution

ClarkFilter earned 2000 total points
ID: 26212149
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 58
ID: 26212281
<< 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 26212310
safest way is to accept them all as Text type of data.

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

755 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