Need batch script to replace CR-LF in .dsv file

If the last column isn't indicated (like in the referenced question), it's impossible to determine whether a CRLF is a row terminator or a line break that needs to be removed.
So further knowledge of the exact structure of the file is required, "similar" is not enough.
1. How many columns does the file have?
2. Does it have a header line?
3. Are the cell contents enclosed in double quotes?
In addition, please provide some sample lines (inside [code][/code] tags).

Hi oBdA,
Thanks for replying !

1. the file has 21 columns,
2. yes , the file has a header line.
3.yes, the contents are enclosed in double quotes.

Here's the sample data,

"VisitKey"|"CallKey"|"CallDateTime"|"CallAssignment"|"CallType"|"ProcedureCode"|"ClientIdentifierOnCall"|"ServiceEnteredOnCall"|"MobileLogin"|"VisitLocation"|"VisitNotes"|"CallLatitude"|"CallLongitude"|"TelephonyPIN"|"CallTimeZone"|"OriginatingPhoneNumber"|"RecordUpdatedBy"|"RecordUpdateDateTime"|"GroupCode"|"VisitLocationType"|"CallNoteKey"
 
"1234567890"|"1961605585"|"2011-01-24T18:02:25Z"|"Call Out"|"MOBILE"|"W7060"|"991673"|"W7890"|shshshshsh@gmail.com||"Kyle wanted to go see animals,
so we tried the Humane Society, but they were not open to the public.
So, we drove to  to the  Animal Rescue. We couldn¿t go inside, but we were able to walk around outside where we saw donkeys, ponies, horses,
sheep, etc.
when we got back, my engine light was on, so I stopped to get oil.
Once we were back, we cleaned out the refrigerator and it was time for me to go.
Kyle doesn¿t want me to come on Wed or Fri because his friend will be here."|"40.28404045044118"|"-76.65530852494733"||"US/Eastern"||shshshshsh@gmail.com|"2011-01-24T18:03:03Z"|||"887951841"


Please let me know if you have any more questions. Thanks.

This is PowerShell.
You can set a comma separated list of column names to process in line 1 (for example $processProps = 'VisitNotes', 'VisitLocation').
If you want to replace the CRLF with a space instead of completely removing it, replace the empty pair of single quotes at the end of line 4 with a space enclosed in single quotes (' ' instead of '').

$processProps = 'VisitNotes'
Import-Csv -Path '.\input.dsv' -Delimiter '|' | ForEach-Object {
	ForEach ($prop in $processProps) {
		$_.$prop = $_.$prop -replace '\r?\n', ''
	}
	$_
} | Export-Csv -NoTypeInformation -Path '.\output.dsv'

Select allOpen in new window

hi oBdA,

Thanks for script, its working fine. I m having an issue while i m running PS script on my server ? can you please send me a batch script ? 

"issue" being what exactly?

The server is not supporting PS scripts, when i execute the script you sent on CMD prompt or powershell it works fine but on server it throughs execution error on informatica.

PowerShell comes with the OS since Server 2008. Please don't tell me you still have Server 2003.
So what is the server OS, and what is the exact error message when you're trying to run the script on the server?

the error is on informatica server, 

ERROR: "LM_36623 Command task instance [Command_Task_SAVE_ChangeTracking]: execution of command [] did not complete successfully with exit code [1]" 

This is not a PowerShell error message.
So what exactly are you trying to do how?
Remember that nobody on EE except you knows anything about your environment, so how do you expect someone to help you if trying to get information out of you is like pulling teeth?

It's working fine on PowerShell and CMD. I m having an issue on Informatica server which is reject the PowerShell script for some reason. so i want to try the BatchScript for the same issue. sorry about not giving proper info.

So what exactly is Informatica  server doing, and how exactly did you define that "task instance" or "job" or whatever that is?
If you're not running this in a PS instance where you have control over the current path, you'll probably have to use full absolute paths in lines 2 and 7, and whatever account is executing the job must have the permissions to read from the source file and write to the target file.

