Uploading Comma Separated Text Data into Access Table
Converting Access table contents into a comma-delimited text form makes it easier to transport the data through Internet. It can be uploaded into 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 of this method combined with the usage of 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 have in-built data Import and Export options.
Here, we will learn how to read a Text File containing names of Employees, each separated with comma, and write them into a new Access table. Image of a sample text file is given below:
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 (few names of people or products), separated with comma. You may create names on separate lines with different number of items on each line. 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 give below.
- Open VBA Editing Window and Insert a new Standard Module.
- Copy and Paste the following VBA Code into the Module and save it:
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 change for the correct location of your text file. Click somewhere in the middle of the Code and press F5 Key to Run the Code.
Note:If every thing 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.
- When the program runs for the first time, it creates the NamesList Table, with a single field: Names. Subsequent runs of the program ignores 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 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 a 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 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 text file is reached all files are closed and stops the function.
Next week we will learn how to work with text file having several fields of data in a record.