Hope someone can help... I am looking to create a small batch file or script that will take a flat CSV file and remove all dollar signs ($) from all the records in this CSV file and resave it with the same name, without those dollar signs.

Just so you have the background... this .csv file is a listing of records of names and addresses with several cost fields... each cost field has a $ before the cost.. I need to remove those dollar signs in an automated fashion so that I can import this .csv file into another program for processing.

Any ideas would be appreciated.

Tom P

Hi tomp

@echo off
SetLocal EnableDelayedExpansion

type nul > ~Tmp.txt
for /f "tokens=*" %%a in ('type CommaList.txt') do (
set Data=%%a
set Data=!Data:$=!
echo !Data! >> ~Tmp.txt
)
del CommaList.txt
ren ~Tmp.txt CommaList.txt

This is perfect.. thank you soooo much. What a great resource.

Tom P

One other question, is there a separate routine that will change the format of a date field? Currently, the date format in each of the records is 'yyyy/mm/dd" and I would like to change them all to mm/dd/yyyy

Is this possible?

Tom P

Maybe. If you post a few lines.

The layout you want to change to must be to satisfy the North American compulsion to get the DD in the middle. Which makes dates much harder to manipulate.

=====================================
If at first you don't succeed, you're about average.

M2

Here is the file with only one record...plus the headers. Hope this helps.

DOM/INTL,Account Number,Login ID,Shipment Number,Invoice Number,Date,Shipment Reference,Sent By,Receiver Company,Receiver Dept.,Receiver Address,Receiver City,Receiver State,Receiver Zip Code,Receiver Country,Receiver Phone Number,Receiver Attention To,Bill To,Bill To Account Number,DOX/WPX,Bill Duty To,Bill Duty To Account Number,Package Weight (Lbs),Package Description,Customs Value,Package Dimensions,Service Description,Declared Value,Est. Freight Charges,Est. Other Charges,Est. Total Charges,Est. Total Without Handling Fee,Consol Code
DOM,801429749,lsllps,21539048856,,2007-05-14,"Vaughn.P002","A. Young","Vaughn & bushnell Manufacturing Co.","","11414 Maple Avenue ","Hebron",IL,60034,United States,914-723-4300,"Mr. Robert Bachta",Sender,801429749,,Sender,,5,"",$0.00,None,Express,0,$35.47,$8.54,$44.01,$44.01,,,
DOM,801429749,lsllps,21551440352,,2007-05-14,"SHAPE.LIT","R. GOLDEN","UNITED STATES DISTRICT JUDGE","SOUTHERN DISTRICT OF NEW YORK","500 PEARL STREET ","New York",NY,10007,United States,2128050500,"THE HONORABLE P. KEVIN CASTEL",Sender,801429749,,Sender,,8 oz Letter,"",$0.00,None,Express,0,$9.16,$8.46,$17.62,$10.62,,,

Tom P

Perl solution that accomplishes both requirements.

====================================================
This does an inline edit of the csv file as well as creating a backup of the original file.

C:\>perl -pi.bak -e "s/\$//g; s~(\d{4})/(\d\d)/(\d\d)~$2/$3/$1~g;" my.csv

I'm getting an error indicating that 'perl' is not recognized as an internal or external command

Tom P

You need to download and install Perl, it's free.

http://activestate.com/store/produc...

I see that the format of your date string in the example differs from your prior statement.

You'll need to adjust the regular expression accordingly.

====================================================
C:\>perl -pi.bak -e "s/\$//g; s~(\d{4})-(\d\d)-(\d\d)~$2-$3-$1~g;" my.csv

this appears to be a pretty good software, but I don't want to have to load a whole program onto my customer's computer.. Is there a version that will allow it to run on any computer.

I would appreciate a standard batch file solution at this point, while I play with perl for a while.

Tom P

Perl scripts can be compiled to exe and be run on your customers computer without them having to install Perl.

There are several utilities available to do the compiling, one of the more common choices is Perl2Exe from indigostar.com
http://www.indigostar.com/perl2exe.htm

Another option, which might be better is PAR.
http://www.expertsrt.com/tutorials/...

great.. I will go through this and it will take me a good deal of time to become familiar with it..

In the meantime, if anyone has a batch file solution to the date reformatting issue, that would be preferrable because I need to get this done this week.

thank you again.

Tom P

Call me crazy but that file looks like a header and *TWO* records. The BAT below seems to do the deed, but perl is the right tool. The BAT is hard-wired and couls lose it's way at the least opportune time.

For the record, I think mm-dd-yyyy is trouble. But it's your data.

::== csvdate5.bat
:: changes date fmt in column 5
:: leanes row 1 [header] unchanged

@echo off > new.csv
setLocal EnableDelayedExpansion

for /f "tokens=1-6,* delims=," %%a in (the.csv) do (
set /a N+=1
if !N! equ 1 (echo %%a, %%b, %%c, %%d, %%e, %%f, %%g >> new.csv) else (
set old=%%e
set new=!old:~5,2!-!old:~8,2!-!old:~0,4!
echo %%a, %%b, %%c, %%d, !new!, %%f, %%g >> new.csv)
)
::==

=====================================
If at first you don't succeed, you're about average.

M2