Import Text by location

[amerifax]

I have a text file with each line a new record.

Each record has 3 fields on each line. They are always in the same location.
Example:
022313 534672 13251- More notes.
121913 342231 40251

First value is a date field: "OrgDate"
022313 It should go into a date field called OrgDate as 02/23/2013

Second Field: Seal
534672 This is a text field called "Seal".

Third Field: "SC"
13251 This is a text field called "SC"

Fourth Field: "PerNote"
- More note. It may or may not start with "-". Regardless it is never more than 20 characters of text. It only contains text about 12% of the time, if that.

Note: the text values always start as the following and always the same width.
OrgDate: start 1 & 6 wide
Seal: Start 8 & 6 wide
SC: Start 15 & 5 wide
PerNote: 20 & 20 wide
Bob

 

[spikepl]

and your question is ? (you have 283 posts and still haven't worked out how all this ticks. Read my signature)

 

[amerifax]

Question is splitting up a text field and importing it into Access 2013 as a date and text field.

Bob

 

[amerifax]

Based on your response. I have decided to try to undertake the task myself using Excel. I can do it in the Excel. It's just that on the hundreds of times that I run this itwould make a lot more sense to me just to have something that can do it quick and easy in Access since that's where it will end up.

Bob

 

[amerifax]

I was able to convert most data in Excel using the following:

022313 534672 13251- More notes (This field equals the file name, good to append the file to record.

022313 =MID(A2,8,6) need to find a way to convert the date to 02/223/2013
534672 =MID(A2,8,6)
13251 = MID(A2,15,5)
- More notes. = RIGHT(A2,45) A bit of a sloppy way for me to do it, but at least it works when I do have notes

Maybe this is more clean on what I'm trying to do. My final step would be to import this data into Access 2013.

Bob

 

[amerifax]

I did an import of text to Access 2013. The date came out much unexpected.
010313 came out as 03/26/1928. I only list this one but the all came out wrong. I had set the data type to Date/Text. I did not change it to anything special. I just took the default.

I'm sure it's probably something simple that I'm not aware of, at least of hope so. Any help would be appreciated. Thank you very much.

Bob

 

[jdraw]

Have you looked at any youtube videos re:Importing text into msAccess?
Here's one https://www.youtube.com/watch?v=ddbf4lh7fZQ

I don't have access2013, but there is usually an option for 2 or 4 digit years in the dialog.

Good luck.

 

[amerifax]

Very good and informative YouTube. It helped me better understand the process.

Know I need to know how to create a link to files in directory. Every file in the directory has a matching record field. Do you know of a YouTube. My search brought up nothing.
Bob

 

[andrrivalle]

Very good and informative comparatively.It's good to be here.