| 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 |
|
#4
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 |
|
#6
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 |
|
#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
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
|
|
#10
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 |
|
#11
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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
Dave Peterson wrote:
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 (^: Haha!... We had 11x17 reports too, which were often printed on the wrong side of greenbar by IS OPS because they could not figure out how to load the stock in the printer. What to do... |
|
#13
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 |
|
#14
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 |
|
#15
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 |
|
#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
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 |
|
#18
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 |
|
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
Rick,
Is it necessary (the only way) to VB6-compile a DLL to call from a macro to "secure" the code? If so, I'll purchase VB6 just for that purpose. I assume the following steps?: 1) write the VBA procedure(s) 2) load VB6 3) perform 1,2,3,4 steps in VB6 4) out pops a DLL. 5) refer to the DLL in my macro 6) (whatever I missed) Would you BRIEFLY (being sensitive to your time) expand upon #3 & #5? EagleOne "Rick Rothstein \(MVP - VB\)" wrote: 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 |
|
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
In reality, the files have gone to 25MB; average about 8MB.
All of the solutions have their place. In advance of my posting, I did do Google Searches - both the Add in and straight through. I was surprised that I did find much directly applicable/easily-adaptable to either Excel and/or Access. All of the responses are excellent for this forum to synergyze OPs on this issue. Thanks to all for your time and knowledge. "Rick Rothstein \(MVP - VB\)" wrote: 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 |
|
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
I'm not sure I understand what your "'secure' the code" concerns are... what
part of the process is it that you are worried about, what part do you think needs to be secured? Rick wrote in message ... Rick, Is it necessary (the only way) to VB6-compile a DLL to call from a macro to "secure" the code? If so, I'll purchase VB6 just for that purpose. I assume the following steps?: 1) write the VBA procedure(s) 2) load VB6 3) perform 1,2,3,4 steps in VB6 4) out pops a DLL. 5) refer to the DLL in my macro 6) (whatever I missed) Would you BRIEFLY (being sensitive to your time) expand upon #3 & #5? EagleOne "Rick Rothstein \(MVP - VB\)" wrote: 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 m... 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 |
|
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
If your files are really that big, I would not suggest your using the
solution I posted... 25 Megs would probably push the code too hard (I still think the code would work, but it would be much, much slower, exponentially so, than for a 2 to 3 Meg file). Rick wrote in message ... In reality, the files have gone to 25MB; average about 8MB. All of the solutions have their place. In advance of my posting, I did do Google Searches - both the Add in and straight through. I was surprised that I did find much directly applicable/easily-adaptable to either Excel and/or Access. All of the responses are excellent for this forum to synergyze OPs on this issue. Thanks to all for your time and knowledge. "Rick Rothstein \(MVP - VB\)" wrote: 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 |
|
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
I don't use regex to be effective. But I can copy and paste (and struggle while
debugging!). wrote: 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 -- Dave Peterson |
|
#24
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
I don't use regex to be effective.
should have been: I don't use regex enough to be effective. Using regex can be very effective! Dave Peterson wrote: I don't use regex to be effective. But I can copy and paste (and struggle while debugging!). wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#25
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
Using regex can be very effective!
But they can often require a **lot** of trial and error coding to get them "exactly right". Rick |
|
#26
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
In your reply you commented "if you have the stand-alone (compiled) Visual Basic program available (Version 6 only because of the use of the Replace function." This reminded me of compiling "macro project" into a .DLL which is much more "secure" than a VBProject password; at least that is what I heard. All that said, I cheched the price for VB6, about $600, and read that it is essentially orphaned (not sold by MS) in favor of .NET. Are there any alternate choices to secure VBA? TIA EagleOne "Rick Rothstein \(MVP - VB\)" wrote: I'm not sure I understand what your "'secure' the code" concerns are... what part of the process is it that you are worried about, what part do you think needs to be secured? Rick wrote in message .. . Rick, Is it necessary (the only way) to VB6-compile a DLL to call from a macro to "secure" the code? If so, I'll purchase VB6 just for that purpose. I assume the following steps?: 1) write the VBA procedure(s) 2) load VB6 3) perform 1,2,3,4 steps in VB6 4) out pops a DLL. 5) refer to the DLL in my macro 6) (whatever I missed) Would you BRIEFLY (being sensitive to your time) expand upon #3 & #5? EagleOne "Rick Rothstein \(MVP - VB\)" wrote: 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 om... 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 |
|
#27
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
Good point! I tried and found myself doing quite a **lot**. One has to use Regex all the time to
do it quickly like Ron Rosenfeld seems to do/be. "Rick Rothstein \(MVP - VB\)" wrote: Using regex can be very effective! But they can often require a **lot** of trial and error coding to get them "exactly right". Rick |
|
#28
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
Even at $600 you will find it money well spent.
Another option might be FreeBasic: http://www.freebasic.net/ RBS wrote in message ... In your reply you commented "if you have the stand-alone (compiled) Visual Basic program available (Version 6 only because of the use of the Replace function." This reminded me of compiling "macro project" into a .DLL which is much more "secure" than a VBProject password; at least that is what I heard. All that said, I cheched the price for VB6, about $600, and read that it is essentially orphaned (not sold by MS) in favor of .NET. Are there any alternate choices to secure VBA? TIA EagleOne "Rick Rothstein \(MVP - VB\)" wrote: I'm not sure I understand what your "'secure' the code" concerns are... what part of the process is it that you are worried about, what part do you think needs to be secured? Rick wrote in message . .. Rick, Is it necessary (the only way) to VB6-compile a DLL to call from a macro to "secure" the code? If so, I'll purchase VB6 just for that purpose. I assume the following steps?: 1) write the VBA procedure(s) 2) load VB6 3) perform 1,2,3,4 steps in VB6 4) out pops a DLL. 5) refer to the DLL in my macro 6) (whatever I missed) Would you BRIEFLY (being sensitive to your time) expand upon #3 & #5? EagleOne "Rick Rothstein \(MVP - VB\)" wrote: 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 m... 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 news:4ha794h95idlgkbvj95dqj8cu4ih8eaifb@4ax. com... 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 |
|
#29
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 |
|
#30
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
|
|
#31
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
Thanks Dick.
A few times I attempted to get my mind around RegEx and found it not very intuitive. Of course I said that about VBA at one time. Dick Kusleika wrote: 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. |
|
#32
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
I guess I do not appreciate the relative cost issues. All I want to do is compile my VBA code into
a DLL. It is quite possible that $600 is worth it. "RB Smissaert" wrote: Even at $600 you will find it money well spent. Another option might be FreeBasic: http://www.freebasic.net/ RBS wrote in message .. . In your reply you commented "if you have the stand-alone (compiled) Visual Basic program available (Version 6 only because of the use of the Replace function." This reminded me of compiling "macro project" into a .DLL which is much more "secure" than a VBProject password; at least that is what I heard. All that said, I cheched the price for VB6, about $600, and read that it is essentially orphaned (not sold by MS) in favor of .NET. Are there any alternate choices to secure VBA? TIA EagleOne "Rick Rothstein \(MVP - VB\)" wrote: I'm not sure I understand what your "'secure' the code" concerns are... what part of the process is it that you are worried about, what part do you think needs to be secured? Rick wrote in message ... Rick, Is it necessary (the only way) to VB6-compile a DLL to call from a macro to "secure" the code? If so, I'll purchase VB6 just for that purpose. I assume the following steps?: 1) write the VBA procedure(s) 2) load VB6 3) perform 1,2,3,4 steps in VB6 4) out pops a DLL. 5) refer to the DLL in my macro 6) (whatever I missed) Would you BRIEFLY (being sensitive to your time) expand upon #3 & #5? EagleOne "Rick Rothstein \(MVP - VB\)" wrote: 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 om... 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 news:4ha794h95idlgkbvj95dqj8cu4ih8eaifb@4ax .com... 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 |
|
#33
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to read text file; find string; delete same before import
You can't compile into a dll with VBA, but I believe with FreeBasic you can
and it is free. Must admit though I have never done it as I have VB6. RBS wrote in message ... I guess I do not appreciate the relative cost issues. All I want to do is compile my VBA code into a DLL. It is quite possible that $600 is worth it. "RB Smissaert" wrote: Even at $600 you will find it money well spent. Another option might be FreeBasic: http://www.freebasic.net/ RBS wrote in message . .. In your reply you commented "if you have the stand-alone (compiled) Visual Basic program available (Version 6 only because of the use of the Replace function." This reminded me of compiling "macro project" into a .DLL which is much more "secure" than a VBProject password; at least that is what I heard. All that said, I cheched the price for VB6, about $600, and read that it is essentially orphaned (not sold by MS) in favor of .NET. Are there any alternate choices to secure VBA? TIA EagleOne "Rick Rothstein \(MVP - VB\)" wrote: I'm not sure I understand what your "'secure' the code" concerns are... what part of the process is it that you are worried about, what part do you think needs to be secured? Rick wrote in message m... Rick, Is it necessary (the only way) to VB6-compile a DLL to call from a macro to "secure" the code? If so, I'll purchase VB6 just for that purpose. I assume the following steps?: 1) write the VBA procedure(s) 2) load VB6 3) perform 1,2,3,4 steps in VB6 4) out pops a DLL. 5) refer to the DLL in my macro 6) (whatever I missed) Would you BRIEFLY (being sensitive to your time) expand upon #3 & #5? EagleOne "Rick Rothstein \(MVP - VB\)" wrote: 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 news:49o894pfvsg0rlq8ofp5k814hpuih66edn@4ax. com... 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 news:4ha794h95idlgkbvj95dqj8cu4ih8eaifb@4a x.com... 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 |
| 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 | |||
Linear Mode
