Solved

Rename CSV file for MS Access 2010

Posted on 2015-01-22
8
170 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 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 500 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
Technology Partners: 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

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

696 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