Solved

VB Script help removing empty lines from .csv files

Posted on 2014-10-16
3
647 Views
Last Modified: 2014-10-17
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
Comment
Question by:Sir Learnalot
3 Comments
 
LVL 25

Expert Comment

by:SStory
ID: 40385445
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
 
LVL 37

Accepted Solution

by:
Gerwin Jansen earned 500 total points
ID: 40385476
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
 
LVL 6

Author Closing Comment

by:Sir Learnalot
ID: 40386627
Thank you very much this worked perfectly!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the years I've spent many an hour playing on hardened, DMZ'd servers, with only a sub-set of the usual GNU toy's to keep me company; frequently I've needed to save and send log or data extracts from these server back to my PC, or to others, and…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now