Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Delete columns in a csv file 2

Status
Not open for further replies.
tonyflora63

tonyflora63

MIS
May 22, 2003
42
US
Hi does anyone know how to deleted columns in a csv file?

Thanks in advance.
 
Sort by date Sort by votes
What is creating the csv file?
What are you using to read the file?
wjwjr

This old world keeps spinning round - It's a wonder tall trees ain't layin' down
 
Upvote 0 Downvote
So this is coming from and exchange server util of some sort.
The easiest way to get rid of the column would be to read it into excel, delete the column you dont want, and save as - whatever format you need.
If you are trying to do this with vbscript mabee you should consider vba/excel to automate the process.
wjwjr

This old world keeps spinning round - It's a wonder tall trees ain't layin' down
 
Upvote 0 Downvote
Assume you have a CSV like this:

this,that,the,other,thing
me,you,him,her,us

And let's assume you want to remove column 3. Then the following code would do the job.

Code: 
'==========================================================================
'
' NAME: RemoveColumnFromCSV.vbs
'
' AUTHOR: Mark D. MacLachlan , The Spider's Parlor
' URL: [URL unfurl="true"]http://www.thespidersparlor.com[/URL]
' 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"

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at
Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.
 
Upvote 0 Downvote
In what way did my suggestion in thread329-1433373 not suit?
 
Upvote 0 Downvote
Remou, your suggestion is a fine one if you have Excel installed on the server.

My solution does not require the installation of Excel. Also, jet databases are not supported on 64bit systems.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at
Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.
 
Upvote 0 Downvote
markdmac
I take your point about 64 bit systems, but the Excel part of my suggestion is simply added to suit the OPs original enquiry and not part of editing the CSV.
 
Upvote 0 Downvote
... also, tonyflora63 did not respond to that post, and I am curious as to why.
 
Upvote 0 Downvote
A star for the code you posted markdmac. There many ways to get to the same place, I am reading and trying your code and learning new ways....
wjwjr

This old world keeps spinning round - It's a wonder tall trees ain't layin' down
 
Upvote 0 Downvote
Mark

Your code works with the exception that the remaining data is moved under the incorrect columns.

Thanks

Tony
 
Upvote 0 Downvote
Good catch Tony, I neglected to account for the zero element in the array.

Changing this line:
If intCount <> CutColumn Then

To this will correct the error.
If intCount <> CutColumn -1 Then

Thanks for pointing out the discrepancy.



I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at
Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.
 
Upvote 0 Downvote
This is cool i was wondering what if i wanted to delete more then one column at a time say i wanted to delete columns 6,7,9,32,67,99

how would you add this to your script? including the If intCount <> CutColumn -1 Then

change
 
Upvote 0 Downvote
Just work backwards so the column numbers don't change on you. Delete the 99 first, then 67, then 32 etc.

If you were to delete column 6 first, then column 7 becomes column 6 and it is a headache to track.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at
Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.
 
Upvote 0 Downvote
Here is how i have edited your code.. what i am doing wrong... because it is not deleting the columns i want deleted

Code: 
'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:\staging\test.csv")
Set newFile = objFSO.CreateTextFile("C:\staging\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,",")
    CutColumn = 53
    CutColumn = 52
    CutColumn = 51
    CutColumn = 50
    CutColumn = 49
    CutColumn = 48
    CutColumn = 47
    CutColumn = 45
    CutColumn = 44
    CutColumn = 43
    CutColumn = 42
    CutColumn = 41
    CutColumn = 40
    CutColumn = 39
    CutColumn = 37
    CutColumn = 36
    CutColumn = 35
    CutColumn = 34
    CutColumn = 33
    CutColumn = 32
    CutColumn = 31
    CutColumn = 30
    CutColumn = 29
    CutColumn = 28
    CutColumn = 27
    CutColumn = 26
    CutColumn = 25
    CutColumn = 24
    CutColumn = 23
    CutColumn = 22
    CutColumn = 18
    CutColumn = 17
    CutColumn = 16
    CutColumn = 14
    CutColumn = 13
    CutColumn = 12
    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
WScript.Echo "Done"
 
Upvote 0 Downvote
Also, jet databases are not supported on 64bit systems.
Click to expand...
Where did this come from? As far as I know it isn't true.

There are real problems trying to use 32-bit Jet from a 64-bit process, but then again WSH and VBScript are still 32-bit as well aren't they? Even in Win2008?

If there are changes here I'd like to know more about them. So far I haven't had a single problem myself though.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Rick_Stanich
  • Locked
  • Question
Save xls file to cvs file 1
Replies
3
Views
374
Rick_Stanich
Rick_Stanich
bdwilcox
  • Locked
  • Question
Trying to adapt VBScript to search if machine is in an AD group 6
Replies
8
Views
1K
SoftwareRT
SoftwareRT
S
  • Locked
  • Question
Need to submit page when clicking a checkbox
Replies
0
Views
309
shbr5413
S
Palito1916
  • Locked
  • Question
Disable multiple computer accounts using TXT file with computer names
Replies
2
Views
365
Palito1916
Palito1916
PiotrMP006
  • Locked
  • Question
How to quickly check if a file has 1 or more lines of text?
Replies
1
Views
349
combo
combo

Part and Inventory Search

Sponsor

Back
Top