Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove lines from text file prior to importing in Excel
I've scoured the usual sources, but haven't found any existing code
that will accomplish what I need, so here's my situation. I've got a large text file from my company's mainframe. It's around 120,000 lines, with each record from a particular database taking up several lines (generally 8 to 16). Of those lines, only 4 of them are needed for the output file I need to produce. Since there is an import limit on Excel 2003, I'd like to remove the extraneous lines prior to the actual opening of the file in Excel. It should be pretty easy to isolate which lines to delete: Example data: RECORD1 11 DATA-01 DATA-25 DATA-49 RECORD1 14 DATA-02 DATA-26 DATA-50 RECORD1 17 DATA-03 DATA-27 DATA-51 RECORD1 20 DATA-04 DATA-28 DATA-52 RECORD1 31 DATA-05 DATA-29 DATA-53 RECORD1 34 DATA-06 DATA-30 DATA-54 RECORD1 37 DATA-07 DATA-31 DATA-55 RECORD1 40 DATA-08 DATA-32 DATA-56 RECORD2 11 DATA-09 DATA-33 DATA-57 RECORD2 14 DATA-10 DATA-34 DATA-58 RECORD2 17 DATA-11 DATA-35 DATA-59 RECORD2 20 DATA-12 DATA-36 DATA-60 RECORD2 31 DATA-13 DATA-37 DATA-61 RECORD2 34 DATA-14 DATA-38 DATA-62 RECORD2 37 DATA-15 DATA-39 DATA-63 RECORD2 40 DATA-16 DATA-40 DATA-64 RECORD3 11 DATA-17 DATA-41 DATA-65 RECORD3 14 DATA-18 DATA-42 DATA-66 RECORD3 17 DATA-19 DATA-43 DATA-67 RECORD3 20 DATA-20 DATA-44 DATA-68 RECORD3 31 DATA-21 DATA-45 DATA-69 RECORD3 34 DATA-22 DATA-46 DATA-70 RECORD3 37 DATA-23 DATA-47 DATA-71 RECORD3 40 DATA-24 DATA-48 DATA-72 Basically, the 9th and 10th characters of a row would determine whether or not I keep the line or delete them. I know how to do this in VBA (Basically a match using If Mid(Cells(currow, 1), 9, 2) < "[Value to Match]" And etc...), but I can't figure out how to manipulate the text prior to importing to avoid that 65,000 row limit. Any help would be appreciated. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove lines from text file prior to importing in Excel
On 1 Mar, 19:05, "Aaron.*" wrote:
I've scoured the usual sources, but haven't found any existing code that will accomplish what I need, so here's my situation. I've got a large text file from my company's mainframe. *It's around 120,000 lines, with each record from a particular database taking up several lines (generally 8 to 16). *Of those lines, only 4 of them are needed for the output file I need to produce. *Since there is an import limit on Excel 2003, I'd like to remove the extraneous lines prior to the actual opening of the file in Excel. *It should be pretty easy to isolate which lines to delete: Example data: RECORD1 11 DATA-01 DATA-25 DATA-49 RECORD1 14 DATA-02 DATA-26 DATA-50 RECORD1 17 DATA-03 DATA-27 DATA-51 RECORD1 20 DATA-04 DATA-28 DATA-52 RECORD1 31 DATA-05 DATA-29 DATA-53 RECORD1 34 DATA-06 DATA-30 DATA-54 RECORD1 37 DATA-07 DATA-31 DATA-55 RECORD1 40 DATA-08 DATA-32 DATA-56 RECORD2 11 DATA-09 DATA-33 DATA-57 RECORD2 14 DATA-10 DATA-34 DATA-58 RECORD2 17 DATA-11 DATA-35 DATA-59 RECORD2 20 DATA-12 DATA-36 DATA-60 RECORD2 31 DATA-13 DATA-37 DATA-61 RECORD2 34 DATA-14 DATA-38 DATA-62 RECORD2 37 DATA-15 DATA-39 DATA-63 RECORD2 40 DATA-16 DATA-40 DATA-64 RECORD3 11 DATA-17 DATA-41 DATA-65 RECORD3 14 DATA-18 DATA-42 DATA-66 RECORD3 17 DATA-19 DATA-43 DATA-67 RECORD3 20 DATA-20 DATA-44 DATA-68 RECORD3 31 DATA-21 DATA-45 DATA-69 RECORD3 34 DATA-22 DATA-46 DATA-70 RECORD3 37 DATA-23 DATA-47 DATA-71 RECORD3 40 DATA-24 DATA-48 DATA-72 Basically, the 9th and 10th characters of a row would determine whether or not I keep the line or delete them. I know how to do this in VBA (Basically a match using If Mid(Cells(currow, 1), 9, 2) < "[Value to Match]" And etc...), but I can't figure out how to manipulate the text prior to importing to avoid that 65,000 row limit. Any help would be appreciated. Thanks! Aaron, As far as I am aware, there is no easy way of doing this, since the import routines supplied with Excel do not allow for the exclusion of specified records - only skipping fields. I suspect that you will have to write your own VBA subroutine to import the records selectively. That is what I did when I was faced with a similar problem a couple of years ago. If you need further information, I will gladly supply a code fragment that you can modify to suit your own needs. JAC |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove lines from text file prior to importing in Excel
I guess I asked too soon. I found some code on-line and was able to
put it together in a form that would work for my uses. Actually pretty straight forward. Sub DeleteExtraLines(SourceFile As String) Dim TargetFile As String, tLine As String Dim i As Long, F1 As Integer, F2 As Integer TargetFile = "RESULT.TMP" If Dir(SourceFile) = "" Then Exit Sub If Dir(TargetFile) < "" Then On Error Resume Next Kill TargetFile On Error GoTo 0 If Dir(TargetFile) < "" Then MsgBox TargetFile & _ " already open, close and delete / rename the file and try again.", _ vbCritical Exit Sub End If End If F1 = FreeFile Open SourceFile For Input As F1 F2 = FreeFile Open TargetFile For Output As F2 i = 1 ' line counter Application.StatusBar = "Reading data from " & _ TargetFile & " ..." While Not EOF(F1) If i Mod 100 = 0 Then Application.StatusBar = _ "Reading line #" & i & " in " & _ TargetFile & " ..." Line Input #F1, tLine If Mid(tLine, 9, 3) = "009" Then 'This is where you enter the clause for which lines you want to copy, in my case, any line where the 9-11 characters are "009" Print #F2, tLine End If i = i + 1 Wend Application.StatusBar = "Closing files ..." Close F1 Close F2 Kill SourceFile ' delete original file Name TargetFile As SourceFile ' rename temporary file Application.StatusBar = False End Sub Hope this helps someone else in the future. On Mar 1, 1:55*pm, JAC wrote: On 1 Mar, 19:05, "Aaron.*" wrote: I've scoured the usual sources, but haven't found any existing code that will accomplish what I need, so here's my situation. I've got a large text file from my company's mainframe. *It's around 120,000 lines, with each record from a particular database taking up several lines (generally 8 to 16). *Of those lines, only 4 of them are needed for the output file I need to produce. *Since there is an import limit on Excel 2003, I'd like to remove the extraneous lines prior to the actual opening of the file in Excel. *It should be pretty easy to isolate which lines to delete: Example data: RECORD1 11 DATA-01 DATA-25 DATA-49 RECORD1 14 DATA-02 DATA-26 DATA-50 RECORD1 17 DATA-03 DATA-27 DATA-51 RECORD1 20 DATA-04 DATA-28 DATA-52 RECORD1 31 DATA-05 DATA-29 DATA-53 RECORD1 34 DATA-06 DATA-30 DATA-54 RECORD1 37 DATA-07 DATA-31 DATA-55 RECORD1 40 DATA-08 DATA-32 DATA-56 RECORD2 11 DATA-09 DATA-33 DATA-57 RECORD2 14 DATA-10 DATA-34 DATA-58 RECORD2 17 DATA-11 DATA-35 DATA-59 RECORD2 20 DATA-12 DATA-36 DATA-60 RECORD2 31 DATA-13 DATA-37 DATA-61 RECORD2 34 DATA-14 DATA-38 DATA-62 RECORD2 37 DATA-15 DATA-39 DATA-63 RECORD2 40 DATA-16 DATA-40 DATA-64 RECORD3 11 DATA-17 DATA-41 DATA-65 RECORD3 14 DATA-18 DATA-42 DATA-66 RECORD3 17 DATA-19 DATA-43 DATA-67 RECORD3 20 DATA-20 DATA-44 DATA-68 RECORD3 31 DATA-21 DATA-45 DATA-69 RECORD3 34 DATA-22 DATA-46 DATA-70 RECORD3 37 DATA-23 DATA-47 DATA-71 RECORD3 40 DATA-24 DATA-48 DATA-72 Basically, the 9th and 10th characters of a row would determine whether or not I keep the line or delete them. I know how to do this in VBA (Basically a match using If Mid(Cells(currow, 1), 9, 2) < "[Value to Match]" And etc...), but I can't figure out how to manipulate the text prior to importing to avoid that 65,000 row limit. Any help would be appreciated. Thanks! Aaron, As far as I am aware, there is no easy way of doing this, since the import routines supplied with Excel do not allow for the exclusion of specified records - only skipping fields. I suspect that you will have to write your own VBA subroutine to import the records selectively. That is what I did when I was faced with a similar problem a couple of years ago. If you need further information, I will gladly supply a code fragment that you can modify to suit your own needs. JAC- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove lines from text file prior to importing in Excel
Glad you got it working! I often post, and then find a solution right
afterward. Anyway, here's a generic solution for importing a large *.txt file, for you for next time, or for anyone else reading this post: Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double Dim mySht As Worksheet 'Ask User for File's Name FileName = Application.GetOpenFilename 'Check for no entry If FileName = "" Then End 'Get Next Available File Handle Number FileNum = FreeFile() 'Open Text File For Input Open FileName For Input As #FileNum 'Turn Screen Updating Off Application.ScreenUpdating = False 'Create A New WorkBook With One Worksheet In It Workbooks.Add Template:=xlWorksheet 'Set The Counter to 1 Counter = 1 'Loop Until the End Of File Is Reached Do While Seek(FileNum) <= LOF(FileNum) 'Display Importing Row Number On Status Bar Application.StatusBar = "Importing Row " & _ Counter & " of text file " & FileName 'Store One Line Of Text From File To Variable Line Input #FileNum, ResultStr 'Store Variable Data Into Active Cell If Left(ResultStr, 1) = "=" Then ActiveCell.Value = "'" & ResultStr Else ActiveCell.Value = ResultStr End If 'For xl95 change 65536 to 16384 If ActiveCell.Row = 65536 Then 'If On The Last Row Then Add A New Sheet ' and copy the header row from the last sheet Set mySht = ActiveSheet ActiveWorkbook.Sheets.Add mySht.Cells(1, 1).EntireRow.Copy ActiveSheet.Cells(1, 1).EntireRow Application.CutCopyMode = False ActiveCell.Offset(1, 0).Select Else 'If Not The Last Row Then Go One Cell Down ActiveCell.Offset(1, 0).Select End If 'Increment the Counter By 1 Counter = Counter + 1 'Start Again At Top Of 'Do While' Statement Loop 'Close The Open Text File Close 'Remove Message From Status Bar Application.StatusBar = False End Sub -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Aaron.*" wrote: I guess I asked too soon. I found some code on-line and was able to put it together in a form that would work for my uses. Actually pretty straight forward. Sub DeleteExtraLines(SourceFile As String) Dim TargetFile As String, tLine As String Dim i As Long, F1 As Integer, F2 As Integer TargetFile = "RESULT.TMP" If Dir(SourceFile) = "" Then Exit Sub If Dir(TargetFile) < "" Then On Error Resume Next Kill TargetFile On Error GoTo 0 If Dir(TargetFile) < "" Then MsgBox TargetFile & _ " already open, close and delete / rename the file and try again.", _ vbCritical Exit Sub End If End If F1 = FreeFile Open SourceFile For Input As F1 F2 = FreeFile Open TargetFile For Output As F2 i = 1 ' line counter Application.StatusBar = "Reading data from " & _ TargetFile & " ..." While Not EOF(F1) If i Mod 100 = 0 Then Application.StatusBar = _ "Reading line #" & i & " in " & _ TargetFile & " ..." Line Input #F1, tLine If Mid(tLine, 9, 3) = "009" Then 'This is where you enter the clause for which lines you want to copy, in my case, any line where the 9-11 characters are "009" Print #F2, tLine End If i = i + 1 Wend Application.StatusBar = "Closing files ..." Close F1 Close F2 Kill SourceFile ' delete original file Name TargetFile As SourceFile ' rename temporary file Application.StatusBar = False End Sub Hope this helps someone else in the future. On Mar 1, 1:55 pm, JAC wrote: On 1 Mar, 19:05, "Aaron.*" wrote: I've scoured the usual sources, but haven't found any existing code that will accomplish what I need, so here's my situation. I've got a large text file from my company's mainframe. It's around 120,000 lines, with each record from a particular database taking up several lines (generally 8 to 16). Of those lines, only 4 of them are needed for the output file I need to produce. Since there is an import limit on Excel 2003, I'd like to remove the extraneous lines prior to the actual opening of the file in Excel. It should be pretty easy to isolate which lines to delete: Example data: RECORD1 11 DATA-01 DATA-25 DATA-49 RECORD1 14 DATA-02 DATA-26 DATA-50 RECORD1 17 DATA-03 DATA-27 DATA-51 RECORD1 20 DATA-04 DATA-28 DATA-52 RECORD1 31 DATA-05 DATA-29 DATA-53 RECORD1 34 DATA-06 DATA-30 DATA-54 RECORD1 37 DATA-07 DATA-31 DATA-55 RECORD1 40 DATA-08 DATA-32 DATA-56 RECORD2 11 DATA-09 DATA-33 DATA-57 RECORD2 14 DATA-10 DATA-34 DATA-58 RECORD2 17 DATA-11 DATA-35 DATA-59 RECORD2 20 DATA-12 DATA-36 DATA-60 RECORD2 31 DATA-13 DATA-37 DATA-61 RECORD2 34 DATA-14 DATA-38 DATA-62 RECORD2 37 DATA-15 DATA-39 DATA-63 RECORD2 40 DATA-16 DATA-40 DATA-64 RECORD3 11 DATA-17 DATA-41 DATA-65 RECORD3 14 DATA-18 DATA-42 DATA-66 RECORD3 17 DATA-19 DATA-43 DATA-67 RECORD3 20 DATA-20 DATA-44 DATA-68 RECORD3 31 DATA-21 DATA-45 DATA-69 RECORD3 34 DATA-22 DATA-46 DATA-70 RECORD3 37 DATA-23 DATA-47 DATA-71 RECORD3 40 DATA-24 DATA-48 DATA-72 Basically, the 9th and 10th characters of a row would determine whether or not I keep the line or delete them. I know how to do this in VBA (Basically a match using If Mid(Cells(currow, 1), 9, 2) < "[Value to Match]" And etc...), but I can't figure out how to manipulate the text prior to importing to avoid that 65,000 row limit. Any help would be appreciated. Thanks! Aaron, As far as I am aware, there is no easy way of doing this, since the import routines supplied with Excel do not allow for the exclusion of specified records - only skipping fields. I suspect that you will have to write your own VBA subroutine to import the records selectively. That is what I did when I was faced with a similar problem a couple of years ago. If you need further information, I will gladly supply a code fragment that you can modify to suit your own needs. JAC- Hide quoted text - - Show quoted text - . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modify The Code For Importing Text File into an Excel File | Excel Programming | |||
How do I remove lines through text in excel | Excel Discussion (Misc queries) | |||
Importing Text Files of 100,000+ lines, many blanks | Excel Programming | |||
Importing data from a Text File, Not enough lines? | Excel Programming | |||
Importing Multiple lines of text in a single Excel Cel | Excel Programming |