Results 1 to 6 of 6

Thread: How to parse comma separated text into Multiple Fields?

  1. #1
    Join Date
    Apr 2009
    Posts
    396

    How to parse comma separated text into Multiple Fields?

    Hi friends,
    I want to know about the methods that can be useful for analyzing comma separated text into a text field. And also with that I want to display text in multiple text fields. I think that I have explained in simple language so that there would be no confusion for you guys.!! So please tell me how to parse comma separated text into Multiple Fields?

  2. #2
    Join Date
    May 2008
    Posts
    376

    Re: How to parse comma separated text into Multiple Fields?

    You can use the first method for a text field that contains two words separated by a comma, for example, a field containing a name followed by last name (Joa, Alice). The method uses an expression in a query that has three functions: Instr () to find the comma in the text field and the Left $ () and Right $ () to extract the two parts of the text field. You can use the second method to a text field that contains more than two words separated by commas, for example, a field containing a city, region and country. This method uses two user-defined functions: a function called CountCSWords () to count the number of words separated by commas in the text field and a feature called GetCSWord () to return the word n in the text field.

  3. #3
    Join Date
    Jan 2008
    Posts
    3,758

    Re: How to parse comma separated text into Multiple Fields?

    To analyze a text field that contains two words separated by a comma, follow these steps :
    1. Open any existing database.
    2. Create a table with the following structure :
      Code:
      Table: Parse2Words
             ------------------
             Field Name: Empl
             Data Type: Text
    3. See table Parse2Words in Datasheet view and type the following three records used in the field :
      Code:
      Diaz, Belina
      Peire, Laura 
      Fuller, Andrew
    4. Then you will have to create query, based on the table Parse2Words.
    5. Run the query.

  4. #4
    Join Date
    Apr 2008
    Posts
    4,644

    Re: How to parse comma separated text into Multiple Fields?

    You can write the following query, which is based on the table Parse2Words. you can include this in 4th step that was mentioned by the 'Sam.D'. The following is the code :
    Code:
    Query: QueryTest
           -------------------------------------------------- ----------------
           Field: FirstName: Right $ ([Empl], Len ([Empl]) - InStr (1, [Empl ],",")- 1)
              Show: True
           Field: LastName: Left $ ([Empl], InStr (1, [Empl ],",")- 1)
              Show: True

  5. #5
    Join Date
    May 2008
    Posts
    4,574

    Re: How to parse comma separated text into Multiple Fields?

    To analyze a text field that contains more than two words separated by commas, follow these steps :
    1. Open any database.
    2. View the ParseWords table in Datasheet view, and then type any three records into that.
    3. Create a module and type the following line in the Declarations section if not already there :
      Option Explicit
    4. After writing an appropriate code, compile the module, save it as basp and close it.
    5. Run the query.

  6. #6
    Join Date
    May 2008
    Posts
    3,319

    Re: How to parse comma separated text into Multiple Fields?

    The Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. This method uses two user-defined functions: a function called CountCSWords () to count the number of words separated by commas. The following code would be useful for you if using the method that is explained above by 'Glenny'. The code should be like below :
    Code:
    CountCSWords Function (ByVal S) As Integer
           'Counts the words in a string that are separated by commas.
    
           Sun WC As Integer, Pos As Integer
              If VarType (S) <> 8 Or Len (S) = 0 Then
                CountCSWords = 0
                Exit Function
              End If
              WC = 1
              Pos = InStr (S, "")
              Do While Pos> 0
                WC = WC + 1
                Pos = InStr (Pos + 1, S, "")
              Loop
              CountCSWords = WC
           End Function
    
           GetCSWord Function (ByVal S, Indx As Integer)
           'Returns the nth word in a specific field.
    
           Sun WC As Integer, Count As Integer, SPOS As Integer, As Integer Epos
              WC = CountCSWords (S)
              If Indx <1 Or Indx> WC Then
                GetCSWord = Null
                Exit Function
              End If
              Count = 1
              SPOs = 1
              For Count = 2 To Indx
                SPOs = InStr (spos, S, "") + 1
              Next Count
              Epos = InStr (spos, S, "") - 1
              If Epos <= 0 Then Epos = Len (S)
              GetCSWord = trim (Mid (S, SPOS, EPOS - SPOs + 1))
           End Function

Similar Threads

  1. Does java parse large text file
    By Zeverto in forum Software Development
    Replies: 3
    Last Post: 30-07-2009, 01:26 PM
  2. Powershell script to parse system logs in text file
    By SADIQ in forum Operating Systems
    Replies: 2
    Last Post: 02-06-2009, 07:05 PM
  3. Batch Script Text file parse
    By tator.usenet@gmail.com in forum Windows Server Help
    Replies: 5
    Last Post: 25-03-2009, 02:12 AM
  4. Search multiple sql fields
    By Zindin in forum Software Development
    Replies: 4
    Last Post: 26-02-2009, 09:40 AM
  5. Batch Script to parse lines in text file
    By jntoner1@gmail.com in forum Windows Server Help
    Replies: 8
    Last Post: 28-01-2009, 03:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •