Subscribe to this thread
Home - General / All posts - Help with Columnformat
dchall8
1,008 post(s)
#18-Oct-16 22:45

What is the best way to automatically reformat a column or columns in a table?

Every week I import a data table from our master db. I export from the db to Excel and import into Manifold. There are 4 columns that I would like to have in a different format than what the export gives me, so each week I go through the drill of changing the format on those columns (12.34 format with zero decimal places, thousands indicated, and aligned right). In reading other posts to this forum I have come to learn that this task could be automated. I'm seriously SQL challenged and need about 3 lines of explanation for every line of SQL code. I found this link (http://www.georeference.org/forum/t39566.3) where Dimitri suggests saving and loading a format. I can't find how to do that. Adamw suggest using a script. I thought scripts could only be used on active columns, and these columns are simply imported flat. But if a script will work, I really don't care how it works. I also found this link (http://www.georeference.org/forum/t37392.15), where Lorne suggests some coding at the bottom of the thread. Again, I'm challenged, but I do see the right sort of thing I'm looking for at the bottom. Can someone please kick me in the right direction?

Sloots

678 post(s)
#19-Oct-16 11:43

Why don't you link the table from within Manifold. If you keep the columns the same (name and order) and the filename as well, just open Manifold and refresh the data. Works for me.

Cheers,

Chris


http://www.mppng.nl/manifold/pointlabeler

dchall8
1,008 post(s)
#19-Oct-16 16:36

Linking directly would be ideal. Then my map data would be current at all times. Unfortunately we're leasing the database and are only allowed to change the data by using their interface.

Sloots

678 post(s)
#19-Oct-16 19:08

I thought so, that why I suggested to link the excel file.


http://www.mppng.nl/manifold/pointlabeler

dchall8
1,008 post(s)
#25-Oct-16 19:30

I can't seem to get that to work. I get "Can't establish connection to data source: error binding to data source." Rebooting the computer did not clear that up.

Sloots

678 post(s)
#25-Oct-16 19:51

Are you using Manifold 64bit?


http://www.mppng.nl/manifold/pointlabeler

bclement
275 post(s)
#20-Oct-16 00:20

Dchall8

If the columns you are needing changed are the same ones each time (it sounds like you are ingesting the same table just with updated data each week), then you may create a script component that you can run against the table to make the changes automatically. You are correct that a script can be run within an Active Column, but it is not the only place you can use scripting to your advantage. You can create a script component by right clicking in the project pane and choosing create > script. That script can be run at any time. It can be written in any of several languages. PM me if you need further help.

Ben

tjhb
10,094 post(s)
#20-Oct-16 01:24

I was trying to find the right words. Ben is very measured. So, tough cop...

Dchall8 I wonder if you are not doing yourself a disservice, by not reading the parts of the manual related to programming. That can be daunting at first, but you can definitely do this. You're an experienced user.

The task you're suggesting is great for a simple script. Manifold is well set up to make it really easy. Easy if you read the manual and practice a bit. Easy if you know where to look--and how to look at it (that can be daunting too).

But if a script will work, I really don't care how it works.

Sorry to put it like this, but that's not going to get you anywhere.

dchall8
1,008 post(s)
#25-Oct-16 19:56

I don't have a problem with tough love. But are you asking me to learn three or four scripting languages so I can pick the right one for me to use in Manifold simply so that I can put commas into my column of data? Because that's what the Help Programming section does. This is a perfect example of how the Manifold philosophy of "help" goes astray. The Programming Manifold topic is a 5,000 word dissertation written by scripting experts for scripting experts. First of all, FIVE THOUSAND WORDS??? Secondly it seems to be geared toward scripting professionals new to Manifold and need to understand that Manifold works with any scripting language you want. The topic contains such statements as the following: "Within Manifold, all .NET assemblies (including Manifold.Interop) are installed into the global assembly cache to facilitate reuse." You might understand that, but I don't know what .NET is and I don't know what assemblies are. I don't recognize the term Manifold.Interop. And I don't understand what a global assembly cache is. Later the topic explains how to say "Hello, World" in C#, JScript, JScript . NET, VB .NET VBScript or IronPython. I don't know anything about any of those except that they are scripting languages. So this is my starting point. The entire balance of the programming topics in Manifold help assume the reader is a script programmer and already know scripting syntax. They do not instruct a noob as to how to write a script.

