mattturley
asked on
Type Conversion Error Importing text files to Access
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||AB
A|999999999|1900-01-15||AB
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||AB
A|999999999|1900-01-15||AB
ASKER
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?
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.
pipe.png
pipe-import-results.png
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.
pipe.png
pipe-import-results.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<< 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).
JimD.
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).
JimD.
safest way is to accept them all as Text type of data.
just convert them to real date after import