stripping carriage returns from .txt file

Are the carriage returns all at the end of the lines/rows, or are they in the text fields in the middle of the rows?

do you want a batch file or will a vbs file work?

from command prompt type

c:\>cscript fnr.vbs mytext.txt

'fnr.vbs removes all carriage returns
Const ForReading = 1, ForWriting = 2
strFileName = Wscript.Arguments(0)
strnewtext = ""   'change this if you want to replace the carriage return with something particulay
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFileName, ForReading)
 
strText = objFile.ReadAll
objFile.Close
strNewText = Replace(strText, vbCrLf, strnewtext)
 
Set objFile = objFSO.OpenTextFile(strFileName, ForWriting)
objFile.WriteLine strNewText
objFile.Close

Select allOpen in new window

You have to use VB script to transform data.

Open you DTS package. Click Properties on data trasnformation task (an arrow that goes from Access icon to Text file icon). Now go to trasnformations tab.
If all you columns are transferred in one transformation (Type=XCopy or somethin like that), you will heave to remove it, and add "Copy Column" transformation for each column except column with line breaks. If not, just remove "Copy column" transformation for you column.
Now add new "ActiveX Script" transformation. Click properties. You will have to use scrupt like that to transform data:

urce column to the destination column
Function Main()
      DTSDestination("<your column name>") = REPLACE(DTSSource("<your column name>"), vbCrLf, "")
      Main = DTSTransformStat_OK
End Function

knightEknight:
Are the carriage returns all at the end of the lines/rows, or are they in the text fields in the middle of the rows?

They are in the middle of rows.  I didn't anticipate the command weellio gave above getting rid of CRLF.  Is it possible to  change the code to eliminate all CR's but leave instances of the CRLF's alone?

Thanks,
Dave

change the  vbCrLf  to  vbCr  

Rimvis:

I have inserted the script, one replace for each column (there are 30 columns), but I get the error:

The number of failing rows exceeds the maximum specified
error code:0
vbscript runtime error
invalid use of null:  'Replace'

Here is a snippet from the code I am using:

'**********************************************************************
'  Visual Basic Transformation Script
'************************************************************************

'  Copy each source column to the destination column
Function Main()
      DTSDestination ("descriptivetextcatfull") = REPLACE  (DTSSource("descriptivetextcatfull"),vbCrLf,"")
      DTSDestination ("sku") = REPLACE  (DTSSource("sku"),vbCrLf,"")
      DTSDestination ("sortkeyword") = REPLACE  (DTSSource("sortkeyword"),vbCrLf,"")
      DTSDestination ("titlecatalog") = REPLACE  (DTSSource("titlecatalog"),vbCrLf,"")
      DTSDestination ("commentstart") = REPLACE  (DTSSource("commentstart"),vbCrLf,"")
      DTSDestination ("byline") = REPLACE  (DTSSource("byline"),vbCrLf,"")
                  Main = DTSTransformStat_OK
End Function


It runs with only 2 or 3 of the replace lines present, but errors out after that.  I thought perhaps it was related to some non-text columns, but after removing them, it still fails.  Have I interpreted your instructions incorrectly?

thanks,
Dave

I have got the vb trans script to run by inserting:

elseif not isnull( DTSSource ( "sku")) then
      DTSDestination ("sku") = REPLACE  (DTSSource("sku"),vbCr,"")

in front of each column REPLACE command line  The runtime error goes away.

However, I still have all these extra lines in the .txt output file. I believe they might be manual line breaks from Word or access, rather than carriage returns.  Is there someway to change the code above to eliminate manual line breaks?

vblf = linefeed
vbcr = carriage return
vbcrlf = carriage return and linefeed

you need to probably add something like this

function parseme(strtext)
 'remove carriage returns
      parseme = Replace(parseme, vbCr, "")
 'remove line feeds
      parseme = Replace(parseme, vblf, "")
end function

Weellio:

Where would I add this?  I tried it with the cr, crlf, and lf, and none worked.  Would the code look like:

function parseme(strtext)
 'remove carriage returns
      parseme = Replace(parseme, vbCr, "")
 'remove line feeds
      parseme = Replace(parseme, vblf, "")
end function
'fnr.vbs removes all carriage returns
Const ForReading = 1, ForWriting = 2
strFileName = Wscript.Arguments(0)
strnewtext = ""   'change this if you want to replace the carriage return with something particulay
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFileName, ForReading)
 
strText = objFile.ReadAll
objFile.Close
strNewText = Replace(strText, vbcrlf, strnewtext)
 
Set objFile = objFSO.OpenTextFile(strFileName, ForWriting)
objFile.WriteLine strNewText
objFile.Close

