Code to Rename Files

Question

Hi there -

I am developing a package in SSIS (Sql Server Integration Services) part of which uses something called a Script Task (a component in which you can program via VB).  I have posted this question in the SSIS forum as well, but it is really more of a VB question. 

In any event, for this component I need some code that basically loops through files in a folder and changes all text right of the first decimal point (in the file name) to ".txt". 

I am currently doing this via VBA in Access but am moving the process to SQL Server.

Thank you!

---

Bonediggler

Answer 1

When you say "changes all text right of the first decimal point (in the file name) to ".txt"" do you mean to simply change the file extension from whatever it currently is to "txt"?

If so, that could be a little routine like:

Dim files() As String = System.IO.Directory.GetFiles("c:\folder name\")
For Each file As String In files
    Dim newFile As String = System.IO.Path.ChangeExtension(file, "txt")
    System.IO.File.Move(file, newFile)
Next

Replace "c:\folder name\" with the actual path to your files.

---

Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

Answer 2

Hi Reed-

Changing all files to .txt is the ultimate aim, however the files are in one of the two following formats:

1) File_Name.txt.123456 (where 123456 can be any sequence of 5 or 6 numbers) or

2) File_Name.DAT.123456 (where 123456 can be any sequence of 5 or 6 numbers)

So I think if I had a routine that just replaced all syntax to the right of decimal point # 1 with ".txt" I would be all set...unless there is a better way to do it of course...!

---

Bonediggler

Answer 3

    Sub change_allfolderfiles_extentions(ByVal directorypath As String, ByVal extention As String)
        'TODO: get an array of files in the directory

        Dim direc As New System.IO.DirectoryInfo(directorypath)
        Dim files() As System.IO.FileInfo = direc.GetFiles

        For I As Integer = 0 To UBound(files)
            'TODO: get the full name length
            Dim the_length As Integer = files(I).FullName.Length
            'TODO: get the index of the dot in the file name
            Dim the_dot_index As Integer = InStrRev(files(I).FullName, ".")
            'TODO: remove the last extention from the files
            Dim newfilename As String = files(I).FullName.Remove(the_dot_index - 1, the_length - the_dot_index + 1)
            'TODO: add the new extention to the files by moveto() method
            files(I).MoveTo(newfilename & extention)

            'NOTE: if any file has no "." in it's full path an Exception will be thrown
        Next
    End Sub

EDIT: NOTE: the extintion parameter should be with the "." like this :- ".txt" not like "txt"

Answer 4

Hi Reed-

Changing all files to .txt is the ultimate aim, however the files are in one of the two following formats:

1) File_Name.txt.123456 (where 123456 can be any sequence of 5 or 6 numbers) or

2) File_Name.DAT.123456 (where 123456 can be any sequence of 5 or 6 numbers)

So I think if I had a routine that just replaced all syntax to the right of decimal point # 1 with ".txt" I would be all set...unless there is a better way to do it of course...!

---

Bonediggler

Are all Files have the "." dot twice??

or some have one "." and others have 2 "."s

Answer 5

They all have two dots per file name.

---

Bonediggler

Answer 6

They all have two dots per file name.

---

Bonediggler

    Sub change_allfolderfiles_extentions(ByVal directorypath As String, ByVal extention As String)
        'TODO: get an array of files in the directory

        Dim direc As New System.IO.DirectoryInfo(directorypath)
        Dim files() As System.IO.FileInfo = direc.GetFiles

        For I As Integer = 0 To UBound(files)
            'TODO: get the full name length
            Dim the_length As Integer = files(I).FullName.Length
            'TODO: get the index of the dot in the file name
            Dim the_dot_index As Integer = InStrRev(files(I).FullName, ".")
            'TODO: remove the last extention from the files
            Dim newfilename As String = files(I).FullName.Remove(the_dot_index - 1, the_length - the_dot_index + 1)

            'TODO: remove the second Extention
            Dim second_length As Integer = newfilename.Length
            Dim the_second_dot_index As Integer = InStrRev(newfilename, ".")
            Dim the_second_new_filename As String = newfilename.Remove(the_second_dot_index - 1, second_length - the_second_dot_index + 1)
            'TODO: add the new extention to the files by moveto() method
            files(I).MoveTo(the_second_new_filename & extention)

            'NOTE: if any file has no "." in it's full path an Exception will be thrown
        Next
    End Sub

Answer 7

Ah, ok, so:

