Update Query/New Table Query from External Data Source

[ETSMAN]

Problem:
Need an update query or new table query to automate external data source import for reporting purposes.

Example of a record imported from an external text file.
x:\Training\CourseReview\Pending Approval\TeachMeCourse\Tab A\Course101.pptx

Desired Output:
Update query or New Table query to create the following columns & data from the original text import:

Columns:
1. "OriginalData" (source for new columns)
Ex.Record: x:\Training\CourseReview\Pending Approval\TeachMeCourse\Tab A\Course101.pptx
2. "Name" Data result (Variable)-> "TeachMeCourse"
3. "Tab" Data result (could be Tab A-H) -> "Tab A"
4. "File" Data result (Variable)-> "Course101.pptx"

Note: This portion of the txt string will remain constant for all records: "x:\Training\CourseReview\Pending Approval\"

Appreciate the help.
ETSMAN

 

[SkipVought]

Hi,

What version of Excel?

Skip,

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

 

[ETSMAN]

MS Access is the application,
sorry thought I put that in the post.
thx

 

[SkipVought]

Check out the File > IMPORT feature.

You can only have one set of data on an import. So you either get the entire path in one column or parsed data for each folder, essentially.

I'd import into ONE column and then parse in a query.

Skip,

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

 

[ETSMAN]

Thx Skip,
I've got an import routine that automates getting the raw data into a table w/ a column heading of:"OriginalData"

Ex.of a record:
x:\Training\CourseReview\Pending Approval\TeachMeCourse\Tab A\Course101.pptx

This is where my original request for help comes in...

I want to extract parts of the original record text string and create new columns w/ the information from the original record. At the end for the Ex. record above I would have in my table 4 columns: the "OriginalData", "Name" "Tab", and "File" and the related information from the string as outlined in the original post.

Seems like I should be able to create an update query or new table query w/ sometype of criterim to accomplish this.

thx

 

[SkipVought]

First use Data > text to columns - FIXED width to parse the static part from the remainder...
[tt]
x:\Training\CourseReview\Pending Approval\ TeachMeCourse\Tab A\Course101.pptx
[/tt]
then use these 3 formulas to parse the 3 values you want, assuming that the first formula is in row 2 column C...
[tt]
c2: =MID($B2,1,FIND("\",$B2)-1)
d2: =MID($B2,LEN($C2)+2,FIND("\",$B2,LEN($C2)+2)-LEN($C2)-2)
e3: =MID($B2,LEN($C2)+LEN($D2)+3,LEN($B2)-(LEN($C2)+LEN($D2)+3)-4)
[/tt]





Skip,

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

 

[ETSMAN]

Skip,
Thanks, I think this would be a great solution for Excel and constant character fields, but I want to do everything directly in Microsoft Access.

Also, I have no control over the length (# of characters, it will be different depending on the class, Tab, and file.
Couple of examples w/ expected results:

Example1 Data
x:\Training\CourseReview\Pending Approval\mmmm HealthServices\Series 11 HealthServices Course(3 days)\Tab A\BasicHealth Course.docx

Column 1(Access DB)(Original Record)
"x:\Training\CourseReview\Pending Approval\mmmm HealthServices\Series 11 HealthServices Course(3 days)\Tab A\BasicHealth Course.docx"

Column 2(Access DB)(parse happens after the constant txt and beforer the "\Tab..." txt)
"mmmm HealthServices\Series 11 HealthServices Course(3 days)"

Column 3(Access DB)(parse happens before "\"Tab... and after "Tab x")
Tab A

Column 4(Access DB)(parse happens after "Tab x\"
BasicHealth Course.docx
---------------------------
Example2 Data
x:\Training\CourseReview\Pending Approval\Intro to Computer Networking\NETWORKING\Tab C\Applied Computer & Networks.pptx

Column 1(Access DB)(Original Record)
x:\Training\CourseReview\Pending Approval\Intro to Computer Networking\Tab C\Applied Computer & Networks.pptx

Column 2(Access DB)
Intro to Computer Networking

Column 3(Access DB
Tab C

Column 4(Access DB)
Applied Computer & Networks.pptx

thx

 

[SkipVought]

Access uses the same functions. You need only change the string references from a cell reference to a column reference.

SAME PROCESS. 1) Import 2) Query.

Simple!

Skip,

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

 

[ETSMAN]

In excel I get:
c2: x:
d2: Training
e2: CourseReview\Pending Approval\mmmm HealthServices\Series 11 HealthServices Course(3 days)\Tab A\BasicHealth Course.docx

Keep in mind that both c2,d2,and e2 can have multiple subdirectories. In this case
c2 should be "mmmm HealthServices\Series 11 HealthServices Course(3 days)"
d2 should be "Tab A"
e2 should be "BasicHealth Course.docx"

thx.

 

[SkipVought]

Columns:
1. "OriginalData" (source for new columns)
Ex.Record: x:\Training\CourseReview\Pending Approval\TeachMeCourse\Tab A\Course101.pptx
2. "Name" Data result (Variable)-> "TeachMeCourse"
3. "Tab" Data result (could be Tab A-H) -> "Tab A"
4. "File" Data result (Variable)-> "Course101.pptx"

Keep in mind that both c2,d2,and e2 can have multiple subdirectories.

Then you original post cannot be true!

Skip,

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

 

[ETSMAN]

u r correct;
correction 3. Tab will always be A-H

so how does the current code acct for multiple subdirectories for c2 and e2?

thx

 

[SkipVought]

It does not. You have an indeterminate number.

A better table structure is to represent a parent-child relationship with folders. Then you can have any number of sub-folders. This is classic design technique.

Skip,

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

 

[ETSMAN]

I hear u... I'm not a developer just a mgr trying to get it done.

overall it's a simple task for someone like yourself w/ the experience.

How would you sugggest I structure the table to get where I need to be?

thx for all your help hanging in there w/ the unexperienced.

 

[ETSMAN]

The data source is basically .bat file that does a dir scan off a server and creates a txt file.

The txt file is the source for the DB which is the long txt string.

 

[SkipVought]

You really ought to be asking these questions in forum700. You will get much better specific advice than I am able to give.

Skip,

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

 

[ETSMAN]

ok, thx appreciate the guidance. For what its worth one last dump of info. that may or may not help for a quick resolve.

Is there a way to search & replace in a query?

My manual way is to populate each column w/ the data string then perform a series of search & replace (not very effecient).

Data string:
x:\Training\CourseReview\Pending Approval\TeachMeCourse\Tab A\Course101.pptx

Column1 (Name)
Search: x:\Training\CourseReview\Pending Approval\
Replace: null
Result: TeachMeCourse\Tab A\Course101.pptx
Search: \Tab*
Replace: null
Result: TeachMeCourse

Column2 (Tab A-H)
Search: *\Tab x\*
Replace: Tab x
Result: Tab x

Column3 (File)
Search: *\Tab x\
Replace: null
Result: Course101.pptx

Not pretty, but gets the job done.