batch CSV editing

bledd

bledd

bledd

Don
Joined
21 Oct 2002
Posts
46,863
Location
Parts Unknown
I've got a few hundred CSV files, and I need to do the following..

remove line 4
remove line 2
remove line 1

what's the quickest way to do this? in windows


need to not only remove the entries, but the whole line
 
Just line 4,2,1 ?

You could make a vb.net / c# console application and look at streamwriter, if you are still have troubles i will post some code after work :)
 
Last edited:
Here you go, copy the below code in Notepad and save it as RemoveCSVLines.vbs or whatever name means something to you. You can set which lines to skip at the Select statement.

The output files will have the extension .CSV_ just incase you make a mistake. If you're happy with them just delete the orignals and command prompt

rename *.CSV_ *.CSV

Code: 
Dim fso, folderObject, filesObject, fileObject
Dim topFolderName
Dim fileCounter
topFolderName = InputBox("Enter path to CSV files to update including trailling \.")

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FolderExists(topFolderName) Then
    Set folderObject = fso.GetFolder(topFolderName)
    Set filesCollection = folderObject.Files
    
    For Each file In filesCollection
	If UCASE(Right(file.Name,3)) = "CSV" Then
	    removeLines (topFolderName & file.Name)
	    fileCounter = fileCounter + 1
	End If	
    Next
    
    
Else
    MsgBox "Source folder not found.", vbExclamation, "Error"
  
End If

MsgBox fileCounter & " files processed.", vbOKOnly + vbInformation, "Complete"


Set fso = Nothing
Set folderObject = Nothing
Set filesCollection = Nothing

Private Function removeLines(ByVal sFileToOpen)


Dim fsoObject
Dim textFileReadObject
Dim textFileWriteObject
Dim lineCounter

Set fsoObject = CreateObject("Scripting.FileSystemObject")
Set textFileReadObject = fsoObject.OpenTextFile(sFileToOpen)
Set textFileWriteObject = fsoObject.CreateTextFile(sFileToOpen & "_", True)

lineCounter = 0
    Do While Not textFileReadObject.AtEndOfStream
        
        lineCounter = lineCounter + 1
        sReadLine = textFileReadObject.ReadLine
        
        Select Case lineCounter
                Case 1, 2, 4
                ' The lines above will be skipped - modify them to get desiredresults
                
                Case Else
                    textFileWriteObject.WriteLine (sReadLine)
        
        End Select
        
        
        
        
    Loop


Set fsoObject = Nothing
Set textfileObject = Nothing
Set textFileReadObject = Nothing
Set textFileWriteObject = Nothing

End Function
 
Hi :)

someone beaten me to it :)

Here was my effort over my lunch :)

Code: 
Imports System

Imports System.IO

Module Module1

    Dim oFile As System.IO.File

    Dim arrNewFile As New ArrayList

    Dim files() As String

    Sub Main(ByVal ParamArray Args() As String)

        Dim oRead As System.IO.StreamReader

        Dim iC As Integer

        ChDir("C:\Folder") 'Change to working Directory e.g "C:\Folder"

        files = System.IO.Directory.GetFiles(".", "*.csv*") 'Use GetFiles to add all CSV files to 'files' string array

        iC = 0

        Do Until iC = files.Count 'Loop until files.count is meet

            oRead = oFile.OpenText("C:\Folder" & files(iC)) 'Select files per String array

            While oRead.Peek <> -1

                arrNewFile.Add(oRead.ReadLine()) 'Loops through current file adding items to array list (arrNewFile)

            End While

            oRead.Close() 'Close StreamReader (file is locked/in use otherwise)

            'Remove unneed lines from arrNewFile

            arrNewFile.RemoveAt(0)

            arrNewFile.RemoveAt(0)

            arrNewFile.RemoveAt(2)

            Kill("C:\Folder" & files(iC)) 'Delete starting file

            Write_Delimited("C:\Folder" & files(iC)) 'write new replacement files based on array

            iC += 1 'Increase count by 1

        Loop

    End Sub

    Private Sub Write_Delimited(ByVal strNewFile)

        Dim iC As Integer

        iC = 0

        Dim objWriter As New System.IO.StreamWriter(strNewFile, True) 'Recreate blank file

        Do Until ic = arrNewFile.Count

            objWriter.WriteLine(arrNewFile(iC)) 'Write array list into blank file

            iC += 1

        Loop

        objWriter.Close()

    End Sub

End Module
 
Code: 
SETLOCAL ENABLEDELAYEDEXPANSION
SET COUNT=1

FOR /F "tokens=*" %%A IN (%1) DO (
	IF !COUNT!==3 (
	ECHO %%A
	)
	IF !COUNT! GTR 4 (
	ECHO %%A
	)
	SET /A COUNT=COUNT+1
)


Just redirect it, or add redirection to the echo statements.
 
whitecrook said:
Code: 
SETLOCAL ENABLEDELAYEDEXPANSION
SET COUNT=1

FOR /F "tokens=*" %%A IN (%1) DO (
	IF !COUNT!==3 (
	ECHO %%A
	)
	IF !COUNT! GTR 4 (
	ECHO %%A
	)
	SET /A COUNT=COUNT+1
)


Just redirect it, or add redirection to the echo statements.
Click to expand...

thanks for all your efforts guys, programming really isn't my forte, I just never 'get' it, no matter how many noob guides I read.

I'm afraid a call and an if in a bat file is as far as my knowledge goes.


i'm just using what wozencl made for me, does what I need it to
 
You must log in or register to reply here.
Back
Top Bottom