Importing a textfile with datefield into table 1

[flaviooooo]

Hi,

I'm currently programming an Access ADP, based on SQL data. Now we would like a way to import a textfile into the tables.

Correct me if I'm wrong, but I think the only proper way to do this is through DTS? In an ADP, you can't save an importspecification, so that would be troublesome.

Anyway, the problem lies in the format of the textfield. All textfields are imported properly, but the problem comes when it reaches a datefield. In the textfile, the dates are defined as such:

20061013 (so yyyymmdd)

The field in the table is of type datetime.

Unfortunately when I try to import, I get an error saying that the fieldtypes are not the same. I need to somehow tell the DTS that the yyyymmdd-field, is actually a datefield an transform it properly. But I don't know how I would do this...

Any help?

Kind regards
Fabian

 

[mthakershi]

You can use 'DateTime String' Transformation on that particular field. You will be able to define the source and destination date formats. You can do this on 'Transformation' tab of 'Transform Data Task Properties' screen.

Malay

 

[AlexCuse]

you will need to either do some work on your data using SQL tasks in a staging table or write your transformations as an activeX task to change your text date into a string (resembling the date format that your SQL Server is using - mine is '2006-10-13 00:00:00.0000).

Hope this gets you on the right track,

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[AlexCuse]

Malay - I didn't know about that option. Thanks.

Fabian - you also should consider using a staging table and doing some validation, as a bad date in your text file could bring everything to a screeching halt.

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[flaviooooo]

Good stuff Malay, have a star!