oh i was thinkging you would add it to your transformation script
e.g.

DTSDestination ("descriptivetextcatfull") = parseme(DTSSource("descriptivetextcatfull")


if you want to use the VBS file,.. let's first determine if you want any carriage returns, linefeeds or what not,... can you provide an example of what you have and then what you want it to look like and i will make a script to acomplish this.

Weellio:

I would prefer to have it part of the transformation script.  I am not convinced that it is truly a CR or LF however.  Let me give more detail:

The dts job is exporting the Access table columns into a pipe delimited file.  When I open the .txt file (in wordpad), I can see in a couple of cases 100's of blank lines occurring in the middle of the data in a text column ,for example:

SKU|Keyword|Comment|Artist|DescriptiveTextCatFull|Status|Publisher
sku1|product1|1st edition|By Joe Bloe|Product1 description|Out of print|Publ1
sku2|product2|1st edition|By Joe Bloe|Product2 description goes


here and is interrupted by these blank lines|Out of print|Publ2
sku3|product3|1st edition|By Joe Bloe|Product3 description|Out of print|Publ3
sku4|product3|1st edition|By Joe Bloe|Product4 description|Out of print|Publ4

Is there some way to identify exactly what kind of breaks these are?

Thanks,
Dave

does it always happen in the 'description' lines?

see what this does for you

Dim source, target
source = "myfile.txt" 'your source file
target = "myfile2.txt" 'results file
 
Dim fso, f, f2
dim x, y
Const ForReading = 1, ForWriting = 2
 
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(source) Then
   Dim inputLine, outputLine
   Set f = fso.OpenTextFile(source, ForReading, False)
   Set f2 = fso.OpenTextFile(target, ForWriting, True)
   While Not f.AtEndOfStream
      inputLine = f.ReadLine
			outputline = parseme(inputLine)
      f2.Write outputline
   Wend
   f.Close
   f2.Close
   Set f2 = Nothing
   Set f = Nothing
   MsgBox "Done"
Else
   MsgBox source, vbOKOnly, "Source File Not Found"
End If
Set fso = Nothing
 
function parseme(strtext)
'remove carriage returns
	parseme = Replace(parseme, vbCr, " ")
'remove line feeds
	parseme = Replace(parseme, vblf, " ")
'remove the combination of the two
	parseme = Replace(parseme, vbcrlf, " ")
end function

Select allOpen in new window

Weellio,

Did you mean for that script to run at he command prompt (ala, fnr2.vbs)?   If so, it returned nothing to the myfile2.txt file.

Also -- the problem is not limited to one single column.

you need to edit these files first

source = "myfile.txt" 'your source file
target = "myfile2.txt" 'results file

the source file is the txt file in question..
copy the script in the same directory as the access exported txt file and set source = to the filename

ooops i meant the output will look like this
**********************************************

xxxxx sku1|product1|1st edition|By Joe Bloe|Product1 description|Out of print|Publ1 xxxxx sku2|product2|1st edition|By Joe Bloe|Product2 description|Out of print|Publ2 xxxxx sku3|product3|1st edition|By Joe Bloe|Product3 description|Out of print|Publ3 xxxxx sku4|product3|1st edition|By Joe Bloe|Product4 description|Out of print|Publ4

**********************************************

barleyfreak,
could you please show us, how does your transformation script look?


I think that REPLACE  (DTSSource("descriptivetextcatfull"),vbCrLf,"") should do the trick, but I might be wrong :)

Rimvis:

The code (with most of the columns cut out) is below. FYI, this does not however work to take out these mystery lines.

'**********************************************************************
'  Visual Basic Transformation Script
'************************************************************************

'  Copy each source column to the destination column
Function Main()
if not isnull( DTSSource ( "descriptivetextcatfull")) then
      DTSDestination ("descriptivetextcatfull") = REPLACE  (DTSSource("descriptivetextcatfull"),vbCrLf,"")
elseif not isnull( DTSSource ( "sku")) then
      DTSDestination ("sku") = REPLACE  (DTSSource("sku"),vbCrLf,"")
elseif not isnull( DTSSource ( "sortkeyword")) then
      DTSDestination ("sortkeyword") = REPLACE  (DTSSource("sortkeyword"),vbCrLf,"")
end if      
Main = DTSTransformStat_OK
End Function

Weelio at 12.05.2007 at 07:50PM PST

I did change the"myfile.txt" etc. to the correct source/destination prior to running as a command prompt vbs file.  However, the resultant file was empty.
      

Weellio:

Thank you!  That did it.  If I run the 1st script to eliminate the vbcrlf's then the 2nd to replace the xxxxx with a vbcr, it works like a charm!

cheers,
dave

sweet :)