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

  1. 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.

  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 given 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:

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

Next week we will learn how to work with text files having several fields of data in a record.


No comments:

Post a Comment

Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


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