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

Uploading Comma-Separated Text Data into an Access Table.

Converting the contents of an Access table into a comma-delimited text file (CSV format) makes it easier to transport data over the Internet. Such files can be uploaded back into an Access table or imported into virtually any other database system at the receiving end.

We have already explored the data exporting procedure in an earlier example. You can refer to that discussion, which also demonstrates the use of Access’s GetRows() function. This function efficiently copies the entire table contents into a two-dimensional array in memory with a single operation, making it an excellent tool for preparing data for export.

Microsoft Access already has built-in data Import and Export options.

Here, we will read a Text File having the Employee names, each separated by a comma, and write them into a new Access table. An Image of a sample text file is given below:

Right-click to open the large image in New Tab/Window.

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

Right-click to open the large image in New Tab/Window.

First, create a Text file in Notepad or in any Text editor, and enter some sample data. Each line should contain a few names (people or products) separated by commas. Place each set of items on a new line, as shown in the earlier example.

Important: Do not put a comma at the end of any line.
Save the Text file as Names.txt in your database folder, or in any folder you like.
Open your Access Database to try out the Program given below.
Open the VBA Editing Window and insert a new Standard Module.

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

Creating a 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"; change it to the correct location of your text file. Click in the Code and press the F5 Key to run the Code.
Note: If everything has been completed successfully, the table name should appear in the Navigation Pane. If it is not visible, right-click the Navigation Pane and select Search Bar. Enter NamesList in the Search Bar to locate the table. Click the table name to open it and view its data.

How This Works.

When the program runs for the first time, it creates the NamesList table with a single field, Names. On subsequent runs, the table creation procedure is skipped, and the data from the text file is appended directly to the existing table.
The program reads the first text line (for example, three names separated by commas) into the string variable strH.
We use the Line Input statement here instead of the Input statement.
- Line Input reads the entire line of text, stopping only when it encounters a carriage return (the Enter key at the end of the line).
- Input, on the other hand, treats the comma as a delimiter. So, it would only read the first name and stop at the comma, ignoring the rest of the line.
The Split() Function will keep 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 based on 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 from the 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 the function stops.