Uploading Comma Separated Text Data into Access Table ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

Converting Access table contents into a comma-delimited text form makes it easier to transport the data through the Internet. It can be uploaded into the Access table or into any database system at the receiving end. We have already tried the data exporting procedure earlier. You can find the link to this method combined with the usage of the GetRows() Function of Microsoft Access here. The GetRows() Function copies the entire table contents into memory in a two-dimensional Array, in one swift operation.

Microsoft Access already has inbuilt data Import and Export options.

Here, we will learn how to read a Text File containing the names of Employees, each separated with a comma, and write them into a new Access table. An Image of a sample text file is given below:

An Image of the output Access Table, with a single column, is given below for reference.

First, create a text file, using Notepad or any other Text Editor Program, with some sample data (a few names of people or products), separated with a comma. You may create names on separate lines with different sets of items on each line as shown above. Don't put a comma at the end of the line.
Save the Text file with the name: Names.txt, in your database folder, or in any folder you like.
Open your Access Database to try out the Program given below.
Open VBA Editing Window and Insert a new Standard Module.

Copy and Paste the following VBA Code into the Module and save it:

Creating Table From Comma Separated Text File.

Public Function NamesList()
'-----------------------------------------------------
'Utility: Creating Access Table from
'       : comma separated text data.
'Author : a.p.r.pillai
'Date   : April 2016
'Rights : All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------
Dim db As Database, rst As Recordset, tdef As TableDef
Dim strH As String, fld As Field, j As Integer
Dim x_Names As Variant, tblName As String, fldName As String
Dim txtfile As String

On Error GoTo NamesList_err

tblName = "NamesList"
fldName = "Names"
txtfile = "d:\mdbs\Names.txt" 'Change this line to correctly point where your text file is saved.

'create the NamesList table with a single field: Names
Set db = CurrentDb
Set tdef = db.CreateTableDef(tblName)
With tdef
  .Fields.Append .CreateField(fldName, dbtext, 50)
End With
db.TableDefs.Append tdef
db.TableDefs.Refresh

'Open the NamesList table to write names with the text file
Set rst = db.OpenRecordset(tblName)

'Open the Names.txt file to read text data
Open txtfile For Input As #1
'setup a loop to read the data till the end-of-file reached
Do While Not EOF(1)
'read the first line of names, separated with commas and
'terminated with carriage return (Enter key),into String variable strH
Line Input #1, strH
'extract each name separated with comma and load into the Array variable x_Names
x_Names = Split(strH, ",")

'Read each name from array elements
'and write into the NamesList table
With rst
For j = 0 To UBound(x_Names)
    .AddNew
    !Names = x_Names(j)
    .Update
Next
End With
'Repeat till the End-Of-Text File is reached
Loop
close #1

NamesList_Exit:
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
Exit Function

NamesList_err:
If Err = 3010 Then 'Table already exists
  'continue executing from the next line onwards
  Resume Next
Else
  MsgBox Err & ": " & Err.Description, "NamesList()"
  Resume NamesList_Exit
End If
End Function

Find this line in the vba code: txtfile = "d:\mdbs\Names.txt" and make the change in the correct location of your text file. Click somewhere in the middle of the Code and press the F5 Key to Run the Code.
Note: If everything went on well, then you will find the Table NamesList in the Navigation Pane. If you could not find it, then right-click on the top bar of the Navigation Pane and select Search Bar. Type NamesList in the Search Bar to bring up the table in view. Click on it to open and view the data.

How This Works.

When the program runs for the first time, it creates the NamesList Table, with a single field: Names. Subsequent runs of the program ignore the table creation action and simply Appends the data to the existing table from the text file.
The Program reads the first text line (three names separated with commas) into the string variable strH. Here, we have used the Line Input Statement, rather than Input, to read the entire line of text, the Carriage Return character (Enter Key) at the end of the line is considered as a line terminator. The Input statement will only read the first name because the comma after the name is considered as an input terminator character.
The Split() Function will break up the names separately and load them into the Variant Array Variable: x_Names.
The Array variable x_Names will be automatically dimensioned/re-dimensioned, by the Split() Function, for the number of items on the input line, and each item is loaded into the elements of the array.
In the next step, a new record is added to the Access Table for each item loaded into the array and written to the table one by one from array elements.
This process is repeated for all the lines in the text file. When the end of the text file is reached all files are closed and stops the function.