Solved

Rename CSV file for MS Access 2010

Posted on 2015-01-22
8
163 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
ID: 40565172
post all possible variations
this is 1. Science_MP1

what else?
0
 
LVL 119

Expert Comment

by:Rey Obrero
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
 
LVL 119

Expert Comment

by:Rey Obrero
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Closing Comment

by:shieldsco
ID: 40565579
Thanks
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40565587
@shieldsco

did you try my last post?
0
 

Author Comment

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

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

947 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

20 Experts available now in Live!

Get 1:1 Help Now