Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
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 enabledelayedexpansionfor /f "tokens=*" %%a in ('type "%~1"') do (
set line=%%a
echo "!line:","=|!">>"%~2"
)
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.
0 
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....?
0
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
0
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. )
0
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.
0
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
0
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 )
1
Thank you Judago.... wherever you are.. :)
That worked like I charm. I really appreciate the help on this.
-- Mark
0  |
 |
 |
| Login or Register to Reply | |
| Login | Register |
| Ads by Google |
