?
Solved

Rename CSV file for MS Access 2010

Posted on 2015-01-22
8
Medium Priority
?
180 Views
Last Modified: 2015-01-22
I'm using the code below to rename  CSV files.

On Error Resume Next
Dim csvFile As String, xFile As String, csvFolder As String
 Dim vArr() As String, j As Integer, xName As String

 csvFolder = "C:\Students\"  '<<< CHANGE to CORRECT path
 csvFile = Dir(csvFolder & "*.csv")
 While csvFile <> ""

         vArr = Split(csvFile, " ")
         For j = 0 To UBound(vArr)
             If InStr(vArr(j), "Science_MP1") Then
                 xFile = vArr(j)
                 Exit For
             End If
         Next
         xName = Mid(csvFile, InStrRev(csvFile, "("))
         xName = Replace(Replace(xName, "(", ""), ")", "")
         Debug.Print xFile & "_" & xName
         
         Name csvFolder & csvFile As csvFolder & xFile & "_" & xName
         
         csvFile = Dir()
 Wend

The code works fine when the CSV file in the following format:
Grade Sheet Report for test 05_Science_MP1 for section 5th Grade Science-5th Grade Science-002-04(M-F)(Patricia Ashley).csv and I get the following expected result:
05_Science_MP1_Patricia Ashley.csv

If change Science to Math I get an unexpected result:
_Patricia Ashley.csv
I need to adjust the code to select starting with the first number and include anything after the two underscores plus anything between the last ()
0
Comment
Question by:shieldsco
[X]
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
  • 5
  • 2
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40565172
post all possible variations
this is 1. Science_MP1

what else?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40565174
better post all the file names of the .csv files
0
 
LVL 20

Accepted Solution

by:
GrahamMandeno earned 2000 total points
ID: 40565528
It looks like the input format of your CSV file names is:

<Discard1><TestName><Discard2>(StudentName).csv

and the desired result is:

<TestName>_<StudentName>.csv

You are correctly extracting <StudentName> using InstrRev to find the last left-parenthesis.

The problem appears to be identifying <TestName> .  As you have discovered, you cannot search for "Science_MP1" because it doesn't work for "Math_MP1".

QUESTION:  Does the filename always begin with "Grade Sheet Report for test "?

If so, then it is easy to remove a constant string to get to the <TestName>:

Dim csvFile As String, csvFolder As String
Dim sTestName As String, sStudentName As String
Dim sNewName as String
Const cStringToDiscard =  "Grade Sheet Report for test "
  csvFolder = "C:\Students\"  '<<< CHANGE to CORRECT path
  csvFile = Dir(csvFolder & "*.csv")
  Do Until csvFile = ""
    If Left(csvFile, Len(cStringToDiscard)) = cStringToDiscard Then
      sTestName = Mid(csvFile, Len(cStringToDiscard) + 1) ' discard prefix
      sTestName = Left(sTestName, InStr(sTestName, " ") - 1)  'discard everything from first blank onward

      sStudentName = Mid(csvFile, InStrRev(csvFile, "(") + 1) ' discard everything up to and including last "("
      sStudentName = Left(sStudentName, InStr(sStudentName, ")") - 1)  ' discard everything from first ")" onward

      sNewName = sTestName & "_" & sStudentName & ".csv"
      Debug.Print sNewName
         
      Name csvFolder & csvFile As csvFolder & sNewName
         
    End If
    csvFile = Dir()
  Loop

Open in new window

Good luck!
Graham Mandeno [Access MVP 1996-2015]
0
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40565577
revised codes


On Error Resume Next
 Dim csvFile As String, xFile As String, csvFolder As String
  Dim vArr() As String, j As Integer, xName As String

  csvFolder = "C:\Students\"  '<<< CHANGE to CORRECT path
  csvFile = Dir(csvFolder & "*.csv")
  While csvFile <> ""

         For j = 0 To UBound(vArr)
              If InStr(vArr(j), "Science_MP1") Then
                  xFile = vArr(j)
                  Exit For
                  elseif InStr(vArr(j), "Math_MP1") Then
                  xFile = vArr(j)
                  Exit For
              End If
          Next
         xName = Mid(csvFile, InStrRev(csvFile, "("))
          xName = Replace(Replace(xName, "(", ""), ")", "")
          'Debug.Print xFile & "_" & xName
         
          Name csvFolder & csvFile As csvFolder & xFile & "_" & xName
         
          csvFile = Dir()
  Wend
0
 

Author Closing Comment

by:shieldsco
ID: 40565579
Thanks
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40565587
@shieldsco

did you try my last post?
0
 

Author Comment

by:shieldsco
ID: 40565593
Rey - the name is variable
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40565599
I ask you about it and you never replied.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

777 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