Batch File to Search and Replace

Microsoft Windows xp professional editio...
September 24, 2009 at 18:04:37
Specs: Windows XP
Hello,

I am attempting to modify a batch file to do the following.

Take a CSV file where each field is enclosed in quotes and make it pipe-delimited.

"," to |

I also need to get rid of the first and last character on each line (orphan " after search and replace runs).

And, need to kill the header row.

Here is the script - would appreciate any help. Thank you.

@echo off
setlocal enabledelayedexpansion

for /f "tokens=*" %%a in ('type "%~1"') do (
set line=%%a
echo "!line:","=|!">>"%~2"
)


See More: Batch File to Search and Replace

Report •


#1
September 24, 2009 at 20:02:15
It's hard to tell without seeing some of the data. Post a few lines including the header and what it should look like after being parsed.

Will any of the records contain "!"?
Are their a fixed number of records per line?


By the way these sort of posts belong in the programming forum, generally you will get a faster response there.


Report •

#2
September 24, 2009 at 20:26:03
Sure here is additional info:

Sample:
"Header1","Header2","Header3","Header4"
"Row1Field1","Row1Field2","Row1Field3","Row1Field4"
"Row2Field1","Row2Field2","Row2Field3","Row2Field4"

I guess there is a chance that one of the fields (ie, field 1 in row 1) could have a ! in it. For example, the field might represent a comment field from the CRM system and the value might be "This guy is ready to buy!"

The number of rows in the file will be variable.

Thanks for the tip - doesn't look like I can move this one over there....?


Report •

#3
September 24, 2009 at 20:27:33
Oh, and here is what it should look like when batch is done running:

(No header row)

Row1Field1|Row1Field2|Row1Field3|Row1Field4
Row2Field1|Row2Field2|Row2Field3|Row2Field4


Report •

Related Solutions

#4
September 24, 2009 at 20:41:41
Here's a quick knock up, see how it goes.

If you want to keep you !'s don't enable delayed expansion.

@ECHO OFF
for /f "usebackq skip=1 delims=" %%a in ("%~1") do (
    for %%b in (%%a) do (
        (set /p =%%~b<nul) >> "%~2"
	for /f "tokens=4 delims=," %%c in ("%%a") do if not "%%~c"=="%%~b" (set /p =^|<nul) >> "%~2"
    )
    >> "%~2" echo.
)

http://www.computing.net/howtos/sho...


Report •

#5
September 24, 2009 at 20:52:05
Ok I just realised I went for the rube goldberg approach above. Much simpler:

@ECHO OFF
for /f "usebackq skip=1 tokens=1-4 delims=," %%a in ("%~1") do (
    >> "%~2" echo %%~a^|%%~b^|%%~c^|%%~d
)

[edit]
This approach won't work if your fields contain commas.


Report •

#6
September 24, 2009 at 21:12:09
Both were close, but not exactly there...

The values in my fields can have commas

It seems to me the easiest way would be to first remove first and last characters from each row and then replace every instance of quote comma quote (",") with a pipe

Mark


Report •

#7
September 24, 2009 at 21:23:56
Can the data fields also hold double quotes?

http://www.computing.net/howtos/sho...


Report •

#8
September 24, 2009 at 21:31:35
No, the fields will not include double quotes.

Report •

#9
September 24, 2009 at 21:34:12
You were right from the start ;), this should deal with just about any characters(even the ! will be kept).

@echo off
for /f "usebackq skip=1 delims=" %%a in ("%~1") do (
    set data=%%~a
    setlocal enabledelayedexpansion
    set data=!data:","=^|!
    >> "%~2" echo !data!
    endlocal
)

http://www.computing.net/howtos/sho...


Report •

#10
September 24, 2009 at 21:45:10
Thank you Judago.... wherever you are.. :)

That worked like I charm. I really appreciate the help on this.

-- Mark


Report •

#11
September 24, 2009 at 21:46:29
Glad I could help.

http://www.computing.net/howtos/sho...


Report •


Ask Question