Renaming a text file and extension from content

[HDIr0n]

Hello,

I am fairly new to programming and I am stuck on a project.

What I need VBA to do is to open a text file in one location (I will need to do several instances of this)
search for this variable, full_filename: e.g. unit_255-2013-035-1-0
rename the file with the full_filename variable
then I need to name the extension by this variable, filename_extension: e.g. dbd
and finally save them into a different location

I am currently using Excel 2010 / VBA 7.0

Any help would be greatly appreciated.

Thanks in advance,
-Glenn

[patel]

can you attach a sample txt file ?

[HDIr0n]

can you attach a sample txt file ?

Here is a sample, this is clipped as the whole text is very long. All the pertinent info for all the different files I have to work on is up at the top.

dbd_label: DBD(dinkum_binary_data)file
encoding_ver: 5
num_ascii_tags: 14
all_sensors: T
the8x3_filename: 00200000
full_filename: unit_255-2013-035-1-0
filename_extension: dbd
mission_name: STATUS.MI
fileopen_time: Tue_Feb__5_13:59:08_2013
total_num_sensors: 1905
sensors_per_cycle: 1905
state_bytes_per_cycle: 477
sensor_list_crc: 2BCAFF76
sensor_list_factored: 0

So as far as the extension is concerned the txt files already have the right extensions, I just have to keep the extensions the same and rename the file with the
full_filename variable.

Thanks,
-Glenn

[SamT]

Glenn, as Paul says, "Can you explain what are you trying to do, and not how you think you want to do it?" Paul H. (http://www.vbaexpress.com/forum/showthread.php?47641-INDIRECT%28%29-function-doesn-t-work-quot-Can-t-Find-Object-or-Library-quot&p=297932&viewfull=1#post297932)

I think this is relevant because in your intro (http://www.vbaexpress.com/forum/showthread.php?47689-Hello&p=298023&viewfull=1#post298023) you told what you are trying to do and this post is about how you think you want to do it.

In the simplest code terms, what you are trying to do is put data into a Database and analyze it. Stated this way it is obvious that what you need to do first Code wise is get the data from the text files into your database sheet(s). You don't need to rename the text files for that purpose. You still need Structure wise to first setup the DB sheet(s).

Given that, do you still want to rename them?

The Renaming process will be to loop thru all the *.txt files in the folder(s), search for the Line containing the string "full_filename", Analyze that line for the desired file name, then rename the file and loop to the next file. You will need the files' full folder directory path(s).

Regardless of the name change, the process for inputting the data into the Database(s) is to loop thru the .txt files, Loop thru the Lines. Spilt the Lines at the the string ": " into Field names and values, and put the values into the Field columns.

Database structure-wise, it is best practice to import every value in every data file, even the ones you don't see a need for today.

If you upload a complete Text file, it will take me less than a minute to convert it into a sample Database Sheet for you using a Regular Expression editor. I suggest uploading the largest file, since it is most likely to have every possible Filed name.

Upload files by clicking to the "Go Advanced" button and the "Manage Attachments" button under the Advanced editor.

[HDIr0n]

I am trying to rename these files that I receive from my AUV with the variable full_filename and for the file to keep its extension. I need to do this in order to
process the data in another program that I have for visualizing the data.

Once the renaming is done I have a batch file that will take these files and put them into a different folder for processing.

The largest file is the .dbd file, but I will have to run all 8 types. I attached a zip file with all 8 types.

Sorry if I wasn't clear enough.

-Glenn

[Kenneth Hobs]

Obviously, you would change the value of srcPath to your path. Always test on backups.


Sub RenameMyFiles()
Dim srcPath As String, a() As Variant, v As Variant
Dim fne As String, ffn As String, fn As String
Dim s As String, sa() As String
Dim vv As Variant, aa() As Variant

srcPath = "X:\FileReadWrite\RenameFromTextInFile\"

a() = GetFileList(srcPath)

For Each v In a()
'On Error GoTo NextV
fn = CStr(srcPath & v)
If LCase(fn) = LCase(ThisWorkbook.FullName) Then GoTo NextV
sa() = Split(TXTStr(fn), vbLf)
'ffn = Split(Filter(sa(), "filename_extension:", True, vbTextCompare)(0), vbLf)(0)
'fne = Split(Filter(sa(), "mission_name:", True, vbTextCompare)(0), vbLf)(0)
For Each vv In sa()
'Debug.Print v, vv
If Left(LCase(vv), 14) = "full_filename:" Then
ffn = Trim(Split(vv, ":")(1))
Exit For
End If
Next vv
fne = GetFileExt(fn)
On Error Resume Next
Name fn As srcPath & ffn & "." & fne
NextV:
Next v
End Sub

Function GetFileExt(filespec As String)
Dim fso As Object, s As String
Set fso = CreateObject("Scripting.FileSystemObject")
s = fso.GetExtensionName(filespec)
Set fso = Nothing
GetFileExt = s
End Function

Function TXTStr(filePath As String) As String
Dim str As String, hFile As Integer

If Dir(filePath) = "" Then
TXTStr = "NA"
Exit Function
End If

hFile = FreeFile
Open filePath For Binary Access Read As #hFile
str = Input(LOF(hFile), hFile)
Close hFile

TXTStr = str
End Function


Function GetFileList(filespec As String) As Variant
' Returns an array of filenames that match FileSpec
' If no matching files are found, it returns False

Dim FileArray() As Variant
Dim FileCount As Integer
Dim FileName As String

On Error GoTo NoFilesFound

FileCount = 0
FileName = Dir(filespec)
If FileName = "" Then GoTo NoFilesFound

' Loop until no more matching files are found
Do While FileName <> ""
FileCount = FileCount + 1
ReDim Preserve FileArray(1 To FileCount)
FileArray(FileCount) = FileName
FileName = Dir()
Loop
GetFileList = FileArray
Exit Function

' Error handler
NoFilesFound:
GetFileList = False
End Function

[HDIr0n]

Thank you soo much! It works perfectly.

-Glenn