Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Renaming a file

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

Ex.

39991.txt
29221.txt
.... 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..

question..
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?
0
Comment
Question by:zachvaldez
  • 3
  • 3
  • 2
8 Comments
 
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.

CREATE PROCEDURE myProcedure
AS
BEGIN
    SET NOCOUNT ON;
    SELECT [id], [codeDEsc] FROM [MyTable]
END

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
    connection.Open

    '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.
0
 
LVL 14

Expert Comment

by:quizwedge
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)
 Next

Open in new window


The function getCodeDescFromDatabase is where you would call your stored procedure to lookup the value in the database.
0
 
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.
0
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.

 
LVL 14

Expert Comment

by:quizwedge
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.
0
 

Author Comment

by:zachvaldez
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...
0
 

Author Comment

by:zachvaldez
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?
0
 
LVL 14

Accepted Solution

by:
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
        mConnection.Open()
        myCommand.Fill(myDataSet, "FileNames")
        mConnection.Close()
        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)
            Else
                MsgBox("Error: " & Replace(curFileInfo.Name, curFileInfo.Extension, "") & " was not found in the database!")
            End If
        Next

Open in new window

0
 

Author Closing Comment

by:zachvaldez
ID: 38773333
Both approaches worked!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
It’s quite interesting for me as I worked with Excel using vb.net 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 …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Loops Section Overview

963 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