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!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
LD16Author Commented:
Thank you very much Robert! I will test it on monday as I don't have windows at home :-).
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.

Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

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
			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
			Set m = Nothing
			objFileOut.WriteLine strLineOut
		End If
	Set objFile = Nothing
	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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LD16Author Commented:
Hello Robert,

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

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...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.