jo15765,
I interpreted your requirement to:
-you have a text file that has numbers separated with commas (comma separated variables)
-you want to read that file
-for each value create a new record in an Access table.
I mocked up a sample that may help.
Input file
Name:"C:\users\jp\documents\documents_Lenovo\docum ents\samplecsv.txt"
Values:12,23,24,26,32,43,54,34,58
Output table
Name: "joSample"
Fields: RecId autonumber
RecVal number
Records:
recid |
recval |
1 |
12 |
2 |
23 |
3 |
24 |
4 |
26 |
5 |
32 |
6 |
43 |
7 |
54 |
8 |
34 |
9 |
58 |
Code:
' ----------------------------------------------------------------
' Procedure Name: readcsv
' Purpose: Read a comma separated file and use the values between commas as records in atable
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 28-Jul-23
' ----------------------------------------------------------------
Sub readcsv()
Dim SourceFileName As String 'full name of source txt file with csv data
Dim db As DAO.Database
Dim rsOut As DAO.Recordset
Dim strLine As String
Dim aryLine() As String '
Dim SourcefileNum As Integer
Dim commaCount As Long
Dim sql As String
10 Set db = CurrentDb
20 sql = "Create Table joSample (recid autoincrement not null primary key, recval integer null)" 'create table joSample
30 db.Execute "Drop table joSample" 'remove table joSample if it exists
40 db.Execute sql
50 Debug.Print "Start: "; Now()
60 SourceFileName = "C:\users\jp\documents\documents_Lenovo\documents\samplecsv.txt"
70 SourcefileNum = FreeFile()
80 Open SourceFileName For Input As #SourcefileNum
90 Set db = CurrentDb
100 Set rsOut = db.OpenRecordset("joSample")
'Loop through the source file, parse it, and create records in table joSample
110 While Not EOF(SourcefileNum)
120 Line Input #SourcefileNum, strLine
130 aryLine = Split(strLine, ",") 'split the strLine into array elements
140 For commaCount = LBound(aryLine) To UBound(aryLine)
150 Debug.Print aryLine(commaCount)
160 rsOut.AddNew
170 rsOut!RecVal = aryLine(commaCount)
180 rsOut.Update
190 Next
200 Wend
210 rsOut.Close
220 db.Close
230 Debug.Print "Finished: "; Now()
End Sub
Debug.Print output to immediate window
Start: 28-Jul-23 4:34:58 PM
12
23
24
26
32
43
54
34
58
Finished: 28-Jul-23 4:34:58 PM