Editing a batch file names with file directory and string functions

[Shannon Meadows]

I would like to edit some files names to move the date from the end of the file name to the beginning. I have both .xlsx files and .csv files. All of the dates at the end of the file names are formatted as DD-Mon-YY, and the length of the file names are variable.

*** 0.A  Initializing local macros for filepaths and files
********************************************
local fldr_archive        "I:/Archive"

***************************************************************************************************
*** PART 1:    Changing working directory and Load file(s)
****************************************************************************************************

***1A. Change working directory
cd "`fldr_archive'"

***1B. Load files
dir *.csv

local filelist: dir "." files "*.csv"
di `"`filelist'"'

foreach file of local filelist {
    local file_date = substr("`file'",-13,9)
    local wanted = subinstr("`file'","`file_date'","",1)
    export delimited using "`fldr_archive'/`file_date'-`wanted'.csv", replace
clear  
}

I have attempted to use a local for file date, then subinstr to remove the file date, then save as intended with the date then the base file name. When I run this code I get files saved with the file date, but no base file name. With this method, I would need to repeat with edits to have the same process for .xlsx files due to the position of the date within the file name.

Please let me know if there are suggestions on how to fix and/or improve this method. Appreciate any suggestions.

[Mike Lacy]

Your use of -clear- removes all data so there is nothing to export, but that is a different problem than you mention. I don't know what you mean by "base file."

The following, with the use of my own "archive" folder, the elimination of -clear-, and some adjustment to the string position of the date, did what I think you want:

// Make some example files to test the solution
cd /temp
sysuse auto
export delimited using "testA-01-01-2003.csv"
export delimited using "testB-01-02-2003.csv"
export delimited using "testC-01-03-2003.csv"

*** 0.A  Initializing local macros for filepaths and files
********************************************
local fldr_archive        "c:/temp"

***************************************************************************************************
*** PART 1:    Changing working directory and Load file(s)
****************************************************************************************************

***1A. Change working directory
cd "`fldr_archive'"

***1B. Load files
dir *.csv

local filelist: dir "." files "test*.csv"  // test* because I have other csv files in c:/temp
di `"`filelist'"'

foreach file of local filelist {
    // I adjusted date position.
    local file_date = substr("`file'",-14,10)
    di "`file_date'"
    local wanted = subinstr("`file'","`file_date'","",1)
    di "`wanted'"
    export delimited using "`fldr_archive'/`file_date'-`wanted'", replace // csv already there
// clear  
}
ls *test*.csv
   4.5k  10/18/24 13:27  01-01-2003-testa-.csv
   4.5k  10/18/24 13:27  01-02-2003-testb-.csv
   4.5k  10/18/24 13:27  01-03-2003-testc-.csv
   4.5k  10/18/24 13:27  testA-01-01-2003.csv
   4.5k  10/18/24 13:27  testB-01-02-2003.csv
   4.5k  10/18/24 13:27  testC-01-03-2003.csv

[Shannon Meadows]

Hello Mike Lacy, thank you for your reply. To clarify, the base file name I was referring to was the remainder of the file name after I removed the date. Examples from my filelist include: inpatient (1)-02oct2024.csv, inpatient-06nov2024.csv

I removed the clear command, and the '.csv' from the export command since it was already included in the `wanted' macro as you pointed out. When I run the updated script, I receive an error r(102), too few variables specified. When I displayed my `filelist' I can see the 40+ files listed.

Do you have any suggestions on how I would resolve this error? Kind thanks

*** 0.A  Initializing local macros for filepaths and files
local fldr_archive        "I:/Archive"
*** PART 1:    Changing working directory and Load file(s)

***1A. Change working directory
cd "`fldr_archive'"

***1B. Load files
dir *.csv

local filelist: dir "." files "*.csv"
di `"`filelist'"'
 
foreach file of local filelist {
    local file_date = substr("`file'",-13,9)
    local wanted = subinstr("`file'","`file_date'","",1)

    export delimited using "`fldr_archive'/`file_date'-`wanted'", replace
}

[Mike Lacy]

Where is the error occurring? (Try inserting some echos like -display "here 1"-, -display "here 2"-, etc. in your code if you don't know.) Knowing *where* the error is occurring is the first step to solving the problem.

My presumption is that you will find that the problem is with with your -export- command, so try using the -set trace- command around it to see what the problem is. If you haven't used this before, you'll find the output a bit messy, but just look back in the results windows to see what the -export- command looks like when it actually runs.

....
set trace on
export delimited using "`fldr_archive'/`file_date'-`wanted'", replace
set trace off
....

[Clyde Schechter]

I have to say that I don't see any command in #3 that would, in its own right, give the -too few variables specified- error. But perhaps something in the commands that those commands call does that? Mike Lacy's advice for finding the source of the problem is spot on.

That said, I just want to point out that the command -local file_date = substr("`file'",-13,9)- might be problematic. If any of the filenames is shorter than 13 characters, then character -13 will not exist in that value of `file', and -substr()- will return an empty string that will mess things up downstream from there. (Try running -display substr("abcde", -6, 3)- to see what I'm talking about.)