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.

 

[SkipVought]

Well if ETSMAN ignores your great contributions, MajP, I'll give you a [purple]little purple star.[/purple]

Skip,

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

 

[ETSMAN]

Ahhh be nice Skip, I'm new to the whole forum thing, thought I gave credit were credit was due.

Per my posts I hope it's obvious that my appreciation for all of MajP is obvious, but thank you for covering me. I click the little purple star once and thought it would tag the all the posts, I realize now that it's per post.

I do appreciate you pointing that out to me as well, MajP has done me a great service the last few days.

Thanks both of you

 

[ETSMAN]

MajP,
can u walk me thru the code, trying to understand why I get the following results:

my badchar table has (for #s 1-10)
1
1.
1a
1a.
.
.
.
10
10.
10a
10a.

if the record is "1a. filename.txt"
I get "ename.txt" (7characters trimmed)
if the record is "1.a filename.txt"
I get "filename.txt"

?My guess is that the code sees 1a. counts 3 then adds 3 =6 plus 1 for start string (7th character)?

code:
UPDATE tblBadChars, Report SET Report.Presentation = Trim(Mid([Presentation],Len([BadChars])+1))
WHERE Report.Presentation Like [BadChars] & '*';

 

[MajP]

Have to think of another approach.
It matches 1*, 1a*, and 1a.*
So it ends up stripping the value three times. I have to think for a minute on this.

 

[MajP]

Maybe build a function that strips off all leading characters until hitting a letter. That solves all of these cases
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.do

again it fails on
10 BestBeaches.pptx
#1 Restaurants.pptx
~FileName.pptx
etc.
etc.

 

[ETSMAN]

let me play w/ it a little more, still seems like my results are not consistent.

I'll check the code and vers. etc.

 

[ETSMAN]

I scrubbed the tblBadChars and put just #., #letter. and all works fine.

This is a naming std. issue and I'm sure it could be coded to work, but for this effort this will do. The files for order will have to follow a std. of #letter.space.

thx again.

I'm sure got my little purple stars

 

[MajP]

This function will strip characters off until it finds two alphabetic characters

This should handle all cases you presented to include.
10a
10a.

Public Function stripToLetter(fileName As Variant) As String
  Dim i As Integer
  Dim lenString As Integer
  Dim ltr As String
  Dim nextLtr As String
  Dim tempString As String
  
  'Ascii 65-90 = A-Z
  If Not IsNull(fileName) Then
    lenString = Len(fileName)
    tempString = UCase(fileName)
    For i = 1 To Len(fileName)
      ltr = Mid(tempString, i, 1)
      If (i + 1) <= lenString Then
        nextLtr = Mid(tempString, i + 1, 1)
      End If
      If (Asc(ltr) > 64 And Asc(ltr) < 91) And (Asc(nextLtr) > 64 And Asc(nextLtr) < 91) Then
        Exit For
      End If
    Next i
    stripToLetter = Trim(Mid(fileName, i))
 End If
End Function

 

[ETSMAN]

Just need to create a new module for new code correct?

1. new code saved as a module called "trimBadChars"

2. no change to "tblBadChars" fields (should contain any unwanted #letter. combination)

3. no change to "RemoveBadChars-UpdateQuery"

 

[MajP]

You can just add this function to any standard module. It does not need to have its own. You could put all your functions in a single module

To use this function in a query

SELECT 
  StripToLetter([YourfieldName]) AS CleanFile
FROM Table1;

you could even wrap a function with a function going back to the original problem

SELECT 
 DirScan.OriginalData, 
 getCourse([OriginalData]) AS CourseName,   
 getTab([OriginalData]) AS Tab, 
 stripToLetter(getDoc([OriginalData])) AS CleanFile
FROM DirScan;
[code]

 

[ETSMAN]

Thank you, all is working great.

Once again I appreciate all the help and learning opportunity.