Strip CRLF from txt file using existing query

art_r
art_r used Ask the Experts™
on
matthewspatrick had written this code for me to clean up some CRLF's in an import file we have which works very well. Found that we also just need to delete the last CRLF from our file as that is still causing our DTS import to fail as it tries to import the next row which is blank.

Looking for a solution to add to this to just strip the last CRLF from our TXT file import.


Dim fso, tsIn, tsOut, TheLine, arr

Set fso = CreateObject("Scripting.FileSystemObject")
Set tsIn = fso.OpenTextFile("c:\EE-Sample.txt")
Set tsOut = fso.CreateTextFile("c:\Corrected.txt", True)

Do Until tsIn.AtEndOfStream
      TheLine = tsIn.ReadLine
      arr = Split(TheLine, "^")
      If TheLine <> "" Then
            If UBound(arr) <> 15 Then TheLine = TheLine & " " & tsIn.ReadLine
            tsOut.WriteLine TheLine
      End If
Loop

tsIn.Close
tsOut.Close

Set tsIn = Nothing
Set tsOut = Nothing
Set fso = Nothing

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012

Commented:
Why not just change the row delimiter from CrLf to CrLfCrLf, that way you do not have to strip it off?
Top Expert 2012

Commented:
That is assuming that you had two CrLf's at the end of the row.  But on second thoughts I see from the sample that is not the case at least every time.  It would appear that whenever there is an "EOL" at the end the next line should be considered part of the same row and without the final CrLf.  In other words, instead of this:
PART_NO^DESC^SUBST^DIST_1^RETAIL_1^DIST_2^RETAIL_2^AVAILABLE_QTY^BACK_ORDER^ON_ORDER_REPAIR^ON_ORDER_OTHER^STOCK_NOTE^PROD_CLASS^PROD_SUBCLASS^MAIN_SPARES^OTHER_SPARES
PN11112^SIDE BRACKET^^6.15^6.78^6.53^6.23^32^^^^^ETC^00^MISC-2^
PN11113^Adapter^PN21113^55^66^61.6^73.92^0^^^^EOL
Adapter^45^08^ADAPTOR^

PN11114^80GB - 4200RPM^^225^270^252^302.4^13^^^^^HDD^00^HDD^
PN11115^Battery^LIMITED^105.01^124.01^116.41^137.69^0^^^^EOL
Battery^45^06^BATTERY-M^

PN1116^PCB SET^^950^1120^1052^1242.4^77^^^^^PCB^00^PCB-SET^
PN1117^ADAPTER FFC^^27.93^23.52^22.08^26.1^89^^^^^AVD^ACC^^ACCESSORIES
PN1118^PANEL AUDIO^^32.26^38.71^36.13^43.36^0^^^^^AVD^CAB^^CABINET

It should be:

PART_NO^DESC^SUBST^DIST_1^RETAIL_1^DIST_2^RETAIL_2^AVAILABLE_QTY^BACK_ORDER^ON_ORDER_REPAIR^ON_ORDER_OTHER^STOCK_NOTE^PROD_CLASS^PROD_SUBCLASS^MAIN_SPARES^OTHER_SPARES
PN11112^SIDE BRACKET^^6.15^6.78^6.53^6.23^32^^^^^ETC^00^MISC-2^
PN11113^Adapter^PN21113^55^66^61.6^73.92^0^^^^EOLAdapter^45^08^ADAPTOR^
PN11114^80GB - 4200RPM^^225^270^252^302.4^13^^^^^HDD^00^HDD^
PN11115^Battery^LIMITED^105.01^124.01^116.41^137.69^0^^^^EOLBattery^45^06^BATTERY-M^
PN1116^PCB SET^^950^1120^1052^1242.4^77^^^^^PCB^00^PCB-SET^
PN1117^ADAPTER FFC^^27.93^23.52^22.08^26.1^89^^^^^AVD^ACC^^ACCESSORIES
PN1118^PANEL AUDIO^^32.26^38.71^36.13^43.36^0^^^^^AVD^CAB^^CABINET

Is this correct?

Open in new window

Author

Commented:
Hi,
Yes that is correct and the code I got in my previous question already fixes that fine. My problem is now that the last CRLF on the last record is still stopping our import from running. Sorry probably didn't write that well the first time.

So I just need a modification to the code I already have to look at the last record on the page and remove that CRLF so our import won't try to read the next record as there is nothing there but a blank line.

e.g.
300: PN11115^Battery^LIMITED^105.01^124.01^116.41^137.69^0^^^^EOLBattery^45^06^BATTERY-M^
301: PN1116^PCB SET^^950^1120^1052^1242.4^77^^^^^PCB^00^PCB-SET^
302: PN1117^ADAPTER FFC^^27.93^23.52^22.08^26.1^89^^^^^AVD^ACC^^ACCESSORIES
303: PN1118^PANEL AUDIO^^32.26^38.71^36.13^43.36^0^^^^^AVD^CAB^^CABINET
304:

