delete last column of a csv file (plain text) 1

[lfrazier]

Hello Fellow Tekkies!

I have read a thread resolving this but it does not work for me. I have comma-delimited file with a header and 35 columns.
I need to remove the column 35. How do I do this with vb script?

This is the code I tried:

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set oTextStream = objFSO.OpenTextFile("C:\adp\EPIUWV01.csv")
Set newFile = objFSO.CreateTextFile("C:\adp\EPIUWV01-fixed.csv")
dataArray = Split(oTextStream.ReadAll, vbNewLine)
oTextStream.Close

x = 0
For Each strLine In dataArray
    ReDim Preserve clippedArray(x)
    clippedArray(x) = Split(strLine,",")
    CutColumn = 35
    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"

The result has all data on one line. It is removing the column.

Thanks,

Lloyd

 

[SkipVought]

Hi,

There are no columns in a .csv file. A .csv is a TEXT file.

The 'last column' only exists after a .csv file is imported or opened by an application that creates columns, like Excel.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[HarleyQuinn]

Here's a different way to remove the last delimited value without all the looping:

Dim objFSO
Dim oTextStream
Dim newFile
Dim clippedArray

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set oTextStream = objFSO.OpenTextFile("C:\adp\EPIUWV01.csv")

Set newFile = objFSO.CreateTextFile("C:\adp\EPIUWV01-fixed.csv")

dataArray = Split(oTextStream.ReadAll, vbNewLine)

oTextStream.Close

For Each strLine In dataArray

clippedArray = Split(strLine, ",")

ReDim Preserve clippedArray(UBound(clippedArray) - 2) ' assumes has a trailing comma change to -1 if not

newFile.writeline Join(clippedArray, ",") & "," ' remove & "," if no trailing comma required

Next

wscript.echo "Done!"

It splits each line into an array and then removes the last value(s) (change depending on whether a trailing comma is used) by redim'ing the array and then JOINing it up again. Using .WriteLine negates the need to add a newline to the end of each JOINed value.

Hope this helps

HarleyQuinn
---------------------------------
Black coat, white shoes, black hat, cadillac. The boy's a timebomb!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[lfrazier]

Thank you, HarleyQuinn,

I tried your vbscript code, but get this error:

Script: C:\adp\remove last column.vbs
Line: 20
Char: 1
Error: Out of memory
Code: 800A0007
Source: Microsoft VBScript runtime error

I am running this on an XP Pro svc pak 2 box with Dual Processor and 2 GB of RAM.

Any ideas?

 

[HarleyQuinn]

Sorry, didn't test it with 35 columns...

This works for me:

For Each strLine In dataArray

If strLine <> "" then

clippedArray = Split(strLine, ",")

ReDim Preserve clippedArray(UBound(clippedArray) - 2) ' assumes has a trailing comma change to -1 if not

newFile.writeline Join(clippedArray, ",") & "," ' remove & "," if no trailing comma required

End If

Next

Hope this helps

HarleyQuinn
---------------------------------
Black coat, white shoes, black hat, cadillac. The boy's a timebomb!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[lfrazier]

HarleyQuinn,

your solution is a thing of beauty. Thank you so much for your assistance.

I have just started using javascripts and vbscripts to achieve some basic things.

I would like to learn to do a lot more data manipulation type scripts with VBScript. What is your recommend path to success. Do you recommend a book, website, or whatever.

Thanks!

I definitely give you a star for that one!

 

[lfrazier]

SkipVought,

just wanted to describe why I chose 'Column' as a description of what I am trying to remove. It is database speak for 'Field' or 'Postion' in a record. Early database solutions used delimited text records, usually encrypted in a proprietary manner. Btreive comes to mind. More of a record manager than an actual full blown database.

Sorry that I steered you in the Excel path.

Nonetheless, I appreciate your input.

 

[HarleyQuinn]

Glad I could help, thanks for the star

Hmm, as for recommendations for a learning path I'm not too sure (I've been a VB6 programmer for 10 years, alot of the things I use in VB6 are transferable to VBScript, though I'm not going to recommend this path ), I've got VBScript In A Nutshell (O'Reilly) on my desk but I hardly use it to be honest (though it's not bad when I do) as I only really do scripting to answer questions on here .

Website's I'd look at would be site like this and this. They're for VBScript and the second is for the Windows Scripting Host.

If you're using VBScript in web apps then you might also want to look at the VBScript tutorial on the W3schools website.

Looking at other people's scripts is also useful (and quantifiable, as you know you're making progress when you can understand more and more of them). The FAQ's on this site are good for that.

Hope this helps

HarleyQuinn
---------------------------------
Black coat, white shoes, black hat, cadillac. The boy's a timebomb!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[PHV]

What about this ?

Dim objXL, objWB
Set objXL = CreateObject("Excel.Application")
objXL.DisplayAlerts = False
Set objWB = objXL.Workbooks.Open("C:\adp\EPIUWV01.csv")
objWB.ActiveSheet.Cells(1, 35).EntireColumn.Delete
objWB.SaveAs "C:\adp\EPIUWV01-fixed.csv", 6 '6=xlCSV
objXL.Quit

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886