|
code
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Removing too many hyphensmodified by a VB6 program in order to be imported by a proprietary application. As a part of the formatting process I am trying to remove hyphens (-) from part of a text file (these are from facility numbers) without removing them from the dates... There is an example of the text file below the code, which shows an example of facility numbers. ' Open the file, replace noncompatible text and save. Open InName For Input As #ff fileText = Input(LOF(ff), #ff) Close #ff fileText = Replace(fileText, "-", "") ‘THIS IS WHERE I REMOVE THE HYPHENS fileText = Replace(fileText, " - ", "") 'includes the possibilities of spaces around the hyphens fileText = Replace(fileText, " -", "") 'is there a way to start the Replace 10 spaces in past the date? fileText = Replace(fileText, "- ", "") Open InName For Output As #ff Print #ff, fileText Close #ff fileText = "" 'Clear the variable 'Open file to modify date/time from yyyy-mm-dd hh:mm:ss to mm-dd-yy Open InName For Input As #ff fileText = Input(LOF(ff), #ff) Close #ff fileBits = Split(fileText, ",") For i = 0 To UBound(fileBits) If i Mod 5 = 0 Then If bNotFirstRecord Then fileBits(i) = vbNewLine & Format$(Mid$(Replace$(fileBits(i), vbNewLine, ""), 1, 8), "mm-dd-yy") Else fileBits(i) = Format$(Mid$(Replace$(fileBits(i), vbNewLine, ""), 1, 8), "mm-dd-yy") bNotFirstRecord = True End If End If Next Part of Text File: 2009-04-22 00:00:00,"Ind Waste","1282-001","_BOD 5","<150" 2009-04-22 00:00:00,"Ind Waste","1282-002","_BOD 5","375" 2009-04-22 00:00:00,"Ind Waste","1282-003","_BOD 5","203" 2009-04-22 00:00:00,"Ind Waste","1282-004","_BOD 5","210" 2009-04-22 00:00:00,"Ind Waste","1282-005","_BOD 5","203" 2009-04-22 00:00:00,"Ind Waste","1282-006","_Ag","0.0077" 2009-04-24 14:15:20.687000000,"Ind Waste","1283-FAC","_BOD 5","345" 2009-04-27 14:17:22.653000000,"Ind Waste","1758-FAC","_TSS","4.0" 2009-04-27 14:17:22.653000000,"Ind Waste","1758-FAC","_pH1","8.1" 2009-04-27 14:17:22.653000000,"Ind Waste","1758 - FAC","_BOD 5","<200" 2009-04-27 14:17:22.653000000,"Ind Waste","1758-001","_TSS","31" Thanks in advance, Al
Show quote
Hide quote
"Al" <A*@discussions.microsoft.com> wrote in message Use Split() to split into lines, and ignore the first 10 characters of each news:F86EFAC1-92AE-49A9-9235-5EA36AC94251@microsoft.com... >I am exporting data daily from SQL Server into a text file that needs to be > modified by a VB6 program in order to be imported by a proprietary > application. As a part of the formatting process I am trying to remove > hyphens (-) from part of a text file (these are from facility numbers) > without removing them from the dates... There is an example of the text > file > below the code, which shows an example of facility numbers. > > ' Open the file, replace noncompatible text and save. > > Open InName For Input As #ff > > fileText = Input(LOF(ff), #ff) > > Close #ff > > fileText = Replace(fileText, "-", "") 'THIS IS WHERE I REMOVE THE > HYPHENS > > fileText = Replace(fileText, " - ", "") 'includes the possibilities > of spaces around the hyphens > > fileText = Replace(fileText, " -", "") 'is there a way to start > the > Replace 10 spaces in past the date? > > fileText = Replace(fileText, "- ", "") > > Open InName For Output As #ff > > Print #ff, fileText > > Close #ff > > fileText = "" 'Clear the variable > > 'Open file to modify date/time from yyyy-mm-dd hh:mm:ss to mm-dd-yy > > Open InName For Input As #ff > > fileText = Input(LOF(ff), #ff) > > Close #ff > > fileBits = Split(fileText, ",") > > For i = 0 To UBound(fileBits) > > If i Mod 5 = 0 Then > > If bNotFirstRecord Then > > fileBits(i) = vbNewLine & Format$(Mid$(Replace$(fileBits(i), > vbNewLine, ""), 1, 8), "mm-dd-yy") > > Else > > fileBits(i) = Format$(Mid$(Replace$(fileBits(i), vbNewLine, ""), 1, > 8), "mm-dd-yy") > > bNotFirstRecord = True > > End If > > End If > > Next > > > Part of Text File: > 2009-04-22 00:00:00,"Ind Waste","1282-001","_BOD 5","<150" > 2009-04-22 00:00:00,"Ind Waste","1282-002","_BOD 5","375" > 2009-04-22 00:00:00,"Ind Waste","1282-003","_BOD 5","203" > 2009-04-22 00:00:00,"Ind Waste","1282-004","_BOD 5","210" > 2009-04-22 00:00:00,"Ind Waste","1282-005","_BOD 5","203" > 2009-04-22 00:00:00,"Ind Waste","1282-006","_Ag","0.0077" > 2009-04-24 14:15:20.687000000,"Ind Waste","1283-FAC","_BOD 5","345" > 2009-04-27 14:17:22.653000000,"Ind Waste","1758-FAC","_TSS","4.0" > 2009-04-27 14:17:22.653000000,"Ind Waste","1758-FAC","_pH1","8.1" > 2009-04-27 14:17:22.653000000,"Ind Waste","1758 - FAC","_BOD 5","<200" > 2009-04-27 14:17:22.653000000,"Ind Waste","1758-001","_TSS","31" > > Thanks in advance, > Al > line. In the example below, I used a large buffer and used Mid statement because this method is faster than string concatenation. Dim i As Long Dim sLines Dim sOut As String Dim sOutIndex As Long Dim sTemp As String Dim t As Single t = Timer ' Allocate buffer sOut = Space(Len(fileText) + 100) sOutIndex = 1 sLines = Split(fileText, vbCrLf) For i = LBound(sLines) To UBound(sLines) sTemp = Replace$(sLines(i), "-", "", 10) & vbCrLf Mid$(sOut, sOutIndex) = sTemp sOutIndex = sOutIndex + Len(sTemp) Next ' Remove extra spaces sOut = Trim$(sOut) Debug.Print "Time taken " & Timer - t Here is another method without Split(): Dim i As Long Dim CharCode As Long Dim sOut As String Dim sOutIndex As Long Dim t As Single t = Timer ' Allocate buffer sOut = Space(Len(fileText) + 100) sOutIndex = 1 For i = 1 To Len(fileText) CharCode = Asc(Mid$(fileText, i, 1)) If CharCode = 10 Then ' 10 = LF ' Skip the first 10 chars in each line i = i + 11 If i > Len(fileText) then Exit For End If End If If CharCode <> 45 Then ' 45 = Hyphen Mid$(sOut, sOutIndex) = Chr$(CharCode) sOutIndex = sOutIndex + 1 End If Next ' Remove extra spaces sOut = Trim$(sOut) Debug.Print "Time taken " & Timer - t "Larry Serflaten" <serfla***@usinternet.com> wrote in message If you mean me, I always include the first post in full because the OP may news:%230%23AiGd%23JHA.5064@TK2MSFTNGP03.phx.gbl... > Please trim your posts.... not be visible in some servers, especially from "discussions.microsoft.com". So others know what I am responding to. I always trim subsequent posts. "Al" <A*@discussions.microsoft.com> wrote Why open the file twice? How big is the file? Can you be sure it will always> I am exporting data daily from SQL Server into a text file that needs to be > modified by a VB6 program in order to be imported by a proprietary > application. As a part of the formatting process I am trying to remove > hyphens (-) from part of a text file (these are from facility numbers) > without removing them from the dates... There is an example of the text file > below the code, which shows an example of facility numbers. <...> > Thanks in advance, fit in memory? (As your fileText variable requires). If it were me, and I knew the file would always be less than a few megabytes, I'd read the file like you do and then split it up into lines (Split on vbCrLf). Then, looping throught the lines, I'd split the lines up into fields (Split on ",") and then operate on both the date and facilty number fields. Some psudo code to help explain: Sub DoIt() file = freefile Open filename for input as file fileText = Input(LOF(file), file) Close file fileData = Split(fileText, vbCrLf) For idx = 0 to UBound(fileData) fileData(idx) = FixLine(fileData(idx)) Next Open filename for output as file Print #file, Join(fileData, vbCrLf) Close file End Sub Function FixLine(Text) As String data = Split(Text, ",") data(0) = ConvertToDate(data(0)) data(2) = RemoveHyphen(data(2)) FixLine = Join(data, ",") End Function Function ConvertToDate(Text) as String ConvertToDate = Format$(CDate(text), "mm-dd-yy") End function Function RemoveHyphen(Text) As String data = Split(text, "-") For idx = 0 to UBound(data) data(idx) = Trim$(data(idx)) Next RemoveHyphen = Trim$(Join(data, " ")) End function LFS Thanks Larry, I converted my code over to resemble your example; however, I
am getting a Run-time error 13 " "Type Mismatch" when I get to the following line in your code: ConvertToDate = Format$(CDate(Text), "mm-dd-yy") When I hover the mouse over the "Text" variable I see the date/time value of: "2009-04-23 14:44:32.437000000" Any ideas? Thanks, Al Show quoteHide quote "Larry Serflaten" wrote: > Function ConvertToDate(Text) as String > ConvertToDate = Format$(CDate(text), "mm-dd-yy") > End function "Al" <A*@discussions.microsoft.com> wrote I don't know where you get your time value from, but the decimal part is> Thanks Larry, I converted my code over to resemble your example; however, I > am getting a Run-time error 13 " "Type Mismatch" when I get to the following > line in your code: > > ConvertToDate = Format$(CDate(Text), "mm-dd-yy") > > When I hover the mouse over the "Text" variable I see the date/time value of: > > "2009-04-23 14:44:32.437000000" > > Any ideas? not supported. Remove that part of the text before using Format. Something like: Function ConvertToDate(Text) As String ConvertToDate = Format$(Left$(Text, InStr(Text, " ")), "mm-dd-yy") End Function LFS I will have to give it a try on Monday... budget cuts gave us today off
without pay and tomorrow is a holiday. Thank you for all your help I truly appreciate it! Al Thanks Larry, that fixed the date problem. However, now I see that the lines
are now joined together without line breaks in the text file output. Function FixLine(Text) As String data = Split(Text, ",") data(0) = ConvertToDate(data(0)) data(2) = RemoveHyphen(data(2)) FixLine = Join(data, ",") ‘I Believe This Is Where The Problem Is… End Function This is what the output looks like: 04-14-09,Ind Waste,2924 FAC,_Ag,<0.0020,04-14-09,Ind Waste,2924 FAC,_Pb,<0.010,04-14-09,Ind Waste,2924 FAC,_Se,<0.020,04-14-09,Ind Waste,2924 FAC,_Zn,0.058,04-13-09,Ind Waste,6935 FAC,_Ag,<0.0020,04-13-09,Ind Waste,6935 FAC,_Pb,<0.010,04-13-09,Ind Waste,6935 FAC,_Se,<0.020,04-13-09,Ind Waste,6935 Thank you for your help, Al "Al" <A*@discussions.microsoft.com> wrote IS this still what your using?> Thanks Larry, that fixed the date problem. However, now I see that the lines > are now joined together without line breaks in the text file output. fileData = Split(fileText, vbCrLf) For idx = 0 to UBound(fileData) fileData(idx) = FixLine(fileData(idx)) Next Open filename for output as file Print #file, Join(fileData, vbCrLf) Close file End Sub Try this (note I moved the vbCrLf to the first loop): fileData = Split(fileText, vbCrLf) For idx = 0 to UBound(fileData) fileData(idx) = FixLine(fileData(idx)) & vbCrlf Next Open filename for output as file Print #file, Join(fileData, "") Close file End Sub LFS Thanks again Larry, yes, I was using your code and that fixed the line feed
problem. I tried to remove the commas from the beginning of the lines myself but I haven't had much luck yet. Also, I noticed that I still need to remove the spaces from "2924 FAC" part of the text, ie, "2924FAC" The output now looks like this: 04-14-09,Ind Waste,2924 FAC,_Ag,<0.0020 ,04-14-09,Ind Waste,2924 FAC,_Pb,<0.010 ,04-14-09,Ind Waste,2924 FAC,_Se,<0.020 ,04-14-09,Ind Waste,2924 FAC,_Zn,0.058 ,04-13-09,Ind Waste,6935 FAC,_Ag,<0.0020 ,04-13-09,Ind Waste,6935 FAC,_Pb,<0.010 I will continue working on it too and truly appreciate your help with this. You have been a tremendous help to me. Thank you very much! Al
Show quote
Hide quote
"Al" <A*@discussions.microsoft.com> wrote in message Uhh, the first Replace will remove ALL hyphens from the string, so the news:F86EFAC1-92AE-49A9-9235-5EA36AC94251@microsoft.com... > fileText = Replace(fileText, "-", "") 'THIS IS WHERE I REMOVE THE > HYPHENS > > fileText = Replace(fileText, " - ", "") 'includes the possibilities > of spaces around the hyphens > > fileText = Replace(fileText, " -", "") 'is there a way to start > the > Replace 10 spaces in past the date? > > fileText = Replace(fileText, "- ", "") following three statements will do NOTHING.  
Other interesting topics
|
|||||||||||||||||||||||
