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
  • 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.
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
It’s quite interesting for me as I worked with Excel using for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

785 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