text cleanup

3 posts / 0 new
Last post
CB's picture
CB
Offline
Joined: 10/19/2012 - 04:54
text cleanup

what does the script do?

you can choose a idea table from your working directory and than choose a char column from this table.
the script will create a new column (you have to give it a name and datalength) in the table.
the new column is prepared according to a self-assigned formula.

maybe the dialog e.g. could be extended with option buttons for different formulas...

in the example a very easy formula is used...

happy coding...
chris

'example_ColumnClearing
' cb
Dim aFileName() AS String
Dim aColumnName() AS String

Begin Dialog dlgClear 16,35,260,190,"ColumnClearing", .Enable
  PushButton 20,158,60,20, "Ok", .ok 'PB1
  PushButton 180,159,60,20, "Abort", .abbruch 'PB2
  Text 10,10,200,8, "This script create in the file (1) a new column (3),"
  Text 10,20,200,8, "which contains the content from charColumn (2) but was"
  Text 10,30,200,8, "prepared as specified"
  Text 10,50,270,8, "1. Choose File:"
  DropListBox 10,60,240,80, aFileName(), .DrpList1
  Text 10,80,270,8, "2. Choose char column:"
  DropListBox 10,90,240,80, aColumnName(), .DrpList2
  Text 10,105,240,8, "________________________________________________________________________________"
  Text 10,120,200,8, "3. New column:"
  Text 10,130,100,8, "New ColumnNname:"
  TextBox 10,140,104,10, .TextBox1
  Text 150,130,100,8, "New ColumnLength"
  TextBox 150,140,20,10, .TextBox2
End Dialog
Dim sFileName As String
Dim sColumnName As String
Dim sTargetColumn As String
Dim sTargetColumnLength As String

Sub Main
        Dim button As Integer
        Dim v As Integer

        v = 0

        Do
                Dim Dlg0 As dlgClear
                button = Dialog(Dlg0)

                If button = 2 Then
                        Exit Sub
                ElseIf button = 0 Then
                        Exit Sub
                End If

                sTargetColumn = iupper(dlg0.TextBox1)
                sTargetColumnLength = dlg0.TextBox2

                If IsNumeric(sTargetColumnLength) = False Then
                        MsgBox "There is not a numeric value inserted for attribute length (no 3) ."&Chr(13)&"Please correct this.",0,"Hint"
               Else
                        If sTargetColumnLength > 500 Then
                                MsgBox "The length value is to long."&Chr(13)&"Length must be <= 500.",0,"Hint"
                                dlg0.TextBox2 = ""
                        Else
                                 If iIsBlank(sTargetColumn) = 1 And iIsBlank(sTargetColumnLength) = 1 And button = 1 Then
                                    MsgBox "You have not filled out a new ColumnName."&Chr(13)& _
                                                  "Please insert a new ColumnName."&Chr(13)&Chr(13)& _
                                                   "Formatting rules for the new ColumnName" &Chr(13)& _
                                                   "- Length of ColumnName not longer than 20 characters." &Chr(13)& _
                                                   "- Use only letters, numbers and underline" &Chr(13)& _
                                                   "- First character is not a number value" &Chr(13)& _
                                                   "- Use no spaces in the new ColumnName" &Chr(13)& _
                                                   "- Do not use special characters",0,"Hint"
                                 ElseIf iIsBlank(sTargetColumn) = 1 And iIsBlank(sTargetColumnLength) = 0 And button = 1 Then
                                     MsgBox "You have not used a new ColumnName."&Chr(13)& _
                                                   "Please insert a new ColumnName."&Chr(13)&Chr(13)& _
                                                   "Formatting rules for the new ColumnName (no 3):" &Chr(13)& _
                                                    "- Length of ColumnName not longer than 20 characters." &Chr(13)& _
                                                    "- Use only letters, numbers and underline" &Chr(13)& _
                                                    "- First character is not a number value" &Chr(13)& _
                                                    "- Use no spaces in the new ColumnName" &Chr(13)& _
                                                    "- Do not use special characters",0,"Hint"
                                    ElseIf   iIsBlank(iRegExpr(sTargetColumn, "^[a-zA-Z]+[a-zA-Z0-9_]*$")) = 1 Then
                                      MsgBox "The new ColumnName is not allowed to have special characters, spaces, leading numbers."&Chr(13)& _
                                                    "Please insert a new ColumnName."&Chr(13)&Chr(13)& _
                                                    "Formatting rules for the new ColumnName (no 3):" &Chr(13)& _
                                                    "- Length of ColumnName not longer than 20 characters." &Chr(13)& _
                                                    "- Use only letters, numbers and underline" &Chr(13)& _
                                                    "- First character is not a number value" &Chr(13)& _
                                                    "- Use no spaces in the new ColumnName" &Chr(13)& _
                                                    "- Do not use special characters",0,"Hint"
                                    ElseIf Len(sTargetColumn) > 20 Then
                                      MsgBox "The new ColumnName ist longer than 20 characters."&Chr(13)& _
                                                    "Please insert a new ColumnName."&Chr(13)&Chr(13)& _
                                                    "Formatting rules for the new ColumnName (no 3):" &Chr(13)& _
                                                    "- Length of ColumnName not longer than 20 characters." &Chr(13)& _
                                                    "- Use only letters, numbers and underline" &Chr(13)& _
                                                    "- First character is not a number value" &Chr(13)& _
                                                    "- Use no spaces in the new ColumnName" &Chr(13)& _
                                                    "- Do not use special characters",0,"Hint"
                                    ElseIf iIsBlank(sTargetColumn) = 0 And iIsBlank(sTargetColumnLength) = 1 And button = 1 Then
                                      MsgBox "You have not give a new ColumnLength."&Chr(13)&"Please insert a ColumnLength for the new column.",0,"Hint"
                                    Else
                                           If IsNumeric(sTargetColumnLength) = False Then
                                                MsgBox "The ColumnLength you have inserted is not numeric."&Chr(13)&"Please insert a numeric ColumnLength.",0,"Hint"
                                           Else
                                                v = 1
                                           End If
                                  End If
                         End If
                 End If

           Loop Until v = 1 And button = 1

           sub_clearing

           MsgBox "The clearing for the column "& sColumnName &" from file "& sFileName &" was done."&Chr(13)& _
                         "The File "& sFileName &" now contains the new column "& iUpper(sTargetColumn) &"."& Chr(13) & Chr(13),"","Hint"
