Renaming a file

Posted on 2013-01-10
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 170 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.
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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 150 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

626 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