How to find a part number in a file and rename the file with the partnumber?

  • Thread starter Thread starter dan dungan
  • Start date Start date
D

dan dungan

Hi externaldata,

I'm rebuilding a process that resided on a win 3.1 machine
that allowed only 8 character file names.

We want to rename the files using the complete part number as
the file name.

I have about 5,000 files with no file extension. 00-003F,
for example.

I need to find the part number in each file
and rename the file with the part number.

Here are some sample part numbers:
(00-N40L*0800)
(00-M56L*2000)
(02-2174114-0804A)
(T483-15I)
(12-0140**12-061020)
:1001(00-4040L*1001)

We only want the number enclosed by parenthesis.

I think I don't need to read through the whole file.

I want to move to the next file after I've extracted the part number.

I just found out that the part number is not always
on the second line.

Albert D. Kallal supplied the code below and works if the part
number is on the second line of the file and removes the
parenthesis when I rename the file,but I'm confused
about how to find the part number if it isn't on the second line.

Are there any suggestions how to find the part number
and extract it to a variable so I can rename the files?


Sub test9999()

Dim colFiles As New Collection 'To hold file names

Dim strFile As Variant 'To capture the file
name

Dim intFile As Integer 'To count the files to make
sure we only process
file once

Dim strPath As String 'To capture the path
Dim strNewFilename As String 'To capture the new file name
Dim strBuf As String 'To capture the Part Number
'from the second
line of the file
intFile = FreeFile
Call FillDir("C:\Documents and Settings\Analyst\_
My Documents\Work\CNC\Programs\CNC-FEMCO\Test\_
", "", colFiles)

For Each strFile In colFiles
strPath = Left(strFile, InStrRev(strFile, "\"))
Open strFile For Input As #1
Line Input #intFile, strBuf
Line Input #intFile, strBuf
Close intFile
strNewFilename = strPath & Mid(Replace(strBuf, "*", "-")_
, 2, Len(strBuf) - 2)

Debug.Print "old = " & strFile & " new = " & strNewFilename
Name strFile As strNewFilename
Next

MsgBox "done"

End Sub


Thanks,

Dan
 
Hi Again,

I'm using two subs to accomplish this task. (Below)

I revised sub test9999 to add a while wend:

For Each strFile In colFiles
strPath = Left(strFile, InStrRev(strFile, "\"))
'Open strFile For Input As #1


Line Input #intFile, strBuf
While InStr(1, strBuf, "(", vbTextCompare)
Line Input #intFile, strBuf
Close intFile
strNewFilename = strPath & Mid(Replace(strBuf, "*", "-"), 2,
Len(strBuf) - 2)

Debug.Print "old = " & strFile & " new = " & strNewFilename
Name strFile As strNewFilename
Wend
Next



Sub test9999()

Dim colFiles As New Collection 'To hold file names
Dim strFile As Variant 'To capture the file hame
Dim intFile As Integer 'To count the files to make
sure we only process file once
Dim strPath As String 'To capture the path
Dim strNewFilename As String 'To capture the new file
name
Dim strBuf As String 'To capture the Part Number
from the second line of the file
intFile = FreeFile
Call FillDir("C:\Documents and Settings\Analyst\My Documents\Work
\CNC\Programs\CNC-FEMCO\Test\", "", colFiles)

For Each strFile In colFiles
strPath = Left(strFile, InStrRev(strFile, "\"))
'Open strFile For Input As #1


Line Input #intFile, strBuf
While InStr(1, strBuf, "(", vbTextCompare)
Line Input #intFile, strBuf
Close intFile
strNewFilename = strPath & Mid(Replace(strBuf, "*", "-"), 2,
Len(strBuf) - 2)

Debug.Print "old = " & strFile & " new = " & strNewFilename
Name strFile As strNewFilename
Wend
Next

MsgBox "done"

End Sub

'And, the routine filldir *will* traverse sub directories

Sub FillDir(startDir As String, strFil As String, dlist As Collection)

' build up a list of files, and then
' add add to this list, any additinal
' folders

Dim strTemp As String '
Dim colfolders As New Collection
Dim vFolderName As Variant

strTemp = Dir(startDir & strFil)

Do While strTemp <> ""
dlist.Add startDir & strTemp
strTemp = Dir
Loop

' now build a list of additional folders
strTemp = Dir(startDir & "*.*", vbDirectory)

Do While strTemp <> ""
If (GetAttr(startDir & strTemp) And vbDirectory) = vbDirectory
Then
If (strTemp <> ".") And (strTemp <> "..") Then
colfolders.Add strTemp
End If
End If
strTemp = Dir
Loop

' now process each folder (recursion)
For Each vFolderName In colfolders
Call FillDir(startDir & vFolderName & "\", strFil, dlist)
Next vFolderName

End Sub
 
It seems to me that Windows would have no idea what kind of application to
use to open a file that had no extension. Are you saying there IS no
extension, or that no extension is visible?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Hi Jeff,

Yeah, there is no extension. These files are created on a CNC milling
machine and the operators have not saved the file with an extension.

The open file as command does open the file in code.

Thanks,

Dan
 
Hi Jeff,

My big problem right now is that I don't know how to find the line
in the file that holds the part number. I've been attempting to loop
through the file looking for a parenthasis, but I haven't figured out
how yet.

For Each strFile In colFiles
strPath = Left(strFile, InStrRev(strFile, "\"))
'Close intFile
'Close strFile



Open strFile For Input As #1
Line Input #intFile, strBuf

This is where I'm trying to find the file name
but the code returns error 5 invalid procedure call or argument

Do While Not InStr(1, strBuf, "(", vbTextCompare) = -1
Line Input #intFile, strBuf
Loop

' Line Input #intFile, strBuf
Close intFile

strNewFilename = strPath & Mid(Replace(strBuf, "*", "-"), 2,
Len(strBuf) - 2)

Debug.Print "old = " & strFile & " new = " & strNewFilename
 
Hi Jeff,

Here's the rest of the message:

The following line is highlighted

strNewFilename = strPath & Mid(Replace(strBuf, "*", "-"), 2,
Len(strBuf) - 2)

Dan
 
I've been attempting to loop
through the file looking for a parenthasis, but I haven't figured out
how yet.
Click to expand...

Right, but does "other" data have "(" or ")" in it also? How can you tell
one line of junk data from that of a legetiate line of where the file name
resides?

eg:


LINE1: HELLO
LINE2: (#$#@$SDFJ)))(()))
LINE3: (123)
LINE4: (T483-15I)

So, you mean the frst line with "(", that would be line2 is our file name?

Or, are you ABSOLIUTE sure that a line that starts with a "(" is ALWAYS a
part number???

To test for a "(", simply go:

if left(strbuf,1) = "(" then
' this is a file name.


The "basic" shell of a loop to read ALL of a text file is:

intF = FreeFile()
Open strFile For Input As #intF

Do While EOF(intF) = False
Line Input #intF, strLineBuf
if left(strLinebuf,1) = "(" then
' got one...
' code goes here to extract file name
' then exit while, or exit sub
end if

Loop
Close intF

The difficulty is occurring here because you have this file name inside of a
text file, and it starts with a parenthesis, but you have not explained how
the other data looks? If none of the other data starts with a parenthesis on
the first character, then the simple above loop pretty much gives the idea
how you approach this. Thus, you can easy scan the whole file.

On the other hand, if your data has other lines that start with a
parenthesis, then this will start to become a difficult problem very
quickly because we don't have a clear easy way to distinguish between a
simple line that starts with "(", and a line with an actual part number.
 
Hi Albert,

Thanks for your feedback.

This process has become rather sticky.

I'm going to lunch so I'll respond back in an hour or so.

Thanks,

Dan
 
Hi Albert,

Here is data from sample files. I copied and pasted the first 10 rows.

I'm trying to rename the files with the part number which resides in
the first instance of parenthesis in the file.

Also, since windows doesn't allow "*" in the file name, I'm changing
the "*" to a "-".

Thanks,

Dan
__________________________________________
File Name: 99-099F
__________________________________________
%
(99-91577-04L)
(PROGRAM FOR STEEL)
(**USE 7/16"OR 1/2"**303**)
(NOTE-KNURL IS RUN AT)
(REAR OF PART FOR STABILITY)
(**************************)
(FACE & TURN O.D. EXCEPT)
(.030 RAD.O.D. REAR)
__________________________________________
File Name: T483-001
__________________________________________
&HE:%

:2400(T483-24I)

(PROGRAM FOR TEFLON)

(**USE 1-1/2-TEFLON 1500)

(*************************)
__________________________________________
File Name: 12-088F
__________________________________________
&HE:%


:0808(12-0141**18-080870)


(FACE & TURN O.D. FRONT)


__________________________________________
File Name: 02-024F(1)
__________________________________________
&HE:%

:1001(00-4040L*1001)

(PROGRAM FOR STEEL)

(USE 13/16"-303 SS)

(*****************)
__________________________________________
File Name: 02-024F(1)
_________________________________________
:O2205
(02-90133525-2205-**2ND OP**)
(CHUCK ON O.D.)
(CHAMFER THD & TURN FOR KNURL-ISCAR .125)
N1G99G97S2000T1212M03
G00X.882Z.02/M08
G01X1.0Z-.039F.003
G00X1.562Z-.28
G01X1.68Z-.339F.003
Z-.586F.005
__________________________________________
 
Hi Albert,

My responses are in line:
Right, but does "other" data have "(" or ")" in it also? How can you tell
one line of junk data from that of a legetiate line of where the file name
resides?
Click to expand...

I can tell it's a part number because it is the first instance of "("
or ")".
So, you mean the frst line with "(", that would be line2 is our file name?
Click to expand...

Some times line 1, 2, 3, or 4.
Or, are you ABSOLUTE sure that a line that starts with a "(" is ALWAYS a
part number???
Click to expand...

The part number line doesn't always start with a "(".

Sometimes there is a 4 digit number and a ":", like
"0800:("

To test for a "(", simply go:

if left(strbuf,1) = "(" then
' this is a file name.
Click to expand...

Since it's not always the first character in the line, I tried :

If InStr(1, strBuf, "(", vbTextCompare) = -1 Then

It didn't seem to work, and in the debugger, I couldn't determine the
value the formula was returning, so I don't know how to troubleshoot
that formula.
The "basic" shell of a loop to read ALL of a text file is:

intF = FreeFile()
Open strFile For Input As #intF

Do While EOF(intF) = False
Line Input #intF, strLineBuf
if left(strLinebuf,1) = "(" then
' got one...
' code goes here to extract file name
' then exit while, or exit sub
end if

Loop
Close intF
Click to expand...

Do I need to read the entire file?

Thanks,

Dan
 
I can tell it's a part number because it is the first instance of "("
or ")".
Click to expand...

Excellent, so while there can be "many" occuranges of a "(" in the data, we
can assume the *first* one is what we want. As I said, in these problems,
those small details make all the difference in the world.
The part number line doesn't always start with a "(".
Click to expand...

excellnet, again we needed to know this...
If InStr(1, strBuf, "(", vbTextCompare) = -1 Then
Click to expand...

wrong syntax, use:

if instr(strbuf,"(") > 0 then
* got one....

In fact, since we "need" the value of instr to figure out where to start
pull text, lets go:

intCfind = InStr(strBuf, "(")

If intCfind > 0 Then
strPartNum = Mid(strBuf, intCfind + 1)
strPartNum = Left(strPartNum, Len(strPartNum) - 1)
Debug.Print strPartNum

The above assume that the ")" is ALWAYS the last charcter.

In your exmaples that should give us:

99-91577-04L
T483-24I
12-0141**18-080870
00-4040L*1001
02-90133525-2205-**2ND OP**

If the ")" is NOT going to be the last charcter, then we need to do another
instr after you set setPartNum.
 
Hi Albert,

Here's what I ended up with. There are just a few anomalies which
we can manage manually.

Thanks for all your help.

Dan

Sub test9997() 'First Part number in parenethasis
On Error Resume Next
Dim colFiles As New Collection 'To hold file names
Dim strFile As Variant 'To capture the file hame
Dim intFile As Integer 'To count the files to make
sure
'we only process
file once
Dim strPath As String 'To capture the path
Dim strNewFilename As String 'To capture the new file name
Dim strBuf As String 'To capture the Part Number
from
the second line
of the file
Dim intCfind As Integer 'To find parenthasis
surrounding
part number.
Dim strPartNum As String

intFile = FreeFile
Call FillDir("C:\Documents and Settings\Analyst\_
My Documents\Work\CNC\Programs\CNC-FEMCO\Test\", "",
_colFiles)

For Each strFile In colFiles
strPath = Left(strFile, InStrRev(strFile, "\"))
Open strFile For Input As intFile
Do While EOF(intFile) = False
Line Input #intFile, strBuf
intCfind = InStr(strBuf, "(")

If intCfind > 0 Then
strPartNum = Mid(Trim(strBuf), intCfind)
strPartNum = Left(Trim(strPartNum), _
InStr(2, strPartNum, ")", vbTextCompare) - 1)
Debug.Print strPartNum
Exit Do
End If
Loop

Close intFile

strNewFilename =_
strPath & Mid(Replace(strPartNum, "*", "-"), 2, Len(strBuf))
Debug.Print "old = " & strFile & " new = " & strNewFilename
Name strFile As strNewFilename
Next

MsgBox "done"

End Sub
 
Back
Top