Import a Comma Separated Text File Into ONE Access Column

[jo15765]

I have a text file that I need to import into access, and it has data like 1,3,5,22,44,18

When I try to import the data it creates a column for each value after the column. What I am wanting is to import each value into ONE column.



How would I do this with access vba?

[Micron]

I'd say read it as a text file and dump the contents into a record. Something like this (should be ok even though it's Excel)
https://www.automateexcel.com/vba/re...e-into-string/

[jo15765]

I'd say read it as a text file and dump the contents into a record. Something like this (should be ok even though it's Excel)
https://www.automateexcel.com/vba/re...e-into-string/

okay - i understand the ReadTextFile() method, and see that my data would be stored in the variable strText

How do I then use a recordset to save to a table?

[Welshgasman]

Use Tab delimited?

[jo15765]

Use Tab delimited?

the file is received as a comma delimited :/

[Welshgasman]

Yes, but if you say it is Tab delimited all the data comes in as one field.
I imported it manually, but the logic should be the same, especially if you have the table already created?

I am assuming this is a regular occurrence if you want to do it via VBA?

[jo15765]

is there a way to convert from a comma seperated to tab seperated file?

[Welshgasman]

is there a way to convert from a comma seperated to tab seperated file?

Yes, via Excel. Save as Tab delimited.

[orange]

jo15765,

Can you tell us a little more about your project? It would help potential responders if there was more context. This is 1 csv file, but it must be used in conjunction with other data. Can you give us the 30,000 ft overview?

[Micron]

How do I then use a recordset to save to a table?

Save what to a table? I interpreted your post as wanting to save one huge blob into one table record in one field. Now it is to be saved as delimited in some fashion?

[orange]

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

' ----------------------------------------------------------------
' 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