Subscribe to this thread
Home - General / All posts - quick transform
sour113 post(s)
#04-Jun-18 06:35

Good day everyone!

Is there a way in manifold a script perhaps where in all columns that have "data types = Text ANSI (fixed length)" is transform to "Make title case"?

This will help me to transform in an instant instead of using the transformation toolbar and in per column basis.

Thanks!

adamw

8,037 post(s)
#04-Jun-18 10:36

You can use UI scripting.

See attached example.

The script:

'VBScript

 

Sub TitleCaseField(field)

  Set ui = Application.UserInterface

  Set toolbar = ui.Toolbars("Transform")

  toolbar.ControlSet("EditTransformTarget").Text = field

  toolbar.ControlSet("EditTransformOperation").Text = "Make Title Case"

  toolbar.ControlSet("EditTransformApply").Push

End Sub

 

Sub Main

  Set active = Application.ActiveWindow.ActiveComponent

  For Each col In active.ColumnSet

    If col.Type = ColumnTypeAText Then

      TitleCaseField col.Name

    End If

  Next

End Sub

I didn't check if the columns are fixed or variable-length, but that's easy to add.

You have to open the table, then run the script from the Project pane with the table window being active.

Attachments:
title-case-multiple-8.map

steveFitz

208 post(s)
#04-Jun-18 12:21

Or a script with a query in it:

'vbscript

Sub Main

 Set app = Application

  Set comps = Document.ComponentSet

 If comps.ItemByName("Q") = -1 then

 Set qry = document.NewQuery("Q"True)

 Else

 Set qry = comps("Q")

 End if

 theTbl = Application.InputBox ("Name of table to transform to upper case?","Uppercase data")

 If comps.ItemByName(theTbl) = -1 then

 Call app.MessageBox(theTbl & " not found""Not found")

 Exit Sub

 End if

  Set tbl = comps(theTbl)

 For each col in tbl.Columnset

 if col.Type = ColumnTypeAText then

 tblName = col.Name

 qry.text = "UPDATE [Trees Table] SET " & tblName & " = UPPER(" & tblName & ")"

 ' uncomment following line to see names of columns updated.

 'app.History.Log (col.Name) & vbnewline

 qry.run()

 End if

 Next

 If comps("Q") <> "" then

 comps.Remove("Q")

 End if

End Sub

Adam, how can you discern fixed from variable length?

I've been trying to figure it out..

steveFitz

208 post(s)
#04-Jun-18 12:51

Apologies! I read Title Case but registered UPPER CASE.

Probably not so easy in SQL...

adamw

8,037 post(s)
#04-Jun-18 13:27

That's why I used UI scripting. :-)

Whether a text field is fixed-length or variable-length is determined from Column.Size. 0 means variable-length, a positive value means fixed-length.

steveFitz

208 post(s)
#05-Jun-18 00:34

Also, for interest there is a past thread where tjhb and JKelly put forward a VbScript and a C# script that change a column to title case. Interestingly, VbScript does it almost twice as quick.

... variable-length is determined from Column.Size. 0 means variable-length

Thanks Adam. So simple!

steveFitz

208 post(s)
#05-Jun-18 01:16

Interestingly, VbScript does it almost twice as quick.

Scrap that. Once debug/log calls were removed there wasn't much difference.

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.