How to Parse Imported Data String into Multiple Fields? 2

[ETSMAN]

Problem:
- I import a txt file of a directory scan on a server and need to parse the txt string into 4 fields.
- There can be subdirectories in Fields2-4.
- The first part of the string does have a constant
"x:\Training\CourseReview\Pending Approval"

This is what I would like the end result table to be:
Field1 = Original data string imported (no change)
Field2 = CourseName (will vary)
Field3 = Tab x (will be Tab A-H)
Field4 = FileName (will vary)

Example records:
Ex.1:"x:\Training\CourseReview\Pending Approval\TeachMeCourse\Tab A\Course101.pptx"
Ex.2:"x:\Training\CourseReview\Pending Approval\TeachMeCourse2\TeachMeNow\Tab B\Course001.doc"

Desired record output per examples:
Ex.1:
Field1 (Original Data)= "x:\Training\CourseReview\Pending Approval\TeachMeCourse\Tab A\Course101.pptx"
Field2 (CourseName)= "TeachMeCourse"
Field3 (Tab x)= "Tab A"
Field4 (FileName)= "Course101.pptx"

Ex.2:
Field1 (Original Data)= "x:\Training\CourseReview\Pending Approval\TeachMeCourse2\TeachMeNow\Tab B\Course001.doc"
Field2 (CourseName)= "TeachMeCourse2\TeachMeNow"
Field3 (Tab x)= "Tab B"
Field4 (FileName)= "Course001.doc"

Help is appreciated.

 

[ETSMAN]

I get Microsoft Office Access window w/ a single character and an OK button. when button clicked then signle character "." w/ OK button... each time the character alternates a letter then ".".

 

[MajP]

Sorry those were message boxes for debugging.
delete these lines
MsgBox Mid(tempDoc, Len(tempDoc) - 4, 1)
MsgBox Mid(tempDoc, Len(tempDoc) - 3, 1)

The good news the "." means it found the beginning of the file extension.

 

[ETSMAN]

hmmm, all records come back w/ "No File" in the "Document" field.

 

[MajP]

Oops. My fault wrong version. Try this one.

Public Function getDoc(strOriginal As Variant) As String
  Dim strDocs() As String
  Dim tempDoc As String
  If Not IsNull(strOriginal) Then
    strDocs = Split(strOriginal, "\")
    tempDoc = Nz(strDocs(UBound(strDocs)), "")
    If Len(tempDoc) > 4 Then
        If Mid(tempDoc, Len(tempDoc) - 4, 1) = "." Or Mid(tempDoc, Len(tempDoc) - 3, 1) = "." Then
         getDoc = tempDoc
       Else
         getDoc = "No File"
       End If
    Else
      getDoc = "No File"
    End If
  End If
End Function

 

[ETSMAN]

All seems to be working fine now, I'll play around w/ it for a day or so.

THANKS again for all your help.

 

[MajP]

Good luck. If you have questions on how the code works just ask.

 

[ETSMAN]

MajP,
Everythingn is still working great thanks to u.
I don't want to take up too much more of your time, but had another question.

To clean up the manual part of the "Presentation" field would it be more efficient to wright a macro or sql statement and do an update query?

Re-cap of issue:
File names get sequenced by individuals based on how they use them in class instruction but vary from individual to individual.

EX.
Indiv.1 uses file: 1. test prep101.doc
Indiv.2 uses file: 1 test prep101.doc
Indiv.3 uses file: 1test prep101.doc
Indiv.4 uses file: 1.test prep101.doc
Indiv.5 uses file: 1-test prep101.doc
Indiv.6 uses file: 1- test prep101.doc

Indiv.1 uses file: 2. final prep101.doc
Indiv.2 uses file: 2 final prep101.doc
Indiv.3 uses file: 2final prep101.doc
Indiv.4 uses file: 2.final prep101.doc
Indiv.5 uses file: 2-final prep101.doc
Indiv.6 uses file: 2- final prep101.doc


For the most part the only tricky part is when a file has a name such as 3-way.wav or 4-way.wav etc.
I just want to automate removing the sequence portion for the "Presentation" field only, I can then do a manually sort and verify against the "Document" field.

as before THANKS!

 

[MajP]

Use update queries. In SQL use the "Like" operator with wildcards to return similar records.

...WHERE Course Like '*final prep 101*'

http://www.techonthenet.com/access/queries/like.php

 

[ETSMAN]

great thx, appreciate the guidance.

 

[SkipVought]

ETSMAN,

Don't forget to...
[blue]
Thank Tek-Tip Contributor
for this valuable post!
[/blue].
The [purple]little purple Stars[/purple] have several purposes.

First, they [purple]give positive feedback to Tek-Tip Contributors[/purple], that their posts have been valuable.

Second, they [purple]identify threads as containing valuable posts[/purple], so that other members can share in the benefit.

And third, they identify the original poster (that's YOU, BTW), as a grateful member, that not only has received, but is also willing to [purple]give visible tokens of thanks[/purple].



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ETSMAN]

will do, thx again.

 

[ETSMAN]

Query works fine, found the records I need using "Like" command now how do I update the field keeping everything but what I found w/ "Like"?

If I update w/ "" that will update complete field to blank correct? I just want to replace my findings and have the field start with the next character after my findings.

 

[MajP]

Can you give a sample of before and after data? Having a hard time understanding what you want to change the found set to.

 

[ETSMAN]

Ex.
Before After
2 newfilename.pptx -> newfilename.pptx
1. filename.pptx -> filename.pptx
6filename1.pptx -> filename1.ptx
4-filenameBB.pptx -> filenameBB.ptx

Not sure this is the most effiecient statement, but this is how I got all fields starting w/ a number.

SELECT

.fieldname
FROM

WHERE (((

.Fieldname) Like "#*"));

 

[MajP]

To make this reuseable, make a new table.

tblFileNames
fileName

where "fileName" is a field containing all the file names you want to keep. You can then add names to this list and run it whenever you need to.

Assume your main table is Table1 and the field is Contents

UPDATE 
 Table1, 
 tblFileName 
SET Table1.Contents = [tblFileName].[fileName]
WHERE 
  Table1.Contents Like '*' & [tblFileName].[fileName] & '*'

so tblFileNames would have in it
newfilename.pptx
filename.pptx
filename1.pptx
filenameBB.pptx

If that is not doable then you can make a function that strips off any numbers and punctuation prior to the first alphabetic letter.

 

[ETSMAN]

I see where u r going with the new table unfortunetaly I do not control the directory structure and it changes very dynamically. I'll need a function... to strip off the numbers and spaces.
thx

 

[MajP]

If it is only numbers and spaces at the front that is doable. They are not adding things to other locations?

 

[ETSMAN]

correct only #s, spaces, ., and -
typically inconsistent format in usage (1a. xxx, 1a.xxx, etc.)used to sort order in the directory file system.

 

[MajP]

It is pretty hard to provide a rule for making a function. So I would still make a table of bad chars that could be at the beginning of the file, and do it the other way. Identify what to delete not what to keep.

tblBadChars
badChar

Populate with things like
1.
2.
10.
1a.
1b.
1a
....
Then

UPDATE 
 tblBadChars, 
 Table1 
SET 
 Table1.Contents = Trim(Mid([contents],Len([badChars])+1))
WHERE 
 Table1.Contents Like [badChars] & '*'

I think that works. Try it on dummy data.

 

[ETSMAN]

I think this will work playing w/ some test data, there are a few situations that need to be addressed, but I'll play around and see what I can do. If not I'll hit you up one last time in the morn.

thx.