Truncating of a CSV Filename before saving

[shriil]

Hi

I have some .csv files of stocks which I download from the Internet
and are saved in the following format

01-09-2008-TO-04-01-2009RELINFRAEQN.csv
01-09-2008-TO-04-01-2009JPASSOCIATXN.csv
01-09-2008-TO-07-01-2009POWERGRIDEQN.csv

I would like to run a common macro such that I can again save them
as .csv files but with only the stock name, i.e.

as RELINFRA.csv, JPASSOCIAT.csv, POWERGRID.csv.

The prefix (01-09-2008....04-01-2009) and the suffix (EQN or XN,
whichever is the case) needs to be removed from each filename

I hope I could convey my requirement.

Thks in advance.

Shriil

 

[Rick Rothstein]

You can use this function to parse down the path/filenames you have...

Function GetStockName(FilePathAndName As String) As String
GetStockName = Mid(FilePathAndName, 25)
GetStockName = Replace(GetStockName, "EQN.", ".", , , vbTextCompare)
If InStr(1, FilePathAndName, "XNEQN.", vbTextCompare) = 0 Then
GetStockName = Replace(GetStockName, "XN.", ".", , , vbTextCompare)
End If
End Function

An example of its use...

MsgBox GetStockName("01-09-2008-TO-04-01-2009RELINFRAEQN.csv")

 

[Dave Peterson]

You can manipulate the strings by removing the first few characters. And
extracting the middle (avoiding that "suffix", but how do you know what the
suffix really is.

Do you have a complete list?

How do you know that EQN (3 characters) and XN (only two characters) are
suffixes. Why isn't TXN the suffix?

If they were always 3 character suffixes, you could use something like:

Dim OrigFileName As String
Dim NewFileName As String

OrigFileName = "01-09-2008-TO-04-01-2009RELINFRAEQN.csv"

'remove the first 24 characters
NewFileName = Mid(OrigFileName, 25)

'remove the last 7 characters and add back .csv
NewFileName = Left(NewFileName, Len(NewFileName) - 7) & ".csv"

MsgBox NewFileName

========
But unless you have a complete list, I'm not sure how the code would work.

 

[Ron Rosenfeld]

Hi

I have some .csv files of stocks which I download from the Internet
and are saved in the following format

01-09-2008-TO-04-01-2009RELINFRAEQN.csv
01-09-2008-TO-04-01-2009JPASSOCIATXN.csv
01-09-2008-TO-07-01-2009POWERGRIDEQN.csv

I would like to run a common macro such that I can again save them
as .csv files but with only the stock name, i.e.

as RELINFRA.csv, JPASSOCIAT.csv, POWERGRID.csv.

The prefix (01-09-2008....04-01-2009) and the suffix (EQN or XN,
whichever is the case) needs to be removed from each filename

I hope I could convey my requirement.

Thks in advance.

Shriil

If the format is fairly close to what you have posted, then the following
should return the acceptable filename.

A critical part of the format is that the portion of the string to be discarded
ends with "-20nn"

================================
Option Explicit
Function TrimFN(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True

'note that suffix list is a pipe-delimited
'list enclosed in parentheses
re.Pattern = "^.*-20\d\d(.*)(EQN|XN)(.csv)"

'to make both suffix list and the csv suffix
'case-insensitive, UNcomment the next line
' re.ignorecase = True

If re.test(str) = True Then
TrimFN = re.Replace(str, "$1$3")
End If
End Function
==========================================
--ron

 

[shriil]

If the format is fairly close to what you have posted, then the following
should return the acceptable filename.

A critical part of the format is that the portion of the string to be discarded
ends with  "-20nn"

================================
Option Explicit
Function TrimFN(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
    re.Global = True

'note that suffix list is a pipe-delimited
'list enclosed in parentheses
    re.Pattern = "^.*-20\d\d(.*)(EQN|XN)(.csv)"

'to make both suffix list and the csv suffix
'case-insensitive, UNcomment the next line
'    re.ignorecase = True

If re.test(str) = True Then
    TrimFN = re.Replace(str, "$1$3")
End If
End Function
==========================================
--ron- Hide quoted text -

- Show quoted text -

Thanks a lot for the all the solutions provided. I will be trying them
out.

Yes, replying to Dave's query, one thing I am sure that the suffix
will either be "EQN" or "XN", i.e. either 3 characters or 2
characters.

 

[shriil]

You can manipulate the strings by removing the first few characters.  And
extracting the middle (avoiding that "suffix", but how do you know what the
suffix really is.

Do you have a complete list?

How do you know that EQN (3 characters) and XN (only two characters) are
suffixes.  Why isn't TXN the suffix?

If they were always 3 character suffixes, you could use something like:

Dim OrigFileName As String
Dim NewFileName As String

OrigFileName = "01-09-2008-TO-04-01-2009RELINFRAEQN.csv"

'remove the first 24 characters
NewFileName = Mid(OrigFileName, 25)

'remove the last 7 characters and add back .csv
NewFileName = Left(NewFileName, Len(NewFileName) - 7) & ".csv"

MsgBox NewFileName

========
But unless you have a complete list, I'm not sure how the code would work..












--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks Dave. Yes, one thing I am sure that the suffix
will either be "EQN" or "XN", i.e. either 3 characters or 2
characters

Do I need a complete list of the csv files or can I just open the csv
file one by one and run the program? The macro can be in the personal
workbook.

What do I have to do to modify your program such that
the program can catch the name of the original file, after which it
checks whether the suffix is EQN or XN, and then accordingly parses
the filename and save it.

Thks for the help

Shriil