[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 845
  • Last Modified:

VB Script help removing empty lines from .csv files

Hello Everyone,
Trying to write a vb script to delete all blank lines from a collection of .csv files in a folder (e.g. a.csv, b.csv, c.csv, all containing a single letter a,b,c, respectively, and some with blank lines). Here is my code, it runs without error but is not deleting the lines like it should. Suggestions?

sFolder = "C:\Users\akhoshkar\Documents\MyJabberFiles"
Set objFSO = CreateObject("Scripting.FileSystemObject")

For Each sFilename In objFSO.GetFolder(sFolder).Files
  If UCase(objFSO.GetExtensionName(sFilename.Name)) = "CSV" Then

	set objInFile = objFSO.OpenTextFile(sFilename, 1, true, 0)

	Do Until objInFile.AtEndOfStream

		strLine = objInFile.Readline
		strLine = Trim(strLine)
		If Len(strLine) > 0 Then
			strNewContents = strNewContents & strLine & vbCrLf
		End If

	Loop

	objInFile.Close

	set objOutFile = objFSO.OpenTextFile(sFilename, 2, true, 0)

	objOutFile.Write strNewContents
	objOutFile.Close

End If
Next

Open in new window


I have attached a sample environment for those who want to verify the code. 3 .csv files and the script inside a folder. Extract to C:\ for it to work properly (or just change the working directory inside the script). Thanks in advance everyone!
TEST.zip
0
Sir Learnalot
Asked:
Sir Learnalot
1 Solution
 
SStoryCommented:
You could do this with the grep program super simple.

grep . {name of your file}

or send to a file like so

grep . {name of your file} > {name of your output file}


obviously, don't use { or }

You can find that here:
http://gnuwin32.sourceforge.net/packages/grep.htm
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Try this:
sFolder = "C:\Temp\teest"
Set objFSO = CreateObject("Scripting.FileSystemObject")

For Each sFilename In objFSO.GetFolder(sFolder).Files
  If UCase(objFSO.GetExtensionName(sFilename.Name)) = "CSV" Then

	set objInFile = objFSO.OpenTextFile(sFilename, 1, true, 0)

        strNewContents = ""

	Do Until objInFile.AtEndOfStream

		strLine = objInFile.Readline
		strLine = Trim(strLine)
		If Len(strLine) > 0 Then
			strNewContents = strNewContents & strLine & vbCrLf
		End If

	Loop

	objInFile.Close

	set objOutFile = objFSO.OpenTextFile(sFilename, 2, true, 0)

	objOutFile.Write strNewContents
	objOutFile.Close

End If
Next

Open in new window

Note that your code always writes contents of a line + CrLf so last line will be an empty line.
0
 
Sir LearnalotAuthor Commented:
Thank you very much this worked perfectly!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now