So in the example say we are at the bottom of the file, line 303 still has a CRLF on it which is technically right but then it still leaves the last line 304 which our existing DTS still tries to import and fails as there is no data. So looking at this again, I either need to just add in something to delete the last CRLF or the last line I guess which will both fix the issue.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2012

Commented:
>>which our existing DTS still tries to import and fails as there is no data<<
So add some code in your Data Transformation ActiveX script to skip it.  As in:
If Len(DTSDestination("NameOfYourFirstColumn")) > 0 Then
     ' All your Transformations go here


     Main = DTSTransformStat_OK
Else
     Main = DTSTransformStat_SkipRow
End If

Author

Commented:
Is there a way to just incorporate that into the script I already have? We're actually preferring to run that separately as a VBS file and not mess with our DTS job.
Top Expert 2012

Commented:
I just tested it and the script as posted produces the right results:
This is the original:
PART_NO^DESC^SUBST^DIST_1^RETAIL_1^DIST_2^RETAIL_2^AVAILABLE_QTY^BACK_ORDER^ON_ORDER_REPAIR^ON_ORDER_OTHER^STOCK_NOTE^PROD_CLASS^PROD_SUBCLASS^MAIN_SPARES^OTHER_SPARES
PN11112^SIDE BRACKET^^6.15^6.78^6.53^6.23^32^^^^^ETC^00^MISC-2^
PN11113^Adapter^PN21113^55^66^61.6^73.92^0^^^^EOL
Adapter^45^08^ADAPTOR^

PN11114^80GB - 4200RPM^^225^270^252^302.4^13^^^^^HDD^00^HDD^
PN11115^Battery^LIMITED^105.01^124.01^116.41^137.69^0^^^^EOL
Battery^45^06^BATTERY-M^

PN1116^PCB SET^^950^1120^1052^1242.4^77^^^^^PCB^00^PCB-SET^
PN1117^ADAPTER FFC^^27.93^23.52^22.08^26.1^89^^^^^AVD^ACC^^ACCESSORIES
PN1118^PANEL AUDIO^^32.26^38.71^36.13^43.36^0^^^^^AVD^CAB^^CABINET

And this is the result of running the script:
PART_NO^DESC^SUBST^DIST_1^RETAIL_1^DIST_2^RETAIL_2^AVAILABLE_QTY^BACK_ORDER^ON_ORDER_REPAIR^ON_ORDER_OTHER^STOCK_NOTE^PROD_CLASS^PROD_SUBCLASS^MAIN_SPARES^OTHER_SPARES
PN11112^SIDE BRACKET^^6.15^6.78^6.53^6.23^32^^^^^ETC^00^MISC-2^
PN11113^Adapter^PN21113^55^66^61.6^73.92^0^^^^EOL Adapter^45^08^ADAPTOR^
PN11114^80GB - 4200RPM^^225^270^252^302.4^13^^^^^HDD^00^HDD^
PN11115^Battery^LIMITED^105.01^124.01^116.41^137.69^0^^^^EOL Battery^45^06^BATTERY-M^
PN1116^PCB SET^^950^1120^1052^1242.4^77^^^^^PCB^00^PCB-SET^
PN1117^ADAPTER FFC^^27.93^23.52^22.08^26.1^89^^^^^AVD^ACC^^ACCESSORIES
PN1118^PANEL AUDIO^^32.26^38.71^36.13^43.36^0^^^^^AVD^CAB^^CABINET

Open in new window

Top Expert 2012
Commented:
I have no idea why you want to do this as it is simply not necessary, but if you want to remove the CrLf on the last line, then this is how to do it:
Dim fso, tsIn, tsOut, TheLine, arr

Set fso = CreateObject("Scripting.FileSystemObject")
Set tsIn = fso.OpenTextFile("c:\EE-Sample.txt")
Set tsOut = fso.CreateTextFile("c:\Corrected.txt", True)

Do Until tsIn.AtEndOfStream
      TheLine = tsIn.ReadLine
      arr = Split(TheLine, "^")
      If TheLine <> "" Then
            If UBound(arr) <> 15 Then 
				TheLine = TheLine & " " & tsIn.ReadLine
			End if
			If Not tsIn.AtEndOfStream Then
				tsOut.WriteLine TheLine
			Else
				tsOut.Write TheLine
			End If			
      End If
Loop

tsIn.Close
tsOut.Close

Set tsIn = Nothing
Set tsOut = Nothing
Set fso = Nothing

Open in new window

Author

Commented:
That works great.

As for why this way, just means I can fix up the file and not have to get the DB guy in to alter the DTS. When we have him for some other tasks I may get him to put it directly into the DTS but for now it saves us manually fixing the files.

Thanks for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial