VB Script: remove specifics lines and columns

Hello Experts,

I am looking for a VB Script which read a and remove specific lines and columns of File1.csv defined in differents Array variables and generate a File1_revised.csv:


Ex of Array Variable:
      linestoremove = Array(1,4,5)
      columnstoremove = Array(2,8,6)

Afer I launch the script File1_revised.csv will be created and shouldn't contains lines 1,4 and 5 and columns 2,6 and 8 of File1.csv.

If someone can provide the pure VbScript approacch and the CreateObject("Excel.Application") approach it would be great!

Thank you in advance for your help!
LVL 1
LD16Asked:
Who is Participating?
 
Robert SchuttSoftware EngineerCommented:
You can use another separator, I changed lines 24 and 42 for that in the code below (and the input/output filenames to keep it separate from the first test).

Note that your data has a problem: the fields contain  the separator! To test quickly, I changed it to:
Value on row 1, column 1;Value on row 1, column 2; ... etc ...

Open in new window

instead of:
Value on row 1; column 1;Value on row 1; column 2; ... etc ...

Open in new window


So note that if you want to use a separator in any value, you need to enclose the value in quotes (standard csv behaviour).

EDIT: what would also work is:
"Value on row 1; column 1";"Value on row 1; column 2"; ... etc ...

Open in new window


new code (remove1-test.vbs):
Option Explicit

' given

Dim linestoremove, columnstoremove
      linestoremove = Array(1,4,5)
      columnstoremove = Array(2,8,6)

' constants & variable declaration
Const C_HEADER_ROW = True

Dim strCheckLinesToRemove, strCheckColumnsToRemove, objFSO, objFile, arrFields, objFileOut, strLine, strLineOut, intCountRow, intCountCol, blnIncludeRow, objRE, m, mv

' main
Sub Main
	strCheckLinesToRemove = "|" & Join(linestoremove, "|") & "|"
	strCheckColumnsToRemove = "|" & Join(columnstoremove, "|") & "|"

	Set objFSO = CreateObject("Scripting.FileSystemObject")

	Set objRE = New RegExp
	objRE.Global = True
	objRE.MultiLine = False
	objRE.Pattern = "(?:^|; *)(""(?:""""|[^""])*""|[^;""]*)"

	Set objFile = objFSO.OpenTextFile("File1-test.csv", 1, False)
	Set objFileOut = objFSO.OpenTextFile("File1-test_revised.csv", 2, True)
	If C_HEADER_ROW Then intCountRow = -1 Else intCountRow = 0
	While Not objFile.AtEndOfStream
		strLine = objFile.ReadLine
		intCountRow = intCountRow + 1
		If C_HEADER_ROW And intCountRow = 0 Then
			blnIncludeRow = True
		Else
			blnIncludeRow = InStr(strCheckLinesToRemove, "|" & intCountRow & "|") = 0
		End If
		If blnIncludeRow Then
			strLineOut = ""
			Set m = objRE.Execute(strLine)
			For intCountCol = 1 To m.Count
				If InStr(strCheckColumnsToRemove, "|" & intCountCol & "|") = 0 Then
					If strLineOut > "" Then strLineOut = strLineOut & ";"
					mv = m.Item(intCountCol - 1).SubMatches(0)
					strLineOut = strLineOut & mv
				End If
			Next
			Set m = Nothing
			objFileOut.WriteLine strLineOut
		End If
	Wend
	objFile.Close
	Set objFile = Nothing
	objFileOut.Close
	Set objFileOut = Nothing
	Set objRE = Nothing
	Set objFSO = Nothing
End Sub

Call Main

Open in new window



with an xls version you can only use the second script, but should work fine only by changing the input filename (untested for now though).

EDIT: txt version should not be a problem, assuming you are still using a specific field separator.
0
 
Robert SchuttSoftware EngineerCommented:
How about these?

Note the test file contains field values with quotes because I was testing a regular expression for that and thought I might as well use that.

The scripts contain a constant "setting" to use a header row because I wanted to test that too, set it to False if you don't use headers.
remove1.vbs
remove2.vbs
File1.csv
0
 
LD16Author Commented:
Thank you very much Robert! I will test it on monday as I don't have windows at home :-).
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LD16Author Commented:
Hello Robert,

I am trying to test your first code remove1.vbs with a csv which contains ; separator and not """" between every column and I am not able to run it.
Please find attached the file which I used  for the test.

Additionally, What should I modify if the file is a xls version or a txt version?


Thank you in advance for your help.

Regards,
File1-test.csv
0
 
LD16Author Commented:
Hello Robert,

It works!
Thank you again for your help!
0
 
Robert SchuttSoftware EngineerCommented:
Great!

Be careful by the way, the second script does not work for me with semicolon delimited input. Googling around I found that it's got something to do with localization. For me the default separator is comma and Excel is darn stubborn about letting me change that...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.