Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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.

  1. 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.
  2. Save the Text file with the name: Names.txt, in your database folder, or in any folder you like.
  3. Open your Access Database to try out the Program give below.
  4. Open VBA Editing Window and Insert a new Standard Module.
  5. 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
    '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)
        !Names = x_Names(j)
    End With
    'Repeat till the End-Of-Text File is reached
    close #1
    Set rst = Nothing
    Set db = Nothing
    Exit Function
    If Err = 3010 Then 'Table already exists
      'continue executing from the next line onwards
      Resume Next
      MsgBox Err & ": " & Err.Description, "NamesList()"
      Resume NamesList_Exit
    End If
    End Function
  6. 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.


No comments:

Post a Comment


Your email address:

Delivered by FeedBurner


Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.


Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Accesstips Menus and Toolbars MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery Array List Boxes Command Buttons Emails and Alerts Property Query Combo Boxes Custom Wizards DOS Commands Data Objects VBA ms-access functions msaccess functions msaccess graphs msaccess reporttricks Calculation Class Module Data Type msaccessprocess security advanced Access Security Custom Functions Macros Menus Object Reference Report Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Expression Field Type Fields Form Form Instances Join Methods Microsoft Numbering System Records Security Split SubForm Table Utility Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Controls Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Base Class and Derived Object Variants

Last week we have tried an example as how to pass a Base Class Object, through the Set Property Procedure,  to become part of the Object in...


Blog Archive

Recent Posts