Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

stripping carriage returns from .txt file

Posted on 2007-12-04
23
Medium Priority
?
4,339 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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
 

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 2000 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

715 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