Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing Text values (Access 2000)

    I imported a field that contains 4 columns of values. The only split between the values are spaces. In Excel I would have used Data text to Columns and would have ticked the spaces as delimiter and Continues spaces.

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Text values (Access 2000)

    It is often easiest to use the method you describe in Excel, and import the result into Access.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Text values (Access 2000)

    Hans I'm still busy with that large Word import and my records count is 2 Milj. I need to do something in Access.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Text values (Access 2000)

    You can create a query in Access to split it. But it will be extremely slow on your table.

    Create the following custom function in a standard module:

    Public Function SplitPart(aString, n As Long)
    SplitPart = Null
    On Error Resume Next
    SplitPart = Split(aString, " ")(n - 1)
    End Function

    In the query, add columns as follows:

    Part1: SplitPart([Name_of_field], 1)
    Part2: SplitPart([Name_of_field], 2)
    Part3: SplitPart([Name_of_field], 3)
    Part4: SplitPart([Name_of_field], 4)

    Replace Part1 etc. by the names you want the columns to have, and Name_of_field by the name of the existing field.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Text values (Access 2000)

    Thanks for the code. It works for some of the fields. My problem is that not all the fields have the same number of spaces inbetween the values. Is it possible to split on each time there is a value. Almost like stepping through the string and checking for a value and as soon as new value then create new field?

    Thanks for any ideas

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Importing Text values (Access 2000)

    <P ID="edit" class=small>(Edited by MarkD on 08-Dec-03 04:05. Added comment.)</P>If there are extra spaces between items, the Split function will not return correct results. Access does not have the equivalent of the Excel TRIM worksheet function, which removes all extra spaces between items, as well as leading & trailing spaces. You could use a custom function like this example for this purpose:

    Public Function TrimAll(ByVal strTxt As String) As String

    ' Trim any extra spaces between words in text in addition to leading/trailing spaces
    ' Like Excel TRIM worksheet function

    strTxt = Trim$(strTxt)

    ' Replace CR/LF with single space:
    strTxt = Replace(strTxt, Chr$(13) & Chr$(10), Chr$(32), , , vbBinaryCompare)

    ' Then replace any double spaces with single space
    Do Until InStr(1, strTxt, Chr$(32) & Chr$(32), vbBinaryCompare) = 0
    strTxt = Replace(strTxt, Chr$(32) & Chr$(32), Chr$(32), , , vbBinaryCompare)
    Loop

    TrimAll = strTxt

    End Function

    Test results:
    <pre>? TrimAll("A B C D")
    A B C D
    ? TrimAll("A B C D")
    A B C D
    ? TrimAll("A B C D")
    A B C D
    ? TrimAll(" A B C D ")
    A B C D</pre>


    There may be a simpler way to do this.... Once you get rid of extra spaces you can use Split function to populate an array with the string's contents, with single space as delimiter. Note the above function also tests for carriage returns, you can comment that line out if not applicable.

    PS: If you are processing zillions of records, this will not be very fast. The number of loops required to trim extra spaces is proportional to the length (in characters) of the largest space between items in string.

    HTH

  7. #7
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Text values (Access 2000)

    Thanks Mark

    Together with Hans code & yours it works great

Posting Permissions

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