Solved

stripping carriage returns from .txt file

Posted on 2007-12-04
23
4,321 Views
Last Modified: 2012-08-13
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
Comment
Question by:barleyfreak
  • 10
  • 9
  • 2
  • +1
23 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 20408123
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
 
LVL 19

Expert Comment

by:weellio
ID: 20408144
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
 
LVL 19

Expert Comment

by:Rimvis
ID: 20409884
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
 

Author Comment

by:barleyfreak
ID: 20412749
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 20413048
change the  vbCrLf  to  vbCr  
0
 

Author Comment

by:barleyfreak
ID: 20414096
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
 

Author Comment

by:barleyfreak
ID: 20415007
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
 
LVL 19

Expert Comment

by:weellio
ID: 20415792
vblf = linefeed
vbcr = carriage return
vbcrlf = carriage return and linefeed
0
 
LVL 19

Expert Comment

by:weellio
ID: 20415849
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
 

Author Comment

by:barleyfreak
ID: 20415880
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
 
LVL 19

Expert Comment

by:weellio
ID: 20416000
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:barleyfreak
ID: 20416121
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
 
LVL 19

Expert Comment

by:weellio
ID: 20416362
does it always happen in the 'description' lines?
0
 
LVL 19

Expert Comment

by:weellio
ID: 20416461
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
 

Author Comment

by:barleyfreak
ID: 20416752
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
 
LVL 19

Expert Comment

by:weellio
ID: 20417254
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
 
LVL 19

Accepted Solution

by:
weellio earned 500 total points
ID: 20417332
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
 
LVL 19

Expert Comment

by:weellio
ID: 20417339
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
 
LVL 19

Expert Comment

by:Rimvis
ID: 20417838
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
 

Author Comment

by:barleyfreak
ID: 20421364
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
 

Author Comment

by:barleyfreak
ID: 20421393
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
 

Author Comment

by:barleyfreak
ID: 20423767
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
 
LVL 19

Expert Comment

by:weellio
ID: 20424403
sweet :)
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I have published numerous articles here at Experts Exchange that present programs/scripts written in a language called AutoHotkey. Each of those articles has a brief paragraph describing where to download the product and how to install it. I have al…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now