Batch file to rename network files with special characters

[debacle]

foxidrive edit: removed the colour tags as they don't function in a code block, only in text and quotes.

First time here and I have been searching the site for a bit to find a solution to my problem. While I think I have found what WOULD work, I have no idea how to change it. Its all C# to me (programmer humor).

Software we run in house uses Dbase4 files on a Netware 4.11 server. I am installing a W2008 Server w/ MySQL. I want to run a script nightly that takes the Dbase4 and converts them to csv which I will insert into my DB. So far I have this:

Code: Select all

REM Run DBFConverter to convert from DBF to CSV
"c:\Program Files\DBF Converter\dbfcnv.exe" "I:\Data\*.dbf" "\\Server-file\Data" /DOUBLEQUOTA /SEPPIPE /SKIPEMPTY /SKIPD /TOCSV

REM Rename the E4j and E4X files so it doesent have the apostrophe
REN "\\Server-file\Data\E4J'MAIN.csv" "\\Server-file\Data\E4JMAIN.csv"
REN "\\Server-file\Data\E4J'METH.csv" "\\Server-file\Data\E4JMETH.csv"
REN "\\Server-file\Data\E4J'PART.csv" "\\Server-file\Data\E4JPART.csv"
REN "\\Server-file\Data\E4X'MAIN.csv" "\\Server-file\Data\E4XMAIN.csv"
REN "\\Server-file\Data\E4X'METH.csv" "\\Server-file\Data\E4XMETH.csv"
REN "\\Server-file\Data\E4X'PART.csv" "\\Server-file\Data\E4XPART.csv"

REM Rename ink-xref and pap-xref so it doesent have the hyphen
REN "\\Server-file\Data\ink-xref.csv" "\\Server-file\Data\inkxref.csv"
REN "\\Server-file\Data\pap-xref.csv" "\\Server-file\Data\papxref.csv"

REM Run mysql.exe to update the SQL DB
"\\Server-file\$bin\mysql.exe" -f -h <host> --user=<user> --password=<password> <DB> < "//Server-file/$bin/ExcelImport.txt"

REM Remove the old CSV files out of the data directory
del "\\Server-file\Data\*.csv"

I was looking at Foxidrive's code he has posted in numberous places, but not sure how to begin to change it to fit my need. Possible anyone can let me know what I need to change? When I run it as below, it scans all files and folders on my desktop (where the .bat is currently located for testing).

Code: Select all

@echo off
cd \\Server-file\excel
for /f "delims=" %%a in ('dir /a:-d /o:n /b /s') do call :next "%%a"
GOTO:EOF
:next
set "newname=%~nx1"


set "newname=%newname: =_%"
set "newname=%newname:)=_%"
set "newname=%newname:(=_%"
set "newname=%newname:&=_%"
set "newname=%newname:^=_%"
set "newname=%newname:$=_%"
set "newname=%newname:#=_%"
set "newname=%newname:@=_%"
set "newname=%newname:!=_%"
set "newname=%newname:-=_%"
set "newname=%newname:+=_%"
set "newname=%newname:}=_%"
set "newname=%newname:{=_%"
set "newname=%newname:]=_%"
set "newname=%newname:[=_%"
set "newname=%newname:;=_%"
set "newname=%newname:'=_%"
set "newname=%newname:`=_%"
set "newname=%newname:,=_%"

echo ren %1 "%newname%

[debacle]

Sorry, I should have explained it better. Everything is working on my batch file except for the REN commands. I need to rename it due to 6 files having a single quote (') and 2 files having a hypon (-). SQL doesent like them so I renamed my tables to that in my ExcelImport.txt file. Now I just need to rename the csv files before running.

[foxidrive]

Your renames should work fine in this format:

Code: Select all

REN "\\Server-file\Data\pap-xref.csv" "papxref.csv"

Ren doesn't take a target path in the syntax.

[debacle]

Wow. I made that 100x more difficult than it needed to be. I was going to pushd and popd to create a temp mapping to rename the files. I feel about >.......................................................................................................................................< this big of an idiot right now. SOmetimes the best solutions are the simplest. Thanks.

[foxidrive]

And this should work, given that "\\Server-file\excel\*.csv" are the files to process.

There's nothing wrong in pushd/popd because I was using it before I saw your reply.

Of course you can remove the lines with the other characters.

Code: Select all

@echo off
pushd "\\Server-file\excel"
for /f "delims=" %%a in ('dir *.csv /b ') do call :next "%%a"
popd
GOTO:EOF
:next
set "newname=%~nx1"

set "newname=%newname: =_%"
set "newname=%newname:)=_%"
set "newname=%newname:(=_%"
set "newname=%newname:&=_%"
set "newname=%newname:^=_%"
set "newname=%newname:$=_%"
set "newname=%newname:#=_%"
set "newname=%newname:@=_%"
set "newname=%newname:!=_%"
set "newname=%newname:-=_%"
set "newname=%newname:+=_%"
set "newname=%newname:}=_%"
set "newname=%newname:{=_%"
set "newname=%newname:]=_%"
set "newname=%newname:[=_%"
set "newname=%newname:;=_%"
set "newname=%newname:'=_%"
set "newname=%newname:`=_%"
set "newname=%newname:,=_%"

ren %1 "%newname%

[debacle]

I think I will use this rather than statically defining the REN as it will give me greater flexibility in the future. I am sure I will reuse this code sometime soon. Thanks again.

And this should work, given that "\\Server-file\excel\*.csv" are the files to process.

There's nothing wrong in pushd/popd because I was using it before I saw your reply.

Of course you can remove the lines with the other characters.

Code: Select all

@echo off
pushd "\\Server-file\excel"
for /f "delims=" %%a in ('dir *.csv /b ') do call :next "%%a"
popd
GOTO:EOF
:next
set "newname=%~nx1"

set "newname=%newname: =_%"
set "newname=%newname:)=_%"
set "newname=%newname:(=_%"
set "newname=%newname:&=_%"
set "newname=%newname:^=_%"
set "newname=%newname:$=_%"
set "newname=%newname:#=_%"
set "newname=%newname:@=_%"
set "newname=%newname:!=_%"
set "newname=%newname:-=_%"
set "newname=%newname:+=_%"
set "newname=%newname:}=_%"
set "newname=%newname:{=_%"
set "newname=%newname:]=_%"
set "newname=%newname:[=_%"
set "newname=%newname:;=_%"
set "newname=%newname:'=_%"
set "newname=%newname:`=_%"
set "newname=%newname:,=_%"

ren %1 "%newname%

[brinda]

foxidrive,

this is a simple code for renaming. can easily understand the replacement

[foxidrive]

Thanks brinda. I noticed I had made a typo and left off the /b in the lower examples so I added them.