| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to read text file; find string; delete same before import
2003
Need a VBA function to read text file; find string; delete same before import. The function should find all instances (there can be many) of: SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| the last line of the file REPORT COMPLETE In short, just the data please SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| REPORT COMPLETE TIA any and all help greatly appreciated |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
This is very simple. I don't know what you are refering to when you mean
FIND STRING since everything is a string in the TEXT file. It is also important to know wher each line ends. Not sure if your code exactly represents the text file. Open the file with Notepad and make sure WORD WRAP (Format menu) is NOT checked. Then lok at text file and let me know where the end of each line is located. I have two methods of doing this task. I can import the file and then make the changes in excel to delete the data you don't want. Or make the changes before I put the data into spreadsheet. If you want to try a quick experiment try to impor the dat manually. From worksheet menue 1) Data - Import External Data - Import Data 2) Press Delimited - Next 3) Uncheck everything except Other and put the | in the box next to Other 4) Press finish and select where you want the data to go. You can also turn on Record Macro before you do these operations to get a macro. " wrote: 2003 Need a VBA function to read text file; find string; delete same before import. The function should find all instances (there can be many) of: SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| the last line of the file REPORT COMPLETE In short, just the data please SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| REPORT COMPLETE TIA any and all help greatly appreciated |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
Thank you for taking your time and for your knowledge.
I guess I should have been more clear. The import itself is not the issue. What I was looking for is a function that would: 1) read an entire text file line by line 2) find each and every line with an instance of: SELECT TO_CHAR(NET (and/or) AMT|DOC_NUM|FIPC| (and/or) REPORT COMPLETE (and/or) 3) delete the entire line(s) 4) leaving only the pure data 5) save the file 6) (then import into Excel about which I do not need assistance) In short, I want the file to contain only: 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 EagleOne Joel wrote: This is very simple. I don't know what you are refering to when you mean FIND STRING since everything is a string in the TEXT file. It is also important to know wher each line ends. Not sure if your code exactly represents the text file. Open the file with Notepad and make sure WORD WRAP (Format menu) is NOT checked. Then lok at text file and let me know where the end of each line is located. I have two methods of doing this task. I can import the file and then make the changes in excel to delete the data you don't want. Or make the changes before I put the data into spreadsheet. If you want to try a quick experiment try to impor the dat manually. From worksheet menue 1) Data - Import External Data - Import Data 2) Press Delimited - Next 3) Uncheck everything except Other and put the | in the box next to Other 4) Press finish and select where you want the data to go. You can also turn on Record Macro before you do these operations to get a macro. " wrote: 2003 Need a VBA function to read text file; find string; delete same before import. The function should find all instances (there can be many) of: SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| the last line of the file REPORT COMPLETE In short, just the data please SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| REPORT COMPLETE TIA any and all help greatly appreciated |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
David, as usual, you win the day.
Your code can be modified by others too. Thanks for all. ps. Does the Close statement save the file also? Dave Peterson wrote: Option Explicit Sub testme01() Dim TextLine As String Dim KeepThisRecord As Boolean Dim iRecCount As Long Dim oRecCount As Long Dim myStrings As Variant Dim sCtr As Long 'just in case Close #1 Close #2 'my test files Open "c:\my documents\excel\book2.txt" For Input As #1 Open "c:\my documents\excel\book2.txt.out" For Output As #2 myStrings = Array("SELECT TO_CHAR(NET", _ "AMT|DOC_NUM|FIPC|", _ "REPORT COMPLETE") iRecCount = 0 oRecCount = 0 Do While Not EOF(1) Line Input #1, TextLine iRecCount = iRecCount + 1 KeepThisRecord = True For sCtr = LBound(myStrings) To UBound(myStrings) If UCase(TextLine) Like "*" & UCase(myStrings(sCtr)) & "*" Then 'skip it KeepThisRecord = False Exit For End If Next sCtr If KeepThisRecord = True Then Print #2, TextLine oRecCount = oRecCount + 1 End If Loop Close #1 Close #2 MsgBox "Recs In: " & iRecCount & vbLf & "Recs Out: " & oRecCount End Sub wrote: Thank you for taking your time and for your knowledge. I guess I should have been more clear. The import itself is not the issue. What I was looking for is a function that would: 1) read an entire text file line by line 2) find each and every line with an instance of: SELECT TO_CHAR(NET (and/or) AMT|DOC_NUM|FIPC| (and/or) REPORT COMPLETE (and/or) 3) delete the entire line(s) 4) leaving only the pure data 5) save the file 6) (then import into Excel about which I do not need assistance) In short, I want the file to contain only: 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 EagleOne Joel wrote: This is very simple. I don't know what you are refering to when you mean FIND STRING since everything is a string in the TEXT file. It is also important to know wher each line ends. Not sure if your code exactly represents the text file. Open the file with Notepad and make sure WORD WRAP (Format menu) is NOT checked. Then lok at text file and let me know where the end of each line is located. I have two methods of doing this task. I can import the file and then make the changes in excel to delete the data you don't want. Or make the changes before I put the data into spreadsheet. If you want to try a quick experiment try to impor the dat manually. From worksheet menue 1) Data - Import External Data - Import Data 2) Press Delimited - Next 3) Uncheck everything except Other and put the | in the box next to Other 4) Press finish and select where you want the data to go. You can also turn on Record Macro before you do these operations to get a macro. " wrote: 2003 Need a VBA function to read text file; find string; delete same before import. The function should find all instances (there can be many) of: SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| the last line of the file REPORT COMPLETE In short, just the data please SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| REPORT COMPLETE TIA any and all help greatly appreciated |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
|
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
Dave Peterson wrote:
[snip nice code] Looks like you have parsed a few greenbar reports in your day (^: |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
We also had some reports printed on white stock (11x17). These were called
"whitebar". It always made me laugh <vbg. smartin wrote: Dave Peterson wrote: [snip nice code] Looks like you have parsed a few greenbar reports in your day (^: -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
Your file is not too large to do with a macro in Excel (just change the path
and filename assignment to your own file's path and name)... Sub GetDataOnly() Dim FileNum As Long Dim FileName As String Dim TotalFile As String FileName = "c:\Path\To\File\MyFile.txt" FileNum = FreeFile Open FileName For Binary As #FileNum TotalFile = Space(LOF(FileNum)) Get #FileNum, , TotalFile Close #FileNum TotalFile = replace(TotalFile, "SELECT TO_CHAR(NET" & vbCrLf & _ "AMT|DOC_NUM|FIPC|" & vbCrLf, "") TotalFile = replace(TotalFile, "REPORT COMPLETE", "") FileNum = FreeFile Open FileName For Output As #FileNum Print #FileNum, TotalFile Close #FileNum End Sub Rick wrote in message ... Thank you for taking your time and for your knowledge. I guess I should have been more clear. The import itself is not the issue. What I was looking for is a function that would: 1) read an entire text file line by line 2) find each and every line with an instance of: SELECT TO_CHAR(NET (and/or) AMT|DOC_NUM|FIPC| (and/or) REPORT COMPLETE (and/or) 3) delete the entire line(s) 4) leaving only the pure data 5) save the file 6) (then import into Excel about which I do not need assistance) In short, I want the file to contain only: 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 EagleOne Joel wrote: This is very simple. I don't know what you are refering to when you mean FIND STRING since everything is a string in the TEXT file. It is also important to know wher each line ends. Not sure if your code exactly represents the text file. Open the file with Notepad and make sure WORD WRAP (Format menu) is NOT checked. Then lok at text file and let me know where the end of each line is located. I have two methods of doing this task. I can import the file and then make the changes in excel to delete the data you don't want. Or make the changes before I put the data into spreadsheet. If you want to try a quick experiment try to impor the dat manually. From worksheet menue 1) Data - Import External Data - Import Data 2) Press Delimited - Next 3) Uncheck everything except Other and put the | in the box next to Other 4) Press finish and select where you want the data to go. You can also turn on Record Macro before you do these operations to get a macro. " wrote: 2003 Need a VBA function to read text file; find string; delete same before import. The function should find all instances (there can be many) of: SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| the last line of the file REPORT COMPLETE In short, just the data please SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| REPORT COMPLETE TIA any and all help greatly appreciated |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
I remember having trouble with VBA's Replace for longgggggg strings--and since
the OP said that there were over 65k lines of data, I wonder if VBA's replace would work in this case. But regex.replace seems to work with large/huge files. Sub UpDateTxtFile2() Dim FSO As Object Dim RegEx As Object Dim myFile As Object Dim myContents As String Dim myInFileName As String Dim myOutFileName As String Dim myStrings As Variant Dim sCtr As Long myInFileName = "C:\my documents\excel\book2.txt" myOutFileName = "C:\my documents\excel\book2.txt.out" myStrings = Array("SELECT TO_CHAR(NET", _ "AMT|DOC_NUM|FIPC|", _ "REPORT COMPLETE") Set FSO = CreateObject("Scripting.FileSystemObject") Set myFile = FSO.OpenTextFile(myInFileName, 1, False) myContents = myFile.ReadAll myFile.Close Set RegEx = CreateObject("VBScript.RegExp") For sCtr = LBound(myStrings) To UBound(myStrings) With RegEx myStrings(sCtr) = Replace(myStrings(sCtr), "_", "\_") myStrings(sCtr) = Replace(myStrings(sCtr), "(", "\(") myStrings(sCtr) = Replace(myStrings(sCtr), "|", "\|") .Global = True .IgnoreCase = True .Pattern = myStrings(sCtr) & vbCrLf myContents = .Replace(myContents, "") End With Next sCtr Set myFile = FSO.CreateTextFile(myOutFileName) myFile.Write myContents myFile.Close End Sub Or using those 3 lines as one string: Option Explicit Sub UpDateTxtFile3() Dim FSO As Object Dim RegEx As Object Dim myFile As Object Dim myContents As String Dim myInFileName As String Dim myOutFileName As String Dim myString As String myInFileName = "C:\my documents\excel\book2.txt" myOutFileName = "C:\my documents\excel\book2.txt.out" myString = "SELECT TO_CHAR(NET" & vbCrLf _ & "AMT|DOC_NUM|FIPC|" & vbCrLf _ & "REPORT COMPLETE" & vbCrLf Set FSO = CreateObject("Scripting.FileSystemObject") Set myFile = FSO.OpenTextFile(myInFileName, 1, False) myContents = myFile.ReadAll myFile.Close Set RegEx = CreateObject("VBScript.RegExp") With RegEx myString = Replace(myString, "_", "\_") myString = Replace(myString, "(", "\(") myString = Replace(myString, "|", "\|") .Global = True .IgnoreCase = True .Pattern = myString myContents = .Replace(myContents, "") End With Set myFile = FSO.CreateTextFile(myOutFileName) myFile.Write myContents myFile.Close End Sub "Rick Rothstein (MVP - VB)" wrote: Your file is not too large to do with a macro in Excel (just change the path and filename assignment to your own file's path and name)... Sub GetDataOnly() Dim FileNum As Long Dim FileName As String Dim TotalFile As String FileName = "c:\Path\To\File\MyFile.txt" FileNum = FreeFile Open FileName For Binary As #FileNum TotalFile = Space(LOF(FileNum)) Get #FileNum, , TotalFile Close #FileNum TotalFile = replace(TotalFile, "SELECT TO_CHAR(NET" & vbCrLf & _ "AMT|DOC_NUM|FIPC|" & vbCrLf, "") TotalFile = replace(TotalFile, "REPORT COMPLETE", "") FileNum = FreeFile Open FileName For Output As #FileNum Print #FileNum, TotalFile Close #FileNum End Sub Rick wrote in message ... Thank you for taking your time and for your knowledge. I guess I should have been more clear. The import itself is not the issue. What I was looking for is a function that would: 1) read an entire text file line by line 2) find each and every line with an instance of: SELECT TO_CHAR(NET (and/or) AMT|DOC_NUM|FIPC| (and/or) REPORT COMPLETE (and/or) 3) delete the entire line(s) 4) leaving only the pure data 5) save the file 6) (then import into Excel about which I do not need assistance) In short, I want the file to contain only: 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 EagleOne Joel wrote: This is very simple. I don't know what you are refering to when you mean FIND STRING since everything is a string in the TEXT file. It is also important to know wher each line ends. Not sure if your code exactly represents the text file. Open the file with Notepad and make sure WORD WRAP (Format menu) is NOT checked. Then lok at text file and let me know where the end of each line is located. I have two methods of doing this task. I can import the file and then make the changes in excel to delete the data you don't want. Or make the changes before I put the data into spreadsheet. If you want to try a quick experiment try to impor the dat manually. From worksheet menue 1) Data - Import External Data - Import Data 2) Press Delimited - Next 3) Uncheck everything except Other and put the | in the box next to Other 4) Press finish and select where you want the data to go. You can also turn on Record Macro before you do these operations to get a macro. " wrote: 2003 Need a VBA function to read text file; find string; delete same before import. The function should find all instances (there can be many) of: SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| the last line of the file REPORT COMPLETE In short, just the data please SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| REPORT COMPLETE TIA any and all help greatly appreciated -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
I remember having trouble with VBA's Replace for longgggggg strings--and
since the OP said that there were over 65k lines of data, I wonder if VBA's replace would work in this case. Based on the sample data the OP posted, I estimate his data file is around 2 to 2.5 Megs in size... that should not be too large for the Replace function to be able to do its "thing" with. Maybe if the file were around 15 to 20 Megs or more, I would be reluctant to recommend the solution I posted, but I feel it will work comfortably with files up to 10 Megs without any problems. Rick |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
Rick, having solutions bith inside and outside is Excel excellent!
I looked up FreeFile and found it as a Function. Is FreeFile a function available by reference to the "typical" Excel and/or typical-install Visual Basic Libraries or should one grab the FreeFall function and place it in one's VBA module? "Rick Rothstein \(MVP - VB\)" wrote: Your file is not too large to do with a macro in Excel (just change the path and filename assignment to your own file's path and name)... Sub GetDataOnly() Dim FileNum As Long Dim FileName As String Dim TotalFile As String FileName = "c:\Path\To\File\MyFile.txt" FileNum = FreeFile Open FileName For Binary As #FileNum TotalFile = Space(LOF(FileNum)) Get #FileNum, , TotalFile Close #FileNum TotalFile = replace(TotalFile, "SELECT TO_CHAR(NET" & vbCrLf & _ "AMT|DOC_NUM|FIPC|" & vbCrLf, "") TotalFile = replace(TotalFile, "REPORT COMPLETE", "") FileNum = FreeFile Open FileName For Output As #FileNum Print #FileNum, TotalFile Close #FileNum End Sub Rick wrote in message .. . Thank you for taking your time and for your knowledge. I guess I should have been more clear. The import itself is not the issue. What I was looking for is a function that would: 1) read an entire text file line by line 2) find each and every line with an instance of: SELECT TO_CHAR(NET (and/or) AMT|DOC_NUM|FIPC| (and/or) REPORT COMPLETE (and/or) 3) delete the entire line(s) 4) leaving only the pure data 5) save the file 6) (then import into Excel about which I do not need assistance) In short, I want the file to contain only: 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 EagleOne Joel wrote: This is very simple. I don't know what you are refering to when you mean FIND STRING since everything is a string in the TEXT file. It is also important to know wher each line ends. Not sure if your code exactly represents the text file. Open the file with Notepad and make sure WORD WRAP (Format menu) is NOT checked. Then lok at text file and let me know where the end of each line is located. I have two methods of doing this task. I can import the file and then make the changes in excel to delete the data you don't want. Or make the changes before I put the data into spreadsheet. If you want to try a quick experiment try to impor the dat manually. From worksheet menue 1) Data - Import External Data - Import Data 2) Press Delimited - Next 3) Uncheck everything except Other and put the | in the box next to Other 4) Press finish and select where you want the data to go. You can also turn on Record Macro before you do these operations to get a macro. " wrote: 2003 Need a VBA function to read text file; find string; delete same before import. The function should find all instances (there can be many) of: SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| the last line of the file REPORT COMPLETE In short, just the data please SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| REPORT COMPLETE TIA any and all help greatly appreciated |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
Dave,
You must have had a lot of coffee last night! Green Bar! What a concept. BTW, if our boneheaded Green Bar-trained Main-frame IT gurus did their job 15 years ago, I would not have to deal with crap laced through the data file. I wish I could tell you how many MEGA millions were spent on the s/w, as updated ,which was written by the #1 big s/w company. I had no idea that you are a Regex person. Ron Rosenfeld (I think an MVP) who was/is excellent in RegEx who would miraculously show up when file/string issues would pop up. EagleOne wrote: Rick, having solutions bith inside and outside is Excel excellent! I looked up FreeFile and found it as a Function. Is FreeFile a function available by reference to the "typical" Excel and/or typical-install Visual Basic Libraries or should one grab the FreeFall function and place it in one's VBA module? "Rick Rothstein \(MVP - VB\)" wrote: Your file is not too large to do with a macro in Excel (just change the path and filename assignment to your own file's path and name)... Sub GetDataOnly() Dim FileNum As Long Dim FileName As String Dim TotalFile As String FileName = "c:\Path\To\File\MyFile.txt" FileNum = FreeFile Open FileName For Binary As #FileNum TotalFile = Space(LOF(FileNum)) Get #FileNum, , TotalFile Close #FileNum TotalFile = replace(TotalFile, "SELECT TO_CHAR(NET" & vbCrLf & _ "AMT|DOC_NUM|FIPC|" & vbCrLf, "") TotalFile = replace(TotalFile, "REPORT COMPLETE", "") FileNum = FreeFile Open FileName For Output As #FileNum Print #FileNum, TotalFile Close #FileNum End Sub Rick wrote in message . .. Thank you for taking your time and for your knowledge. I guess I should have been more clear. The import itself is not the issue. What I was looking for is a function that would: 1) read an entire text file line by line 2) find each and every line with an instance of: SELECT TO_CHAR(NET (and/or) AMT|DOC_NUM|FIPC| (and/or) REPORT COMPLETE (and/or) 3) delete the entire line(s) 4) leaving only the pure data 5) save the file 6) (then import into Excel about which I do not need assistance) In short, I want the file to contain only: 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 EagleOne Joel wrote: This is very simple. I don't know what you are refering to when you mean FIND STRING since everything is a string in the TEXT file. It is also important to know wher each line ends. Not sure if your code exactly represents the text file. Open the file with Notepad and make sure WORD WRAP (Format menu) is NOT checked. Then lok at text file and let me know where the end of each line is located. I have two methods of doing this task. I can import the file and then make the changes in excel to delete the data you don't want. Or make the changes before I put the data into spreadsheet. If you want to try a quick experiment try to impor the dat manually. From worksheet menue 1) Data - Import External Data - Import Data 2) Press Delimited - Next 3) Uncheck everything except Other and put the | in the box next to Other 4) Press finish and select where you want the data to go. You can also turn on Record Macro before you do these operations to get a macro. " wrote: 2003 Need a VBA function to read text file; find string; delete same before import. The function should find all instances (there can be many) of: SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| the last line of the file REPORT COMPLETE In short, just the data please SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| REPORT COMPLETE TIA any and all help greatly appreciated |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
FreeFile is a function built into VB, so you do not have to do anything
special to use it... it is automatically available. By the way, the code I posted will work, as posted, within Excel as a macro... if you have the stand-alone (compiled) Visual Basic program available (Version 6 only because of the use of the Replace function), then the subroutine I posted can be run from inside a VB6 program exactly as written and it will work in that environment too. Rick wrote in message ... Rick, having solutions bith inside and outside is Excel excellent! I looked up FreeFile and found it as a Function. Is FreeFile a function available by reference to the "typical" Excel and/or typical-install Visual Basic Libraries or should one grab the FreeFall function and place it in one's VBA module? "Rick Rothstein \(MVP - VB\)" wrote: Your file is not too large to do with a macro in Excel (just change the path and filename assignment to your own file's path and name)... Sub GetDataOnly() Dim FileNum As Long Dim FileName As String Dim TotalFile As String FileName = "c:\Path\To\File\MyFile.txt" FileNum = FreeFile Open FileName For Binary As #FileNum TotalFile = Space(LOF(FileNum)) Get #FileNum, , TotalFile Close #FileNum TotalFile = replace(TotalFile, "SELECT TO_CHAR(NET" & vbCrLf & _ "AMT|DOC_NUM|FIPC|" & vbCrLf, "") TotalFile = replace(TotalFile, "REPORT COMPLETE", "") FileNum = FreeFile Open FileName For Output As #FileNum Print #FileNum, TotalFile Close #FileNum End Sub Rick wrote in message . .. Thank you for taking your time and for your knowledge. I guess I should have been more clear. The import itself is not the issue. What I was looking for is a function that would: 1) read an entire text file line by line 2) find each and every line with an instance of: SELECT TO_CHAR(NET (and/or) AMT|DOC_NUM|FIPC| (and/or) REPORT COMPLETE (and/or) 3) delete the entire line(s) 4) leaving only the pure data 5) save the file 6) (then import into Excel about which I do not need assistance) In short, I want the file to contain only: 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 EagleOne Joel wrote: This is very simple. I don't know what you are refering to when you mean FIND STRING since everything is a string in the TEXT file. It is also important to know wher each line ends. Not sure if your code exactly represents the text file. Open the file with Notepad and make sure WORD WRAP (Format menu) is NOT checked. Then lok at text file and let me know where the end of each line is located. I have two methods of doing this task. I can import the file and then make the changes in excel to delete the data you don't want. Or make the changes before I put the data into spreadsheet. If you want to try a quick experiment try to impor the dat manually. From worksheet menue 1) Data - Import External Data - Import Data 2) Press Delimited - Next 3) Uncheck everything except Other and put the | in the box next to Other 4) Press finish and select where you want the data to go. You can also turn on Record Macro before you do these operations to get a macro. " wrote: 2003 Need a VBA function to read text file; find string; delete same before import. The function should find all instances (there can be many) of: SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| the last line of the file REPORT COMPLETE In short, just the data please SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| REPORT COMPLETE TIA any and all help greatly appreciated |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
On Fri, 01 Aug 2008 20:36:31 -0400,
wrote: The import itself is not the issue. What I was looking for is a function that would: 1) read an entire text file line by line 2) find each and every line with an instance of: SELECT TO_CHAR(NET (and/or) AMT|DOC_NUM|FIPC| (and/or) REPORT COMPLETE (and/or) 3) delete the entire line(s) 4) leaving only the pure data 5) save the file 6) (then import into Excel about which I do not need assistance) Sub DoStuff() Dim sFileToImport As String sFileToImport = GetCleanFileName("C:\Import.txt") 'do import stuff here End Sub Function GetCleanFileName(sInputFile As String) As String Dim sCleanFileName As String Dim lFnumIn As Long, lFnumClean As Long Dim sLine As String Const sDELA As String = "SELECT TO_CHAR(NET" Const sDELB As String = "AMT|DOC_NUM|FIPC|" Const sDELC As String = "REPORT COMPLETE" sCleanFileName = "C:\ImportClean.txt" lFnumIn = FreeFile Open sInputFile For Input As lFnumIn lFnumClean = FreeFile Open sCleanFileName For Output As lFnumClean Do While Not EOF(lFnumIn) Line Input #lFnumIn, sLine If InStr(1, sLine, sDELA) = 0 And _ InStr(1, sLine, sDELB) = 0 And _ InStr(1, sLine, sDELC) = 0 Then Print #lFnumClean, sLine End If Loop Close lFnumIn Close lFnumClean GetCleanFileName = sCleanFileName End Function -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
Dick,
The Function solution has arrived, Thanks. What are your thoughts as to the below solution as opposed to the Regex? My point being, that RegEx is probably the best for HUUUUGH files but begs the next issue - RegEx Reference libraries. EagleOne Dick Kusleika wrote: On Fri, 01 Aug 2008 20:36:31 -0400, wrote: The import itself is not the issue. What I was looking for is a function that would: 1) read an entire text file line by line 2) find each and every line with an instance of: SELECT TO_CHAR(NET (and/or) AMT|DOC_NUM|FIPC| (and/or) REPORT COMPLETE (and/or) 3) delete the entire line(s) 4) leaving only the pure data 5) save the file 6) (then import into Excel about which I do not need assistance) Sub DoStuff() Dim sFileToImport As String sFileToImport = GetCleanFileName("C:\Import.txt") 'do import stuff here End Sub Function GetCleanFileName(sInputFile As String) As String Dim sCleanFileName As String Dim lFnumIn As Long, lFnumClean As Long Dim sLine As String Const sDELA As String = "SELECT TO_CHAR(NET" Const sDELB As String = "AMT|DOC_NUM|FIPC|" Const sDELC As String = "REPORT COMPLETE" sCleanFileName = "C:\ImportClean.txt" lFnumIn = FreeFile Open sInputFile For Input As lFnumIn lFnumClean = FreeFile Open sCleanFileName For Output As lFnumClean Do While Not EOF(lFnumIn) Line Input #lFnumIn, sLine If InStr(1, sLine, sDELA) = 0 And _ InStr(1, sLine, sDELB) = 0 And _ InStr(1, sLine, sDELC) = 0 Then Print #lFnumClean, sLine End If Loop Close lFnumIn Close lFnumClean GetCleanFileName = sCleanFileName End Function |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
On Sat, 02 Aug 2008 10:14:17 -0400,
wrote: Dick, The Function solution has arrived, Thanks. What are your thoughts as to the below solution as opposed to the Regex? My point being, that RegEx is probably the best for HUUUUGH files but begs the next issue - RegEx Reference libraries. I'm not a regex expert, but that never stops me from giving my opinion. For me, regex is great when you have a lot of possible strings that can be distilled down to a few patterns. If you want to delete all the phone numbers from a text file, my solutions falls flat while regex is the perfect solution. But for your situation, with only three possible strings, regex seems like overkill. -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
Another point:
The text file typically exceeds 65,000 lines. Therefore, I need to clear the file of non-data lines outside of Excel. I am not conversant in file/line/string manipulation in VBA (pre-import to) of Excel Joel wrote: This is very simple. I don't know what you are refering to when you mean FIND STRING since everything is a string in the TEXT file. It is also important to know wher each line ends. Not sure if your code exactly represents the text file. Open the file with Notepad and make sure WORD WRAP (Format menu) is NOT checked. Then lok at text file and let me know where the end of each line is located. I have two methods of doing this task. I can import the file and then make the changes in excel to delete the data you don't want. Or make the changes before I put the data into spreadsheet. If you want to try a quick experiment try to impor the dat manually. From worksheet menue 1) Data - Import External Data - Import Data 2) Press Delimited - Next 3) Uncheck everything except Other and put the | in the box next to Other 4) Press finish and select where you want the data to go. You can also turn on Record Macro before you do these operations to get a macro. " wrote: 2003 Need a VBA function to read text file; find string; delete same before import. The function should find all instances (there can be many) of: SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| the last line of the file REPORT COMPLETE In short, just the data please SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| SELECT TO_CHAR(NET AMT|DOC_NUM|FIPC| 407.44|N6887008TORHMS5|D|22|AA|08| 2500.00|N6887008MDNC018|D|36|AA|08| -2500.00|N6887008MDNC013|D|36|AA|08| 2133.18|N6887008MDNC013|D|36|AA|08 -1004.93|N6887008WR82003|D|36|AA|08| REPORT COMPLETE TIA any and all help greatly appreciated |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find Text File Import Cell | Excel Programming | |||
| How to import a text file to Excel treating all input content are in string. | Excel Discussion (Misc queries) | |||
| Import partial record from text file string | Excel Programming | |||
| Import partial record from text file string | Excel Programming | |||
| backwards find function to find character in a string of text | Excel Programming | |||
Hybrid Mode
