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
  • 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 VB.net 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.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Integration Management Part 2
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

616 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