Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Renaming a file

Posted on 2013-01-10
Medium Priority
Last Modified: 2013-01-13
I have text files in C:\folder\ListofSchools. These textfiles uses their IDs as file names.


.... etc

However, in the database, their  codeDesc  are..

id         codeDEsc

39991     Prarie
29211     Westwind.

Id like to rename the ids or replace it with their codeDesc ..

I was thinking of creating a stored procedure that will extract the name from Id such as

Select codeDesc from tblSchool where id =  the file name  that is  listed in the folder..

What will be the code to loop to the folder?
What code to rename the id with the code desc?

Has anyone done this directory or file processing?

I appreciate if you can send me code examples how it is done?
Question by:zachvaldez
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
LVL 25

Assisted Solution

by:Luis Pérez
Luis Pérez earned 680 total points
ID: 38766282
Well, this is a basic skeleton of what you need to do. What I would do in your case is the following:

1. Create a stored procedure to retrieve the complete listing of schools and their corresponding names.

    SELECT [id], [codeDEsc] FROM [MyTable]

Open in new window

2. Create the code to call this procedure. You didn't say anything about your database, so I will guess it's SQL Server.

'Put these Imports at the beginning of the form/class/module where you will put the rest of the code
Imports System.Data.SqlClient
Imports System.IO

'Declare a connection against the database
Using connection As SqlConnection = New SqlConnection("connection_string")
    'Open the connection

    'Declare a command to invoke the stored procedure
    Using cmd As SqlCommand = New SqlCommand("myProcedure", connection)
        'We must set the proper command type. In this case, a stored procedure.
        cmd.CommandType = CommandType.StoredProcedure

        'Declare a reader to loop over retrieved records
        Using reader As SqlDataReader = cmd.ExecuteReader()
            'Loop over the records
            While reader.Read
                'Now it's time to effectively rename the files

                'First, compose the full path of the existing file
                Dim existingFile As String = Path.Combine("C:\folder\ListofSchools", reader("id").ToString() + ".txt")

                'Rename it only if exists
                If File.Exists(existingFile) Then
                    'Compose the new name
                    Dim newFile As String = Path.Combine("C:\folder\ListofSchools", reader("codeDEsc") + ".txt")

                    'Rename the file
                    File.Move(existingFile, newFile)

                    'And that's all. This code will repeat for each one of the database records.
                End If
            End While
        End Using
    End Using
End Using

Open in new window

Hope that helps.
LVL 14

Expert Comment

ID: 38766341
Looks like RolandDeschain beat me to posting, but I came up with a solution from the opposite angle. Instead of looping through the database, I'm looping through the file system.

For Each curFile In Directory.GetFiles("C:\test\", "*.*", SearchOption.AllDirectories)
            Dim curFileInfo As FileInfo = New FileInfo(curFile)
            Dim codeDesc As String = getCodeDescFromDatabase(Replace(curFileInfo.Name, curFileInfo.Extension, ""))
            curFileInfo.MoveTo("C:\test\" & codeDesc & "." & curFileInfo.Extension)

Open in new window

The function getCodeDescFromDatabase is where you would call your stored procedure to lookup the value in the database.
LVL 25

Expert Comment

by:Luis Pérez
ID: 38766347
Well, I did it that way because I think that it's better and much optimized to access the database only one time.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 14

Expert Comment

ID: 38766370
@RolandDeschain, didn't mean to knock on your solution. It is much better optimized to access the database one time. I had already taken the time to write up my solution before seeing that you posted. My solution, while less efficient, seemed possibly easier to read or understand if the poster was looking at the problem from the file system instead of what was the most efficient.

You solution is the more efficient solution, but I thought mine might have some merit in looking at the solution from the other side.

@zachvaldez, you should use RolandDeschain's solution if it makes sense and works for you.

Author Comment

ID: 38768382
Just a question. Why isn't any parameter pass like

"where id = @param

@param being the currentfile name which is the id?
and as it loops th @param changes... and renames it with the description...

Author Comment

ID: 38768725
@RolandDeschain code is doing what it supposed to .
However if the name has "/" as  "Dogwood/Subway" on that Id, it errors out and stops the loop.
What the best way to handle this?
LVL 14

Accepted Solution

quizwedge earned 600 total points
ID: 38768822
Just to explain your first question, In my solution, I didn't include the SQL code, but yes, you would need a query with "where id = @param".

For RolandDeschain's solution, you don't need the where clause in the SQL. He is getting all of the records in the database and then looping through them. He is then trying to find a file with the ID name. See line 22 of his code: reader("id") gets the current ID and changes with every iteration of the loop.

The problem with "/" is that they're not allowed in file names for Windows. To get around this, you'll have to replace all slashes with a different character such as underscore. For example, change RolandDeschain's line 27 to the following:

Dim newFile As String = Path.Combine("C:\folder\ListofSchools", Replace(reader("codeDEsc"), "/", "_") + ".txt")

Open in new window

Or, replace my line 3 to the following:
Dim codeDesc As String = Replace(getCodeDescFromDatabase(Replace(curFileInfo.Name, curFileInfo.Extension, "")), "/", "_")

Open in new window

You'll probably find other characters that don't work. You can either keep adding Replace functions or create a new function, for example, ReplaceInvalidCharacters() and handle any replacements there.

Just for completeness and in case this makes more sense to you, here is another way to tackle this.

The steps are the following:
1. Call a stored procedure that gets all of the rows in the database table

2. Set the column id as the primary key. Note, for this to work, each ID in the column id must be unique.

3. Loop through the files in the directory.

4. Find the corresponding row using the Find command on the dataset returned from the database.

5. If the row exists, update the file name. If the row does not exist, show an error message.

Hope that helps. Let me know if you have any additional questions.

        Dim mConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection("connection_string")
        Dim myCommand As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter("myProcedure", mConnection)

        Dim myDataSet As DataSet = New DataSet
        myCommand.Fill(myDataSet, "FileNames")
        myDataSet.Tables(0).PrimaryKey = New DataColumn() {myDataSet.Tables(0).Columns("id")}

        Dim curFile As String = ""
        For Each curFile In Directory.GetFiles("C:\test\", "*.*", SearchOption.AllDirectories)
            Dim curFileInfo As FileInfo = New FileInfo(curFile)
            If Not myDataSet.Tables(0).Rows.Find(Replace(curFileInfo.Name, curFileInfo.Extension, "")) Is Nothing Then
                Dim codeDesc As String = CStr(myDataSet.Tables(0).Rows.Find(Replace(curFileInfo.Name, curFileInfo.Extension, "")).Item("codeDEsc"))
                curFileInfo.MoveTo("C:\test\" & Replace(codeDesc, "/", "_")  & "." & curFileInfo.Extension)
                MsgBox("Error: " & Replace(curFileInfo.Name, curFileInfo.Extension, "") & " was not found in the database!")
            End If

Open in new window


Author Closing Comment

ID: 38773333
Both approaches worked!

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question