Here's a script I copied from one of Art's videos

Function CalcArea

    CalcArea = Record.Data ("Area (I)") /43560

End Function

That one I understand as a mathematical function - hence the name Function. I don't know why it is written like that or why the parentheses, apparent redundant quotes, and spaces are so important but they certainly are. I have another script mostly written by Gustavo Palminha to do some fancy KML manipulations. Being honest I do not understand why it works, but it does. The script at this link (well, linking isn't working in the forum) is sort of close to what I want, but it does not start with the word Function and that's all I know to start with. When the script doesn't start with Function, I don't know what to do to make it work with my data. I don't understand the imports, the class, the shared sub, the dimensioning, or the with statements. I don't know what they do or why/where/how to use them.

I make maps to help people find their property. Every now and then I read something in this forum that gives me the idea that a script or query (and I don't know the difference - nor do I want to become conversant in the differences) would help me do something that I do on a routine basis. In this case I want to remove decimal places and put commas into a field of numbers. Up until now the members of this forum have been helpful. The superior scripting gurus have been quick to knock something out. I should not have to endure goading by the superior scripting minds into a new career in scripting to help me help myself on a forum.

tjhb
10,094 post(s)
#26-Oct-16 00:01

These are very helpful comments, especially to anyone involved in documentation or training.

Thanks for taking the time.

The only thing I would criticise is this--just the underlined bits.

Every now and then I read something in this forum that gives me the idea that a script or query (and I don't know the difference - nor do I want to become conversant in the differences) would help me do something that I do on a routine basis. In this case I want to remove decimal places and put commas into a field of numbers. Up until now the members of this forum have been helpful. The superior scripting gurus have been quick to knock something out. I should not have to endure goading by the superior scripting minds into a new career in scripting to help me help myself on a forum.

In short, if you don't want to learn, don't ask for help.

If you do want to learn, you do need to be interested, at least a bit.

The forum is collegial. We help each other get better at what we do (it works).

If instead you want a service, pay for it.

But the really worthwhile point you have made is: this should be easier. You're right.

dchall8
1,008 post(s)
#26-Oct-16 15:28

This entire forum is a service to the Manifold users of the world. These services are run by volunteers just like every other Internet forum. Manifold might be our job, but writing about Manifold has become a hobby. Well, maybe for some writing about it is part of their job, but for the vast majority of us at this forum it is a hobby. We all "pay" for the service by contributing ideas and support where our strengths lie. When we run into a hurdle we can usually count on finding someone here with strengths to cover our weaknesses.

I just want to do my job and make helpful maps. Programming is out of my league, which I realized 40 years ago in college. When it seems like there might be a shortcut for something I do repeatedly in Manifold, this seems like the right place to ask. I'm not asking for someone to write War and Peace (Gustavo already wrote that one for me ). That product, which I got working, commented heavily, and posted in its entirety to share with this community, is nothing short of revolutionary if you ask me. That coding demonstrates how to use a an active column field in Manifold to create a KML export file containing HTML, CSS, and links to external websites when implemented in Google Earth. It takes me 1 minute to update 30,000 records and publish the KML file. Someone could sell that as a Google Earth product to REALTORS and USDA investigators. I give it away to anyone who asks so they'll stop calling me. I give it to ESRI users because they can't figure out how to do it themselves. The trick to doing something like that is to look at lots and lots of other peoples work to get ideas. I can get the ideas, but I can't implement them without help. When I get something good here, I finish the paperwork with a final report posting showing the completed working text. That's about all I can do to pay for the service provided by this collegiate forum.

Okay enough about forum philosophy, I found this coding on another helpful post (http://www.georeference.org/forum/t37392.15).

code

' VB .NET

Option Explicit

Imports Manifold.Interop.Scripts

Imports M = Manifold.Interop

Class Script

 Shared Sub Main

 Dim Comps As M.ComponentSet

 Dim Tbl As M.Table

 Dim Col As M.Column

 Dim Cols As M.ColumnSet

 Dim fmt As M.ColumnFormat

        Comps = Context.Document.ComponentSet

 

        Tbl = Comps.Item("Drawing Table")

        Cols = Tbl.ColumnSet

 

        Col = Cols.Item("Lat")

        fmt = Col.Format

        fmt.Style = "12°20'44.2"" N"

 

        Col = Cols.Item("Long")

        fmt = Col.Format

        fmt.Style = "12°20'44.2"" E"

 

        Col = Cols.Item("Dt")

        fmt = Col.Format

 With fmt

 ' Open a table and right click on one of the column headers.

 ' Click Format to see a list of possible formats.

 ' Three examples for dates:  "<default>" , "3/14/98", "03/14/1998"

            .Style = "14 March, 1998"

            .Align = M.ColumnAlign.ColumnAlignCenter 'ColumnAlignRight ColumnAlignCenter  ColumnAlignLeft

            .Width = 300

            .Indent = 30

 End With

 

        Col = Cols.Item("Speed")

        fmt = Col.Format

 With fmt

            .Style = "12.34"

            .Decimals = 1

            .Thousands = True

            .Align = M.ColumnAlign.ColumnAlignRight 'ColumnAlignCenter  ColumnAlignLeft

            .Width = 300

            .Indent = 10

 End With

        Tbl.Open()

 End Sub

End Class

This does not look like a script to me. My limited experience with them says they start with the word, Function. This one starts with 'VB .NET." I see at the bottom of the code they are doing exactly what I want to do, but I don't get all the rest of the coding above that. How much of that is important to the process of changing the format for decimals, thousands, and alignment? I also don't understand the dimensioning process and why it's necessary. I assume from the context the name of the column they are reformatting is "Speed."

tjhb
10,094 post(s)
#27-Oct-16 01:39

Progress!

This does not look like a script to me.

It is a script, written in Visual Basic .NET. Other scripts on the forum are written in VBScript (the default in Manifold 8), C#, IronPython, sometimes JScript (a Microsoft version of JavaScript).

To see what scripting languages are available, use File > Create > Script, then in the Create Script dialog, look at the Language list. If you like, create a VBScript script, open it, run it.

My limited experience with them says they start with the word, Function.

A script can contain functions. A function can be part of a script (or a program). Some scripts have no functions, some many. What counts as a function varies from one language to another.

Manifold active columns can reference a script that only contains functions. A script containing just functions can be called a library (more usual in other contexts).

Queries are like scripts written in SQL, except for two fundamental differences, which make them not scripts.

Scripts start (broadly speaking) at the top, and move to the bottom, doing one thing after the other. (More accurately, each part, such as a function, proceeds in this way; in some languages the parts can be in arbitrary order.)

Queries don't do that. SQL starts on the inside (often, near the bottom), then proceeds outwards (broadly speaking, towards the top). Another thing queries don't do, is do one thing after another. The query defines logical relations between sets--at the minimum, an input set and an output set. Unlike a script, a query doesn't define a series of processing operations or "steps". It is up to the SQL engine to decide this, and we normally don't see it. The engine can schedule operations however it likes--leave things out, combine or add things--provided it gets the specified logical relations right.

(To complicate things, it is possible to have an SQL script--a sequence of queries or other SQL statements, roughly the same as running one whole query after another.)


The script above is definitely in the right area, and you're right, it can be much shorter.

tjhb
10,094 post(s)
#27-Oct-16 02:22

Try this. It is VBScript--similar but simpler.

-- VBScript

Option Explicit

Sub Main

    Dim tblName

    Dim colNames

    Dim tbl

    Dim cols, col

    Dim name

    '

    tblName = "Table"

    colNames = Array("Column 1""Column 2""Column 3""Column 4")

    '

    Set tbl = Document.ComponentSet(tblName)

    Set cols = tbl.ColumnSet

    For Each name In colNames

        Set col = cols(name) ' must exist and must be a floating-point column

        With col.Format

            ' only change what needs changing (faster)

            If .Style <> "12.34" Then .Style = "12.34"

            If .Decimals <> 0 Then .Decimals = 0

            If Not .Thousands Then .Thousands = True

            If .Align <> ColumnAlignRight Then .Align = ColumnAlignRight

        End With 

    Next ' name

End Sub

Change tblName and the list in colNames to match the target table.

Attachments:
Set column formatting.txt

dchall8
1,008 post(s)
#27-Oct-16 18:08

In reply to your previous, "Progress," post, thank you for taking the time to explain that. Secondly, OMG! That explanation makes my head hurt. I'm not an inside out thinker.

In reply to the VBScript post, thank you. Again, I don't know why you have to dimension those elements and not simply use the names of the elements themselves along the way, but here we go! I'll get back to you...

Graeme

990 post(s)
#29-Jul-17 01:26

Variation on this scripted approach to changing the format of multiple columns by script.

I have occasional need to reformat about 300 columns in one table, from the default floating point to "12.34" "zero decimals" just like in the present case. However to explicitly name the full array of columns within the script would take maybe as long as manually renaming them. If this was a routine need, it would be worth the effort, but it's an occasional one-off. My question is, is there a VBScript equivalent of a wildcard "*" for this sort of task?

I've searched for this and come acrossthis discussion. All my columns in the current case are of the form "G1234_abcd_12" so all begin with "G" in common. I had a punt at substituting " colNames = Array(".G*")" in the script, it almost seems there but returns an error

Index out of range:

Line 16

Any pointers (including get busy with the mouse) much appreciated!

adamw


10,447 post(s)
#29-Jul-17 06:31

There is no wildcard syntax supported, but you can loop through the columns and inspect their names.

Eg:

'VBScript

Sub Main

  Set table = Document.ComponentSet("T")

  For Each col in table.ColumnSet

    If LCase(Left(col.Name, 2)) = "c_" Then ' names starting with c_

      col.Format.Align = ColumnAlignCenter

    End If

  Next

End Sub

See attached file (open the table to see current formatting on an example record, open the script, run the script and observe changes to formatting).

Attachments:
script-format-columns-8.map

Graeme

990 post(s)
#29-Jul-17 08:42

Much appreciated Adam - slowly the mists thin..

For anyone else struggling, in context it's necessary to tailor to suit your own specific data structure. So in my present case, searching for strings beginning with upper-case "G", and needing to change from default floating point format to 12.34 with zero decimal places:

'VBScript

Sub Main

    Set table = Document.ComponentSet("T")

    For Each col in table.ColumnSet

        If UCase(Left(col.Name, 1)) = "G" Then

            col.Format.Style  = "12.34"

            col.Format.Decimals  = 0

        End If

    Next

End Sub

adamw


10,447 post(s)
#29-Jul-17 08:55

That's a minor thing, but your code is going to format columns with names starting with either "g" or "G". If you want to only format columns with names starting with "G", remove the call to UCase. I used LCase in my example, because I wanted to accept prefixes regardless of their case (could have used UCase and then compared to "C_" to achieve the same).

dchall8
1,008 post(s)
#27-Oct-16 19:09

I like the simpler VBScript, so thank you. I have changed the names to fit my table and commented the lines that I think I understand below. It is choking on line 16.

Option Explicit

Sub Main

    Dim tblName

    Dim colNames

    Dim tbl

    Dim cols, col

    Dim name

    '

    'Identify the table to work on and refer to it as tblName in this script

    tblName = "WorkingParcels"

    'Identify the names of the columns to be reformatted in the table

    colNames = Array("land_val""imprv_val""market""assessed_val")

    'I'm not sure how to describe the next line

    Set tbl = Document.ComponentSet(tblName)

    'script chokes on this next line.  Object doesn't support this property or method: 'tbl.ColumnSet' Line: 16

    Set cols = tbl.ColumnSet

    For Each name In colNames

        Set col = cols(name) ' must exist and must be a floating-point column

        With col.Format

            ' only change what needs changing (faster)

            If .Style <> "12.34" Then .Style = "12.34"

            If .Decimals <> 0 Then .Decimals = 0

            If Not .Thousands Then .Thousands = True

            If .Align <> ColumnAlignRight Then .Align = ColumnAlignRight

        End With 

    Next ' name

End Sub

What am I missing in line 16?

tjhb
10,094 post(s)
#27-Oct-16 19:41

First the error on the line

Set cols = tbl.ColumnSet

->

Object doesn't support this property or method: 'tbl.ColumnSet'

Error messages aren't always so helpful, but this one is.

Here's how to read it:

What kind of object has a .ColumnSet property? A table.

OK, what kind of object is tbl? A table, right?

Let's check that. How did we make it? With the preceding line

Set tbl = Document.ComponentSet(tblName)

So we asked for the component named tblName. Did we check it was a table? Could have, but didn't. So it coud be a different kind of component--that depends on the name in tblName.

Where is tblName set? Up in the line

tblName = "WorkingParcels"

Is "workingParcels" a table? My guess is that it's not, but instead the associated drawing.

Drawings don't have a .ColumnSet property, only their tables do. So that's the problem.

There are several ways to fix that. The easiest is to use the name of the table, not the drawing.

tjhb
10,094 post(s)
#27-Oct-16 20:14

Secondly

Again, I don't know why you have to dimension those elements and not simply use the names of the elements themselves along the way,

It's a very good question.

The short answer is: you don't have to. You could just use the names, just by removing the line

Option Explicit

at the top. Then you could get rid of all the Dim statements.

Why bother requiring Dim statements? Another short answer: to help us find small mistakes, in mispelling a variable name, or using an old variable name somewhere when we've changed it everywhere else to a new name, or similar things. These mistakes are easy to make, and if there is no error message, extremely difficult to find.

This is especially important in VBScript, because if you use the looser approach (without Option Explicit), VBScript lets you you any name anywhere, misspelled, misused, anything. Very often this sort of coding error will silently produce results that are wrong. Everything seems fine, until it isn't, and then you notice that half your data is missing, or has moved 5 metres, or been sent to the client with attributes missing, whatever.

It's a downside of VBScript (and to a slightly lesser extent, JScript and JavaScript) that it lets you do almost anything with very few checks.

A similar issue in both languages is that variables are not strongly typed--usually not typed at all, everything is just a "var", a variable, which can refer to a number one minute, a string the next, and so on, anything. That is very flexible and simple, but there's the same downside as with unchecked variable names: it is very easy to make a subtle coding error that raises no alarm bells, but only later turns out to be critical (and then is extremely hard to find).

These considerations are important for choosing a language. It's a balance of safety and convenience, and partly a matter of taste. For example, I dislike C# because I find there is so much text devoted to safety that I can hardly see what I'm doing (which isn't actually very safe, either).

If you want to learn scripting in just one scripting language, for the long term, you could also consider Python (for Manifold that means IronPython). You don't have to use cruft to be safe. There are other mechanisms to keep you from shooting yourself in the foot that work really well in practice, without getting in the way of coding. Python is even simpler than VBScript (especially if you need to do something complex!).

But there's nothing wrong with sticking with VBScript, if that works for you, or moving to Visual Basic .NET later.

dchall8
1,008 post(s)
#27-Oct-16 20:56

Changing the name to the full name of the table worked. This is wonderful! I did not exactly dread changing the formatting every week, but I did put it off until I needed it. I wondered if the script would work since the columns are in a related data table. But it changed in the related columns and not in the original table. Thank you for your patience and for the additional commentary. I will be referring to it in the future.

Now, what other fields can I change the format on?? Hmmmmm...

tjhb
10,094 post(s)
#28-Oct-16 09:19

I didn't answer this.

'I'm not sure how to describe the next line

Set tbl = Document.ComponentSet(tblName)

Try

'Make a reference to the target table object

More to be said (why Set for some lines but not others? and so on) but no need to complicate things probably.

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