Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4340
  • Last Modified:

stripping carriage returns from .txt file

I have a DTS job that creates a .txt file out of an Access table.  The Access table has a number of carriage returns that need to be stripped before the .txt file can be used correctly.  Instead of stripping the Access table fields, I am looking to find a way to automate the replacement of these carriage returns on the .txt file.  I have seen a bunch of dos to unix instructions out there, but I don't have (or know) unix and I want to automate this on a WINXP or WIN2000 server machine.

Is there a way to automate the stripping of carriage returns in a .txt file, say from teh command prompt & a batch file, or when running the DTS job that creates the .txt file?

Thanks,
Dave
0
barleyfreak
Asked:
barleyfreak
  • 10
  • 9
  • 2
  • +1
1 Solution
 
knightEknightCommented:
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?
0
 
weellioCommented:
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

Open in new window

0
 
RimvisCommented:
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

0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
barleyfreakAuthor Commented:
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
0
 
knightEknightCommented:
change the  vbCrLf  to  vbCr  
0
 
barleyfreakAuthor Commented:
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
0
 
barleyfreakAuthor Commented:
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?
0
 
weellioCommented:
vblf = linefeed
vbcr = carriage return
vbcrlf = carriage return and linefeed
0
 
weellioCommented:
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


0
 
barleyfreakAuthor Commented:
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
0
 
weellioCommented:
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.
0
 
barleyfreakAuthor Commented:
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
0
 
weellioCommented:
does it always happen in the 'description' lines?
0
 
weellioCommented:
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

Open in new window

0
 
barleyfreakAuthor Commented:
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.

0
 
weellioCommented:
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
0
 
weellioCommented:
this will remove all linefeeds and carriage returns, which may not be what you want...
my suggestion would be to edit the dts script to have each line start with
xxxxx

so the output will look like the following
**********************************************

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

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

this way the script can remove all the linefeeds and then once they are all removed you can replace the xxxxx with linefeeds to make it all look normal

the optimal solution would actually be editing the dts export or setting up a stored procedure to get rid of the linefeeds prior to export.

similar to this situation
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_20182000.html
0
 
weellioCommented:
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

**********************************************
0
 
RimvisCommented:
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 :)
0
 
barleyfreakAuthor Commented:
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
0
 
barleyfreakAuthor Commented:
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.
      
0
 
barleyfreakAuthor Commented:
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
0
 
weellioCommented:
sweet :)
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 10
  • 9
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now