Solved

How to convert a txt file field that is a date into an Access table as a date type field ?

Posted on 2006-07-06
5
307 Views
Last Modified: 2012-06-21
I created a DTS package to utlimately create an Excel report.
I am starting out with a txt file, that contains a date field.
One of the first steps I perform with the DTS package is a "Transform Data Task" with a SOURCE of the txt file and the DESTINATION is an Access table.
For a date field in the txt file called [Invoice Date], I defined the Access table with a receiving field also called [Invoice Date] with a field type of Datetime.

I was trying to transfer the text field called [Invoice Date] on the txt file with date values to an Access field also called [Invoice Date] as a date field because I need to perform some date comparisons with this field once the txt file is loaded into the Access table.
 
This causes the following error when I run the DTS package:

Transform Data Task: undefined
The number of failing rows exceeds the maximum specified.
Transform Copy 'DTS Transformation_6' conversion error:
Conversion invalid for data types on column pair 1
(source column 'col006' (DBTYPE_STR), destination column 'Invoice Date' (DBTYPE_DBTIMESTAMP))

Is there a way to convert the txt date field to a date type field in the Access table ?

If I don't convert the data type to a date field when I move the txt file to an Access table, then if I move the date field to Excel, the field will be a text field
and won't I have a problem conveting the date to a different date format in Excel. Also won't I have a problem testing date ranges with a date field that is
of type nvarchar in the Access table?      
0
Comment
Question by:zimmer9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 35

Expert Comment

by:Raynard7
ID: 17055125
What I would do is make the column text in access;

Then either create an additional column which is a date - and once the data is imported run a sql statement to update the column and set it to cdate([Invoice Date])

Or you could use a query to covert the column and use this query wherever you refer to the table - and it is just like it was a date field.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 17055235
IN SQL:
ALTER TABLE TABLENAME ALTER COLUMN COLUMNAME DATE
But why would a field that is always a date be a text field in the first places?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17055897
What is the format of the text date field. Remember Access requires date separators in a text field to convert it to a date.  If the text date is 20060706 - it won't work.  You have to import the text field as text, and build a date using something like:

UPDATE myTable SET myDateFld=DateSerial(Left(txtDate,4),Mid(txtDate,5,2),Right(txtDate,2));
0
 

Author Comment

by:zimmer9
ID: 17063043
How would I extend the following ALTER TABLE statement to include 2 fields in terms of the syntax ? For ex: using Col X and Col Y

ALTER TABLE TABLENAME ALTER COLUMN COLUMNAME DATE
0
 
LVL 26

Accepted Solution

by:
jerryb30 earned 500 total points
ID: 17063247
Do not believe you can alter more than one column at a time in Access.  You would have to execute to separate DDL statements in queries/code.

Alter tablename alter column [col x] date
and then
alter tablename alter column [col y] date
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

749 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