Writing to a csv

[Zach Lau]

Hello. I have an issue that I am trying to sort through. I'm working with several (100's) or different csv files. I came upon a fairly large issue. I have a csv file format with three field names which are the exact same thing. My thought is that I would open the csv file using a DAO connection and write directly to the csv file. I'm not entirely sure if this is possible. I can read, see code...

Option Compare Database
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rcd As New ADODB.Record

Dim PathtoTextFile As String
Dim PathtoMDB As String
Dim myarray() As Variant
Private Sub Form1()
PathtoTextFile = "C:\Users\Zach\Desktop\Zach.csv"
PathtoMDB = "C:\PathtoMDB\"
'CmdOpen.Caption = "Open textfile and display field value"
'CmdInsert.Caption = "Insert textfile values into MDB"
End Sub


Private Sub Command0_Click()
Dim n As Integer
Dim m As Integer
Dim rcd As ADODB.Record
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Users\Zach\Desktop;" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited;"";"
rst.Open "Select * From Zach.csv", cnn, , , adModeReadWrite 'adCmdText
'Debug.Print rst(1)
'For n = 1 To 6
'Debug.Print rst
'Next n

For n = 0 To 2
For m = 0 To 1
MsgBox rst.Fields(m).Name

Next m
m = 0
For m = 0 To 1
MsgBox rst.Fields.Item(m)

Next m
rst.MoveNext
Next n

If rst.Fields(0).Name <> rst.Fields(1).Name Then
rst.Fields(1).Name = "My Change"
Else
End If


rst.Close
cnn.Close
End Sub

I cannot write, however. See code segment...

If rst.Fields(0).Name <> rst.Fields(1).Name Then
rst.Fields(1).Name = "My Change"
Else
End If

I get the error "Can't assign to a read only property." I'm really wanting to do something like

For n = 1 To fieldcount

On Error Next, etc

If field.name = field(n+1).name Then

field(n+1).name = field(n+1).name & "1"

Else

End If

 

[boblarson]

I think you may be going about this in too complicated of a way.

Are you wanting to APPEND data to the CSV file? Or what? What is an example of data from the CSV file, where does it come from, and what are you trying to manipulate in it?

 

[Zach Lau]

I am in access vba. I have a batch of csv files which come in many formats. Currently I have a code that imports the csv files into a temptable, appends to a temporary running table, renames the imported csv file and eventually the running table and exports to several csv files. The code sorts through the multiple csv file formats.

The problem is that one of the formats of files that I receive, which is out of my control, has three column or field names which are exactly the same. This throws off everything.

I really want to be able to look at each of the csv files and change the field names if they are the same. This might be a hassle, but would make the rest of my 500 lines of code work well.

 

[Zach Lau]

So I work for a company that does data management for public school testing for most of the state of Indiana. The files are downloaded from a company called Acuity. They contain student information and scores. The columns might be different depending on the type of test and other factors, some of which cannot be determined. One example is something like the following with about 50 more columns.

District Last Name First Name Subject Standard Cluster Cluster Cluster Score

It contains records for about 5000 students.