Change Number to Date (1 Viewer)

tucker61 · Nov 10, 2017

I have 2 query's that import information from a linked table (txt) into a table in my database.

Both queries have a date field but they don't pull the date formatted as a date field, SO I think that during the import query I need to format the text as a date. in query 1 the text in its raw format is 20171109, and in query 2 it is 9112017.

I cannot amend the text files as they are systemic driven, and I would have to jump through hoops to get any changes.

Would I be better putting the data into 2 separate tables ? as every other field is the same apart from this date field ?

Any ideas ?

Thanks

plog · Nov 10, 2017

Don't directly import your files into the ultimate table the data will go into. Instead, import the data from the files into a staging table (tmp_Stage). Then run a query(queries) to move the data into the final table converting the fields to the correct data type as you do.

Actually, I always have a validation set of queries to let me know if any data is bad that will throw off the move query (a date that's not really a date, a number that's not really a number, etc.).

tucker61 · Nov 10, 2017

What's the easiest way to convert the numbers into a date ?

plog · Nov 10, 2017

With the format it is coming in at, you would need to extract out the parts you need (Mid()) and then you would put them together and create a date (CDate()).

Check out this page with the functions you would need:

https://www.techonthenet.com/access/functions/

tucker61 · Nov 10, 2017

Thanks, I have used left, right and mid before. But never heard of Cdate..

I will have a read of the link you sent..

Pat Hartman · Nov 10, 2017

If you are using TransferText to import the file, you can use an import spec to control how the date fields are interpreted.

Start by importing the file again. in the dialog, you need to press the advanced button. That will allow you to specify a date fields order - mdy, ymd, dmy. And it will allow you to specify whether or not there are separators. The problem is that ALL date fields in a particular import MUST be the same format. Now when you go back to the field by field definition, you can specify that a column is a date and it will be interpreted correctly.

Save the import and give it a meaningful name. Then in your code, you can reference the spec in your TransferText method so the import can be automated.

The reason that you are seeing 9112017 rather than 09112017 is because Access/Excel is interpreting this field as a number rather than as a string and so is dropping the leading zero. Open the file using Notepad to be sure the leading zero is there. If the field is actually variable in length, you have a much bigger problem to overcome. For example is 1112017 January 11th or November 1st?

tucker61 · Nov 10, 2017

Interesting. I might have a look at this later. I did create a import specification, but every time I set the format as date and time, the data did not import, but gave me a error similar to !num. I did not know we could change the import format to yymmmdd etc.. Thanks..

tucker61 · Nov 10, 2017

Re imported my data, setting the fields to Date & Time, and changing the format. This worked. Thanks...

Pat Hartman · Nov 12, 2017

You're welcome.

tucker61 · Nov 13, 2017

Pat Hartman said:
You're welcome.

Re did my import specification today and the new import works fine.. my problem now is the old data..how to convert this to the correct date format.

I tried to export into .txt file but this did not look correct.

I need to export the old data, then create a new import specification for this data. I tried this in excel and access but doing it in them formats did not let me create the import specification.

What is the best way to do this. In total I have about 300k records that need to be exported .

Cheers.

Sent from my SM-T715 using Tapatalk

Pat Hartman · Nov 13, 2017

You don't need to export the data. You can create a new column and populate it. When the data is fixed up rename the original column and give the fixed up collum the name of the original one. After a while, just delete the original column.

But, what is wrong with the old data? Did you import it as a text string? A date field cannot hold invalid data.

If there is no way to distinguish the old, bad data from the new, good data, you might want to back out the new data, fix up the old data, and then re import the new data.

As always - back up, back up, back up prior to doing anything like this.

Change Number to Date (1 Viewer)

tucker61

Registered User.

plog

Banishment Pending

tucker61

Registered User.

plog

Banishment Pending

tucker61

Registered User.

Pat Hartman

Super Moderator

tucker61

Registered User.

tucker61

Registered User.

Pat Hartman

Super Moderator

tucker61

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread