are there any methods can choose all field and make the SUM for each fields or I must do it one by one?

Question

I will try to be more specific and really I need help

In my personal geodatabase there are two layers:- one of them is small -- about 13 records/features. The other is about 118 records/features. The relation class between the two layers is one to many where the small layer is the origin layer and the big is the destination. What I need to do is to join the big layer with an external excel table. This table has about 200-500 columns and after the join I need to calculate the sum of these records and put it in the small layer.

What I did is a model using model builder in ArcGIS 10.00. This model does the following:

1. Joins the excel table with the big layer
2. After joining I cannot do quick export because I haven't got the license for Data Interoperability Tools so I make feature class to feature class and now I have a feature class called (B)
3. I then make a summary statistics table on feature class B. I choose attribute by attribute and choose the summary type SUM for each column.

The first question is whether there any methods to choose all field and make the SUM for each fields or must I do it one by one?

1. After that I make a join between the small layer and the summary statistics output table (C)
2. I then make feature class to feature class to have the final results

The second question: If the column name in the excel file changed this model is now not useful which I need to do that with different excel file

The third question: Are there any other techniques can I use to solve this problem

Answer 1

I would use ArcPy with ListFields to iterate through your three hundred fields and run Summary Statistics, with a SearchCursor used to open each of the 300 tables thus created and read the sum value.

Note: This answer is in response to the original question (which is now the first of three). If three questions are being asked then I think each should become its own GIS-SE Question so that the overall problem can be addressed in answerable chunks.

Answer 2

Could you be more specific please? In case you mean the fields of a Shapefile, you can access the .dbf file and, after copying the contents to a spreadsheet, you can make any calculations you want. On the other hand if you want to make the calculations inside a GIS software, then both ESRI and QGIS software have tools to add new fields, and/or calculate any statistics of your attribute table.

For QGIS I recommend the Basic Stats Plugin which can do all sorts of stats on your data.

Answer 3

Since you are limited by license issues, do you truly need to do this via a spatial software package?

I have solved join issues before with very simply database tools such as Base in OpenOffice / LibreOffice. It is reasonably straightforward to manage shapefiles with this tool.

Just have a think about the format of the resulting dataset, all you are trying to do is make sure the lat long stay with each record?

Answer 4

To answer your first question. You may be able to use dissolve tool (under data management) to sum multiple fields. Set your input as feature class B. Leave all dissolve fields unchecked. Then add each field you want summed in the statistics section and choose "SUM" for the stat type. Result will be new feature class that only contains the summed fields.

For your second question. You could just make a copy of your model and change the column name. Or load your execl table into your personal GDB as a DBF to standardize the field names.

Hope this helps,

Tyler