Tidy Up/Split Text in One Field

[JamesBond]

Hi



I imported a large csv file into a table called "tbMasterList". In the table there is a field called "Languages".

The format for the text in the Language field is very messy at the minute and I am trying to tidy it up. There could be one, two, three or four languages in the one field, in some cases the words are together without spaces, here is an example;

English
EnglishSpanish
English Italian
Basic English German
BasicEnglishGerman
GermanFrenchEnglish
Spanish

I would like to tidy the field up as follows;

English
English, Spanish
English, Italian
Basic English, German
Basic English, German
German, French, English
Spanish

However if the use of the phrase "Basic English" complicates matters when it comes to putting in commas then I am happy to go with the following format;

English
English Spanish
English Italian
Basic English German
Basic English German
German French English
Spanish

Any help would be appreciated, thank you in advance.

[Bob Fitz]

Not quite what you wanted, but until some else passes by, try:
SELECT [a ] & [b] & [c] & [d] & [e] AS Result, tblMasterList.Languages, tblMasterList.Languages AS L, IIf(InStr(1,[L],"Basic")>0,Mid([L],1,5) & " ") AS a, IIf(InStr(1,[L],"English")>0,Mid([L],InStr(1,[L],"English"),7) & " ") AS b, IIf(InStr(1,[L],"Spanish")>0,Mid([L],InStr(1,[L],"Spanish"),7) & " ") AS c, IIf(InStr(1,[L],"German")>0,Mid([L],InStr(1,[L],"German"),6) & " ") AS d, IIf(InStr(1,[L],"French")>0,Mid([L],InStr(1,[L],"French"),6) & " ") AS e
FROM tblMasterList;

[Bob Fitz]

I can't help feeling there is a better way, but this seems to give the required result.

SELECT IIf(Right([a ] & [b] & [c] & [d] & [e],2)=", ",Left([a ] & [b] & [c] & [d] & [e],Len([a ] & [b] & [c] & [d] & [e])-2),"") AS Result, tblMasterList.Languages, tblMasterList.Languages AS L, IIf(InStr(1,[L],"Basic")>0,Mid([L],1,5) & ", ") AS a, IIf(InStr(1,[L],"English")>0,Mid([L],InStr(1,[L],"English"),7) & ", ") AS b, IIf(InStr(1,[L],"Spanish")>0,Mid([L],InStr(1,[L],"Spanish"),7) & ", ") AS c, IIf(InStr(1,[L],"German")>0,Mid([L],InStr(1,[L],"German"),6) & ", ") AS d, IIf(InStr(1,[L],"French")>0,Mid([L],InStr(1,[L],"French"),6) & ", ") AS e
FROM tblMasterList;

[JamesBond]

Thanks Bob, That works well for the given list of languages, but in reality the list is not confined to the languages I gave as an example. The list could include any language in the world. Maybe the better way to go is to just separate each word at a capital letter, for example

English
GermanDutch
Italian French
RussianIrishSpanish
BasicEnglishIrish
TurkishEnglishGermanIrish

The list would end up looking like this

English
German Dutch
Italian French
Russian Irish Spanish
Basic English Irish
Turkish English German Irish

Can you tell me what the sql is to separate at a Capital letter, please.

Thank you in advance.

[Bob Fitz]

Can you tell me what the sql is to separate at a Capital letter, please.

I don't think there is any. I think you will need a user defined function to achieve your goal. I will post back if I am able to offer something in this regard.

[orange]

Here's a small routine to simulate the issue and to show a solution.
You don't have to parse the input data -necessarily. If you have a table with all the languages you
expect to work with (ISO-639), you just have to see if the incoming record has that language.

'---------------------------------------------------------------------------------------
' Procedure : Lang
' Author    : Jack
' Date      : 13/04/2014
' Purpose   : Procedure to simulate an incoming file with a string of langauge names
' with inconsistent formatting. Compare each incoming string to see if it contains one
' or more standard language names. If so report a standard language(s) in a standard format
'
'---------------------------------------------------------------------------------------
'
Sub Lang()
          Dim has As String   'holding languages for this record
