INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Delete Column from cvs file via VBA (Access 2010)

Delete Column from cvs file via VBA (Access 2010)

(OP)
Hello. I am trying to figure out why the code below does not produce a new row of data. For example the old.csv shows
A B   C    D          E
1 JOE DOE  MALE       UNF
2 MOE DOE  MALE       UF
3 FOE DOE  FEMALE     SPACE_HERE
4 JOE DOE  SPACE_HERE CAL 

But the output to the new.csv looks like this
A B   C   D    E F   G   H    I J   K   L      M N   O   P
1 JOE DOE MALE 2 MOE DOE MALE 3 FOE DOE FEMALE 4 JOE DOE SPACE_HERE 

The output is what I need but the writing to the new.csv is not in the original format row by row.

Please provide some help.

Sub Test()
'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:\File\Old.csv")
Set newFile = objFSO.CreateTextFile("C:\File\New.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 = 5
    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 Sub


Thank You

RE: Delete Column from cvs file via VBA (Access 2010)

What is the CSV file using for end of line characters? vbNewLine or vbCrLf?

RE: Delete Column from cvs file via VBA (Access 2010)

@airwolf09,

You've been around here at Tek-Tips a tad more than 4 years, posted half a dozen threads, have received lots of good tips, yet never have responded with a little purple star that other members can see and identify responses worthy of note. It's part of Tek-Tips.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Delete Column from cvs file via VBA (Access 2010)

You can accomplish this in Excel real easy.

1) IMPORT the .csv into a new sheet

2) DELETE the unwanted column(s)

3) SaveAS a .csv text file

If you want code, turn on your Macro Recorder.

Simple & Easy.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Delete Column from cvs file via VBA (Access 2010)

(OP)
Yes, I understand the Excel part but I am not using Excel. I am using Access 2010. I am not trying to open the csv. I am trying to make run the process in the background. I think I figured it out. Here it is what I was able to achieve.

Sub Test()
'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:\File\Old.csv")
Set newFile = objFSO.CreateTextFile("C:\File\New.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 = 5
    intCount = 0
    NewLine = vbCrLf
    For Each Element In clippedArray(x)
        If intCount = UBound(clippedArray(x)) Then
            'REMOVED LINE OF CODE
        Else
            EndChar = ","
        End If
    
        If intCount <> CutColumn - 1 Then
           NewLine = NewLine & Element & EndChar
        End If
        intCount = intCount + 1
        If intCount = UBound(clippedArray(x)) Then 'Removed + 1 from line If intCount = UBound(clippedArray(x)) + 1 Then
            newFile.Write NewLine
        End If
    Next 
 
Next
End Sub

This code would leave a empty line on the top and I removed it with the code below

'Delete first line on csv file

Dim iFile As Integer
Dim sData As String
iFile = FreeFile
Open "C:\File\New.csv" For Binary Access Read As iFile
sData = Space(LOF(iFile))
Get #iFile, , sData
Close iFile

sData = Mid(sData, InStr(sData, vbCrLf) + 2)
Kill "C:\File\New.csv"

iFile = FreeFile
Open "C:\File\New.csv" For Binary Access Write As iFile
Put #iFile, , sData

Close iFile

RE: Delete Column from cvs file via VBA (Access 2010)

(OP)
Thanks

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close