Hi Guys,
I was wonder does anyone know.vbs scripting that can remove the entire column from .csv file? Example: entire column A is to remove
Hi Guys,
I was wonder does anyone know.vbs scripting that can remove the entire column from .csv file? Example: entire column A is to remove
The following VBScript will delete FIELDS (example 10 and 3) within the CSV file C:\TEST.csv
'On Error Resume Next
Dim objFSO, dataArray, clippedArray()
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Create an array out of the CSV
'open the data file
Set oTextStream = objFSO.OpenTextFile("C:\test.csv")
Set newFile = objFSO.CreateTextFile("C:\newCSV.csv")
'make an array from the data file
dataArray = Split(oTextStream.ReadAll, vbNewLine)
'close the data file
oTextStream.Close
x = 0
For Each strLine In dataArray
'Now make an array from each line
ReDim Preserve clippedArray(x)
clippedArray(x) = Split(strLine,",")
' Delete Field 10 and 3 (highest to lowest)
CutColumn = 10
CutColumn = 3
intCount = 0
newLine = ""
For Each Element In clippedArray(x)
If intCount = UBound(clippedArray(x)) Then
EndChar = vbCrLf
Else
EndChar = ","
End If
If intCount <> CutColumn -1 Then
newLine = newLine & Element & EndChar
End If
intCount = intCount + 1
If intCount = UBound(clippedArray(x))+1 Then
newFile.Write newLine
End If
Next
Next
You can try this as well:-
' NAME: RemoveColumnFromCSV.vbs
'
' AUTHOR: Mark D. MacLachlan , The Spider's Parlor
' URL: http://www.thespidersparlor.com
' DATE :
' COPYRIGHT (c) 2007 All Rights Reserved
'
' COMMENT:
'
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF
' ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
' THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
' PARTICULAR PURPOSE.
'
' IN NO EVENT SHALL THE SPIDER'S PARLOR AND/OR ITS RESPECTIVE SUPPLIERS
' BE LIABLE FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL DAMAGES OR ANY
' DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS,
' WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS
' ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE
' OF THIS CODE OR INFORMATION.
'
'==========================================================================
'On Error Resume Next
Dim objFSO, dataArray, clippedArray()
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Create an array out of the CSV
'open the data file
Set oTextStream = objFSO.OpenTextFile("C:\Testcsv.csv")
Set newFile = objFSO.CreateTextFile("C:\newCSV.txt")
'make an array from the data file
dataArray = Split(oTextStream.ReadAll, vbNewLine)
'close the data file
oTextStream.Close
x = 0
For Each strLine In dataArray
'Now make an array from each line
ReDim Preserve clippedArray(x)
clippedArray(x) = Split(strLine,",")
CutColumn = 3
intCount = 0
newLine = ""
For Each Element In clippedArray(x)
If intCount = UBound(clippedArray(x)) Then
EndChar = vbCrLf
Else
EndChar = ","
End If
If intCount <> CutColumn Then
newLine = newLine & Element & EndChar
End If
intCount = intCount + 1
If intCount = UBound(clippedArray(x))+1 Then
newFile.Write newLine
End If
Next
Next
WScript.Echo "Done"
The code look like detecting with a comma "," then it will know is the next column. But what i want is to removing entire column A without using the comma to check the next column because in column A the data contain many comma and each row of column A contain different comma (some 5 comma some is 7 comma)
Hi guys. Thanks for scripts above.
But is there any shorter way to delete for example column 3, 5,6 via VSB script ?
Open the file, modify and save ?
CZAS;DEPART;CLIENT;CLASSE;LOCATION;ERREURS;CODE ZNANY;MESSAGE;VALUE;TYP;CZAS;TAILLE(Kb);LALA;STREAMS;STORAGE;RETENTION
2012-04-13;02:00:59;vbsscript;vbsscript_aix_rgsedi01;rgsedi01-bck.tlt;0;0;OK;vbsscriptlolek2sem;scriptérentielle;00:08:48;2111360;759;6;suyesokdiane_dmx4_054;2 Sem
2012-04-13;02:01:02;vbsscript;vbsscript_aix_rgsedi01_data;rgsedi01-bck.tlt;0;0;OK;vbsscript_cumu_2sem;yayative;00:06:35;128512;500;12;suyesokdiane_dmx4_054;2 Sem
2012-04-13;04:00:13;vbsscript;vbsscript_aix_rgsedi01_gtf;rgsedi01-bck.tlt;0;0;OK;vbsscript_cumu_2sem;yayative;01:11:53;5832800;21054;1;suyesokdiane_dmx4_054;2 Sem
2012-04-13;03:00:00;vbsscript;vbsscript_rhe_liabv8;liabv8.tlt;0;0;OK;vbsscriptlolek2sem;scriptérentielle;00 :00:36;85920;148;1;suyesokdiane_dmx4_054;2 Sem
2012-04-13;03:00:00;vbsscript;vbsscript_rhe_liabv9;liabv9.tlt;0;0;OK;vbsscriptlolek2sem;scriptérentielle;00 :01:06;477088;209;1;suyesokdiane_dmx4_054;2 Sem
I've modify this script for my purposes
But it's deleting only column 3Code:Function OK() Const ForReading = 1 Const ForWriting = 2 'On Error Resume Next Dim objFSO, dataArray, clippedArray() Set objFSO = CreateObject("Scripting.FileSystemObject") 'Create an array out of the CSV 'open the data file Set oTextStream = objFSO.OpenTextFile("c:\CSV\2012-04-12.csv", ForReading) Set newFile = objFSO.CreateTextFile("c:\Text.csv", ForWriting) 'make an array from the data file dataArray = Split(oTextStream.ReadAll, vbNewLine) 'close the data file oTextStream.Close x = 0 For Each strLine In dataArray 'Now make an array from each line ReDim Preserve clippedArray(x) clippedArray(x) = Split(strLine,";") ' Delete Field 10 and 3 (highest to lowest) CutColumn = 3 intCount = 0 newLine = "" For Each Element In clippedArray(x) If intCount = UBound(clippedArray(x)) Then EndChar = vbCrLf Else EndChar = ";" End If If intCount <> CutColumn -1 Then newLine = newLine & Element & EndChar End If intCount = intCount + 1 If intCount = UBound(clippedArray(x))+1 Then newFile.Write newLine End If Next Next End Function
How to add option to add also column 7 and 14 and 15 ?
Bookmarks