If I understand right,
- you have a text file in CSV format
- there will be a minimum of 8 rows and a maximum of 80 rows
- you want to convert the rows to columns
- the fields are : date1, amount1, description1, test1, date2, amount2, description2, test2, date3, amount3, description3, test3, ....
If there are 80 lines (dimensions??) per CSV file, then there would be 320 fields (minimum). Office 2010 has a maximum of 255 fields per table. Bit of a problem.....
How you wrote your code is a little unusual , but it seems to work
I changed your code to write the rows to columns, but it looks like you will run out of fields in the table...
Code:
Option Compare Database
Option Explicit 'should always have this line
Dim db_myDatabase As DAO.Database
Dim rs_recordset As DAO.Recordset
Private Sub Form_Load()
Set db_myDatabase = OpenDatabase("c:\temp\mydatabase.accdb")
Set rs_recordset = db_myDatabase.OpenRecordset("myTable")
End Sub
Private Sub Form_Unload(Cancel As Integer)
rs_recordset.Close
db_myDatabase.Close
Set rs_recordset = Nothing
Set db_myDatabase = Nothing
End Sub
Private Sub Command22_Click()
Dim arrData
Dim s As String
Dim i As Integer
Dim FF As Integer 'next file number available for use by the Open statement
i = 1 ' initalize counter
FF = FreeFile 'Use FreeFile to supply a file number that is not already in use.
Open "C:\temp\Card.txt" For Input As #FF
'create a new record and move to it
rs_recordset.AddNew
rs_recordset.Update
rs_recordset.Bookmark = rs_recordset.LastModified
Do While Not EOF(FF)
Line Input #FF, s
arrData = Split(s, ",")
rs_recordset.Edit
rs_recordset("date" & i) = arrData(0)
rs_recordset("amount" & i) = arrData(1)
rs_recordset("description" & i) = arrData(2)
rs_recordset("test" & i) = arrData(3)
rs_recordset.Update
i = i + 1 'increment counter
' If i > 80 Then
' 'do something
' End If
'clear the array - just in case
arrData(0) = Empty
arrData(1) = Empty
arrData(2) = Empty
arrData(3) = Empty
Loop
Close #FF 'close text file
MsgBox "Done"
End Sub
Let me know if I missed something....
Edit: June7 beat me again. I've got to learn how to type faster...