Solved

Rename CSV file for MS Access 2010

Posted on 2015-01-22
8
165 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

785 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