10       On Error GoTo Lang_Error

20        has = ""
          Dim i As Integer    'counter
          Dim j As Integer    'counter
          
          'lng is a list of all languages involved
          '
          Dim lng(10) As String
30        lng(0) = "English"
40        lng(1) = "French"
50        lng(2) = "German"
60        lng(3) = "Italian"
70        lng(4) = "Dutch"
80        lng(5) = "Japanese"
90        lng(6) = "Chinese"
100       lng(7) = "Swedish"
110       lng(8) = "Spanish"
120       lng(9) = "Portugese"
130       lng(10) = "Russian"
          
        'incoming   simulates the records from the csv file
        '
        'You could read the csv file into a table and process it 
        '
          Dim incoming(5) As String
140       incoming(0) = "EnglishFrench German"
150       incoming(1) = "GermanFrenchEnglish"
160       incoming(2) = "German, French, English"
170       incoming(3) = "Basic English,  Spanish Dutch"
180       incoming(4) = " Italian English"
190       incoming(5) = " Italian Basic English"
          
          'Loop through the csv data to see which languages this record uses/has
          
200       For j = 0 To UBound(incoming)
210           For i = 0 To UBound(lng)
220               If InStr(incoming(j), lng(i)) > 0 Then
230                   has = has & lng(i) & ", "
240               End If
250           Next i
260           Debug.Print "incoming(" & j & ")" & incoming(j) & "  contains ( " & Mid(has, 1, Len(has) - 2) & ") "
            '
            ' Modify the debug.print to output to the appropriate table/record as needed
            '
270           has = ""              'reset the this record has languages variable
280       Next j

290      On Error GoTo 0
300      Exit Sub

Lang_Error:

310       MsgBox "Error " & Err.number & " on line " & Erl & " (" & Err.Description & ") in procedure Lang of Module UtterAccessRelated"
End Sub

Sample output:

incoming(0)EnglishFrench German  contains ( English, French, German) 
incoming(1)GermanFrenchEnglish  contains ( English, French, German) 
incoming(2)German, French, English  contains ( English, French, German) 
incoming(3)Basic English,  Spanish Dutch  contains ( English, Dutch, Spanish) 
incoming(4) Italian English  contains ( English, Italian) 
incoming(5) Italian Basic English  contains ( English, Italian)

Hope it's helpful

[Bob Fitz]

JamesBond

You may like to try this:
Copy the function below in to a General module.

Public Function fnSplitStrByCaps(strToSplit As String)Dim strChar As String
Dim intLen As Integer
Dim intCapPos As Integer
Dim strReturn As String
Dim intLastCapPos As Integer
 
  intLen = Len(strToSplit)
  
  For intCapPos = intLen To 1 Step -1
    strChar = Mid(strToSplit, intCapPos, 1)
    If Asc(strChar) >= 65 And Asc(strChar) <= 90 Then
      strReturn = strReturn & ", " & Mid(strToSplit, intCapPos, IIf(intLastCapPos = 0, Len(strToSplit) + 1, intLastCapPos) - intCapPos)
      intLastCapPos = intCapPos
    End If
  Next
    
    If InStr(1, strReturn, ", Basic") And InStr(1, strReturn, "English") Then
      strReturn = Replace(strReturn, ", Basic", "")
      strReturn = Replace(strReturn, "English", "Basic English")
    End If
  
  fnSplitStrByCaps = Right(strReturn, Len(strReturn) - 2)
End Function

Now create a query using:

SELECT fnSplitStrByCaps([Languages]) AS Result
FROM tblMasterList;

[JamesBond]

Bob that worked perfectly. Thank you very much for your help, it is really appreciated.

[Bob Fitz]

Bob that worked perfectly. Thank you very much for your help, it is really appreciated.

Good. Glad to be of help
However, since my last post, I have been giving it some more thought and I have a concern. In the sample data you posted, "Basic English" was one of the languages. Could any other language be "Basic". eg: "Basic French" or "Basic German". The code will not handle these correctly. Post back if that is the case and I will try to alter the code accordingly.