Dim files() As String = System.IO.Directory.GetFiles("c:\folder name\")
For Each file As String In files
    Dim index As Integer = file.IndexOf("."c)
    If index > -1 Then
        Dim newFile As String = System.IO.Path.ChangeExtension(file.Substring(0, index), "txt")
        System.IO.File.Move(file, newFile)
    End If
Next

-EDIT-

I guess at this point there is no advantage to ChangeExtension and it may be slower than just a straight concat, so perhaps this instead:

Dim files() As String = System.IO.Directory.GetFiles("c:\folder name\")
For Each file As String In files
    Dim index As Integer = file.IndexOf("."c)
    If index > -1 Then
        Dim newFile As String = String.Concat(file.Substring(0, index), ".txt")
        System.IO.File.Move(file, newFile)
    End If
Next

---

Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

Answer 8

Hi Trustmeee,

I think your first approach just needed to use InStr to get the first index instead of InStrRev to get the last index... it shouldn't be necessary to perform two seperate operations, unless I'm missing something.  Other than that it appears to be a good demonstration of another way of performing the routine.

---

Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

Answer 9

Hi Trustmeee,

I think your first approach just needed to use InStr to get the first index instead of InStrRev to get the last index.

---

Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

Hello Reed Kimble,

yes Instr can make it but i think InStrRev will be better because it gets the index of the last "." in the full name, so if I used InStr and the file name has another "." the index will be the index of the first "." not the last "." that is right behind the extension.

I want to ask you something, it's far away from our discussion topic

how can I add a SIGN to my posts like yours "When you do things right, people won't be sure you've done anything at all" :D

I Like it :)

God bless you

EDIT:- ahaaa, I think you mean the second approach that removes the 2 extentions in 2 seperate operations,

yes using InStr will make the code faster and we can handle the possiblity of having more "."s by using If statement.

'if the full name has 2 "."s use Instr

'if it's more than 2 "."s use the InStrRev

if i'm wrong please let me know because i'm a very beginner

Answer 10

Hi Trustmeee,

Actually, what I meant about InStr is that I believe the original requirement is to remove not only the extension but also any other characters which may be preceeded by a dot.  The OP states "...all text right of the first decimal point..." so we should be able to find the first index of dot and then take the substring up to that index.  This resulting string can then have the new extension added.

Take your original code and just change the InStrRev to InStr then try it with the OP's example of "File_Name.DAT.123456".

On the question about the signature... actually I don't know!  LOL It should be in your profile when you click Edit but it looks like someone forgot the textbox when they last edited that webpage.  =P  It may also just have moved and I do not yet know where it is...  I'll try to find an answer to this!

---

Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

Answer 11

Hi Reed-

It seems as though the "GetFiles" method is not able to access the necessary directory.  I have already made sure the folders are all read/write etc.  I remember encountering a similar problem when working in Access/VBA using this method.

Is there an alternate way of doing this?  Otherwise I beleive the code you supplied is what I need.

Thank you!

---

Bonediggler

Answer 12

Hmmm, the GetFiles method should be able to return the contents of any folder that you have permission to read.

Are you getting a specific error on the call to GetFiles?  Or is it returning an empty array?

Can you explain a bit more about specifically how the code is failing?

---

Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

Answer 13

My bad.  It is not GetFiles() but rather System.IO.File.Move(file, newfile) that is throwing the error.  And after some further testing, it appears this error gets thrown when there are any files that have already been renamed (i.e. end in .txt instead of something else)....?

This will be an issue as the folder in question will always have normal (.txt) files that don't need to be renamed as well as abnormal (.txt.123456) files that do need to be renamed.

The error is as follows (some of this may be SSIS-specific):

Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.UnauthorizedAccessException: Access to the path is denied.
   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at System.IO.__Error.WinIOError()
   at System.IO.File.Move(String sourceFileName, String destFileName)
   at ST_4719df2bf6574140becf3fa6a5c195df.vbproj.ScriptMain.Main()
   --- End of inner exception stack trace ---
   at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

---

Bonediggler

Answer 14

I added this peice of code and am avoiding the error now:

If index > -1 And Right(file,4) <> ".txt" Then

---

Bonediggler

Answer 15

Ohhhh... well in that case the source and destination file names would be the same and that would cause an exception in Move().

Try this modification:

Dim files() As String = System.IO.Directory.GetFiles("c:\folder name\")
For Each file As String In files
    Dim index As Integer = file.IndexOf("."c)
    If index > -1 Then
        Dim newFile As String = String.Concat(file.Substring(0, index), ".txt")
        If Not file = newFile Then
            System.IO.File.Move(file, newFile)
        End If
    End If
Next

---

Reed Kimble - "When you do things right, people won't be sure you've done anything at all"