How to convert a txt file field that is a date into an Access table as a date type field ?
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?
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?
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?
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?
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(
UPDATE myTable SET myDateFld=DateSerial(Left(
ASKER
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
ALTER TABLE TABLENAME ALTER COLUMN COLUMNAME DATE
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.