Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



How to parse comma separated text into Multiple Fields?

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 12-03-2010
Member
 
Join Date: Apr 2009
Posts: 385
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?

Reply With Quote
  #2  
Old 12-03-2010
Member
 
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.
Reply With Quote
  #3  
Old 12-03-2010
Member
 
Join Date: Jan 2008
Posts: 3,751
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.
Reply With Quote
  #4  
Old 12-03-2010
Member
 
Join Date: Apr 2008
Posts: 4,641
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
Reply With Quote
  #5  
Old 12-03-2010
Member
 
Join Date: May 2008
Posts: 4,565
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.
Reply With Quote
  #6  
Old 12-03-2010
Member
 
Join Date: May 2008
Posts: 3,313
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
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to parse comma separated text into Multiple Fields?"
Thread Thread Starter Forum Replies Last Post
Does java parse large text file Zeverto Software Development 3 30-07-2009 02:26 PM
Powershell script to parse system logs in text file SADIQ Operating Systems 2 02-06-2009 08:05 PM
Batch Script Text file parse tator.usenet@gmail.com Windows Server Help 5 25-03-2009 03:12 AM
Search multiple sql fields Zindin Software Development 4 26-02-2009 10:40 AM
Batch Script to parse lines in text file jntoner1@gmail.com Windows Server Help 8 28-01-2009 04:38 AM


All times are GMT +5.5. The time now is 01:00 AM.