The code is working fine i have given exact paths. In informatica command task, all i need to specify the path of the psscript that i want to execute. when i do that, the output file is not being generated like when it's done on cmd or powershell and throwing the error mentioned above.

So, I just want to see if batchscript works when i run it on informatica command task.

Did you set the ExecutionPolicy on the server to RemoteSigned or Bypass (if the script is on a UNC)?

Here's a BAT approach if you want to play with that...

@echo off
setlocal EnableDelayedExpansion

set FileIn=input.txt
set FileOut=output.txt

set q="
set LineOut=

(
    for /f "tokens=*" %%A in ('type "%FileIn%"') do (
        set LineIn=%%A
        if /i "!LineIn:~-1!" EQU "!q!" (
            if defined LineOut (
                echo.!LineOut! %%A
                set LineOut=
            ) else (
                echo.%%A
            )
        ) else (
            if defined LineOut (
                set LineOut=!LineOut! %%A
            ) else (
                set LineOut=%%A
            )
        )
    )
) > "%FileOut%"

Select allOpen in new window

---

»bp

Thanks oBdA and Bill Prew for your response. :) 

Hi Bill,

I m not getting any output when i m running this script. Its just generating the empty output.txt file.

I have given paths in filein and fileout. 

Hi Bill,
I m not getting any output when i m running this script. Its just generating the empty output.txt file.
I have given paths in filein and fileout.

It worked on a test here, attaching my input and output test files.

Can you upload your test file so I can try it here?

And it might not hurt for you to upload your actual BAT file with the changes you made...

input.txt
output.txt

---

»bp

oBdA, I myself/my colleagues aren't sure what the issue is.

Bill, I m using the same test file.
Please find the script i used to run, I also tried putting the paths in quotes.
------

@echo off
setlocal EnableDelayedExpansion
set FileIn=DEV\EXTRACTS\Extracts\MAPP\MPP\MPP_TST\Input.dsv
set FileOut=DEV\EXTRACTS\Extracts\MAPP\MPP\MPP_TST\output.dsv
 
set q="
set LineOut=
 
(    
               for /f "tokens=*" %%A in ('type "%FileIn%"') do (  
                              set LineIn=%%A
        if /i "!LineIn:~-1!" EQU "!q!" (
                                             if defined LineOut (
                                                            echo.!LineOut! %%A
                set LineOut=     
                                                            ) else (        
                                                                           echo.%%A     
                                                            )  
                                             ) else (  
                                                            if defined LineOut (    
                                                                           set LineOut=!LineOut! %%A        
                                                            ) else (     
                                                                set LineOut=%%A   
                                                            )
                                               )     
                              )  
) > "%FileOut%"

Select allOpen in new window

Also testing this script,

@PowerShell.exe -Command "Invoke-Expression -Command ((Get-Content -Path '%~f0' | Select-Object -Skip 2) -join [environment]::NewLine)" @exit /b %Errorlevel% $processProps = 'VisitNotes' Import-Csv -Path '.\input.dsv' -Delimiter '|' | ForEach-Object {    ForEach ($prop in $processProps) {       $_.$prop = $_.$prop -replace '\r?\n', ''    }    $_ } | Export-Csv -NoTypeInformation -Path '.\output.dsv'


Thanks oBdA & Bill

How is this a valid file name?

DEV\EXTRACTS\Extracts\MAPP\MPP\MPP_TST\Input.dsv

If DEV is a server share, then I would expect that to be:

\\DEV\EXTRACTS\Extracts\MAPP\MPP\MPP_TST\Input.dsv

Am I missing something?

Also, as I suggested before, please attach and upload your actual test file, there could be subtle differences in encoding that aren't visible to the eye in the data.  Don't cut and paste it, rather attach it as a file upload to a comment you post.

---

»bp