Delete Column from cvs file via VBA (Access 2010)

[airwolf09]

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
[pre]
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[/pre]

But the output to the new.csv looks like this
[pre]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[/pre]

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
[pre] 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[/pre]

Next
End Sub

Thank You

 

[DjangMan]

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

 

[SkipVought]

@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,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[airwolf09]

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
[pre]'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
[/pre]
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

 

[SkipVought]

If you must, forum705.

This is not an Access forum.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[airwolf09]

Thanks