Solved

Rename CSV file for MS Access 2010

Posted on 2015-01-22
8
161 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
  • 5
  • 2
8 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
post all possible variations
this is 1. Science_MP1

what else?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
better post all the file names of the .csv files
0
 
LVL 20

Accepted Solution

by:
GrahamMandeno earned 500 total points
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Closing Comment

by:shieldsco
Comment Utility
Thanks
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
@shieldsco

did you try my last post?
0
 

Author Comment

by:shieldsco
Comment Utility
Rey - the name is variable
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
I ask you about it and you never replied.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now