vb code to delete blank lines in text file

[jdttek]

I have a txt file created from a report extract (Eg, system saved as text file (rtf, txt, csv, etc). Text file has blank lines between records (para forces new line). I need to condense the text file, removing the blank lines, before parsing and exporting to excel. With blank lines, text file has over 65,000 lines and Excel will not take that many. With spaces deleted, I have only 30,000 lines.

Any sugestions on code that would delete all blank lines?
Any other suggestions to import text file to excel with over 65,000 lines?

Thanks for any suggestions

jdttek

PS. I have been opening the txt file in Word to edit. Any other utilities for working with text files?

 

[CaKiwi]

I know this a vb forum but you could use the awk (or gawk) utility to delete blank lines by entering the following at the command line:

gawk "length>0" input-file > output-file

If you don't have it you can download it from sourceforge

http://sourceforge.net/project/showfiles.php?group_id=23617

CaKiwi

 

[RonVaught]

Make a reference to (Microsoft Scripting Runtime) in your VB app. This will allow the use to the File System Object



Option Explicit

Private Sub Form_Load()
Dim fso As FileSystemObject
Dim tsReadFile As TextStream
Dim tsWriteFile As TextStream
Dim sRecord As String

Set fso = New FileSystemObject

Set tsReadFile = fspenTextFile("C:\FileWithBlankLine.txt", ForReading)
Set tsWriteFile = fso.CreateTextFile("C:\CleanFile.txt&quot

Do Until tsReadFile.AtEndOfStream

sRecord = tsReadFile.ReadLine
sRecord = Trim$(sRecord)
If sRecord = vbNullString Then
' if line is null, do nothing
Else
tsWriteFile.WriteLine sRecord
End If

Loop

Set fso = Nothing
tsReadFile.Close
tsWriteFile.Close

Set tsReadFile = Nothing
Set tsWriteFile = Nothing
End Sub

 

[teh215]

FSO is OK but the size of the DLL is a problem if the App is to be distributed via download. I would rather read through the file using native Open and Line Input and output the record to a temp file if the record is <> &quot;&quot; and then replace the origianl file with the temp file ...

 

[strongm]

You are aware, I assume, that generally you don't actually HAVE to distribute scrrun.dll, aren't you? It's been included with the operating system since Windows 98. The only OS you won't find it on by default is W95* - and if you've ever installed Office 2000 or any version of Internet Explorer 5 or later on W95, then that too will have the necessary dll.

Whilst scrrun has had new features added to it over the years, the FSO component has been there since the first release. So in almost all cases you can deploy your application without having to include the DLL.


* Not quite true. NT4 doesn't include it by default, but it is included from SP5 onwards. And as with W95, if Office 2000 or later, or IE5 or later has been installed, then scrrun.dll will be available

 

[vb5prgrmr]

Then give this a try.

[tt]
Option Explicit

Private Sub Command1_Click()

On Error GoTo Command1_ClickError

Dim IName As String, INumb As Integer
Dim OName As String, ONumb As Integer
Dim TextLine As String

Command1.Enabled = False

With CommonDialog1
.CancelError = True
.Filter = &quot;Text Files *.txt|*.txt&quot;
.DialogTitle = &quot;Select File For Testing&quot;
.FileName = &quot;&quot;
.ShowOpen

If .FileName = &quot;&quot; Then Exit Sub
IName = .FileName
OName = App.Path & &quot;\temp.txt&quot;
End With

INumb = FreeFile

Open IName For Input As #INumb

ONumb = FreeFile

Open OName For OutPut As #ONumb

Do While Not EOF(INumb)

Line Input #INumb, TextLine
If Trim(TextLine) <> &quot;&quot; Then Print #ONumb, TextLine
DoEvents

Loop

Close #INumb
Close #ONumb

FileCopy App.Path & &quot;\temp.txt&quot;, IName

Command1.Enabled = True

Exit Sub
Command1_ClickError:

If Err.Number = 32755 Then
Command1.Enabled = True
Exit Sub
End If

MsgBox Err.Description

End Sub

[/tt]

 

[f1car]

open &quot;x&quot; for input as #1
open &quot;y&quot; fpr output as #2

Do until eof(1)

line input #1, var1$

var2$ = ltrim$(var1$)

if len(var2$) <> &quot;&quot; then
print #2, var1$
end if

loop