End Sub

Function Enable(ControlID$, Action%, SuppValue%)

          Select Case Action%
                  Case 1
                        sub_fileName_read
                        DlgListBoxArray "DrpList1", aFileName()
                        sFileName = aFileName(0)
                        sub_charColumns_read
                        DlgListBoxArray "DrpList2", aColumnName()
                        sColumnName = aColumnName(0)

                  Case 2
                        If ControlID$ = "drpList1" Then
                                 sFileName = aFileName(SuppValue%)
                                 sub_charColumns_read
                                 DlgListBoxArray "DrpList2", aColumnName()
                                 sColumnName = aColumnName(0)
                        End If

                        If ControlID$ = "drpList2" Then
                                 sColumnName = aColumnName(SuppValue%)
                        End If
       End Select

End Function

Sub sub_fileName_read
        Dim s As String

        s = Dir(Client.WorkingDirectory + "*.imd")
        ReDim aFileName(0)

        While iIsBlank(s) = 0
                aFileName(UBound(aFileName)) = s
                s = Dir
                If iIsBlank(s) = 0 Then
                    ReDim preserve aFileName(UBound(aFileName)+1)  
                End If
        Wend

End Sub

Sub sub_charColumns_read

        Dim n As Integer
        Dim i As Integer
        Dim nSpaltenanzahl As Integer

        Set db = Client.OpenDatabase(Client.WorkingDirectory + sFileName)
        Set table = db.TableDef
        nSpaltenanzahl = table.count
        ReDim aColumnName(0)
        n = 0
        For i = 1 To nSpaltenanzahl

                Set field = table.GetFieldAt(i)
                If field.IsCharacter() = true Then
                          If n > 0 Then
                                ReDim preserve aColumnName(UBound(aColumnName)+1)
                          End If
                          aColumnName(UBound(aColumnName)) = field.name
                          n=n+1
                End If
        Next
        db.Close
End Sub

Sub sub_clearing

        Dim sFormula As String
        Dim eqn As String

        'very simple formula...
        sFormula = "@mid(@UPPER(" + sColumnName + "); 1;  "+sTargetColumnLength+") "

        Set db = Client.OpenDatabase (Client.WorkingDirectory + sFileName)

        Set table = db.TableDef
        Set field = table.NewField

        eqn = sFormula
        field.Name = sTargetColumn

        field.Description = "new Field from example_ColumnClearing.iss"
        field.Type = WI_VIRT_CHAR
        field.Equation = eqn

        field.Length = sTargetColumnLength
        table.AppendField field

        db.Close

        Set db = Nothing
        Set task = Nothing
        Set field = Nothing

End Sub

@Brian:
The new code formatting works great....thx!

Images: 
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

That is a nice script.  I like how you do lots of error checking.  When I give the IDEAScripting course I always emphasize good error checking.  Lets face it users will enter incorrect data accidently or intentionally (to see what it does) and you don't want an error message be given to someone that doesn't have a clue what they mean.

For choosing IDEA files I unusually use the:

Set filebar=CreateObject("ideaex.fileexplorer")
filebar.displaydialog
fileName=filebar.selectedfile

In V9 there is going to be a client function that will get the IDEA file, so you will only have to write one line of code for it.

I also like how you are using another undocumented function (it is documented in V9) the DlgListBoxArray, I didn't know it existed until recently so I always create my arrays prior to calling the dialog.

I could see how you could set this up as a toolbox for cleaning up fields.  I noticed for the mid() function you are using ; - are you using the German Language version of IDEA or is it the language setting?  In the English version you would need to use a comma.  That is one of the problems with scripting in different languages.  The first time I gave the course was in French, well I had forgotten that in French you format your number differently then in English, so one of the routines I had written wouldn't work Smile, on the positive side it forced me to make something that was more generic and not language specific.

CB's picture
CB
Offline
Joined: 10/19/2012 - 04:54

hi brian,
this script with even a much more complicated formula is in production for our users... nice thing to build a join field when different formating is used...

if the user have the possibility to make input on a dialog you have to make an error handling for each input possibility! sometimes that makes a lot of work but be shure of murphys law: „If there's more than one possible outcome of a job or task, and one of those outcomes will result in disaster or an undesirable consequence, then somebody will do it that way.“ and that is true :-D

in the german version you have to use semicolon when you use the eqn for append fields.. so with @-functions i have to use semicolons and with i-functions comma.
i was playing with DlgListBoxArray and see that it work also in dialogs... i have not checked this with the documentation but i am glad that it works :-)) good to now that with idea v9 it will still working...
if you have news about the v9 it would be great to post them... in germany we normaly have to wait one year to get the newest idea version... :-(

ideaex.fileexplorer is a nice function that i know... but i like to make this on my own ;-)

cheers,
chris