topic Re: Create a new table out of listed tables including a section in Python Snippets Questions

Create a new table out of listed tables including a section

JuttaSchiller — Tue, 07 Oct 2014 12:56:27 GMT

I have around 30 tables like this:

Now I have to get one big table out of them, but only for the first four lines of each table.

First I tried it with this code:

resulttxt = newpath + "\\" + "resultSUM.txt"
f = open (resulttxt, 'w')

f.write ("Combi,FREQUENCY,SUM_PERCEN,SUM_SUM_ar,NAME,CODE,\n")
f.close ()

f = open (resulttxt, 'a')   
fc_tables = arcpy.ListTables("*sort_sort*")
for tableR in fc_tables:
    fieldsSe= ["Combi","FREQUENCY" ,"SUM_PERCEN" ,"SUM_SUM_ar", "NAME", "CODE"]
    with arcpy.da.SearchCursor(tableR, fieldsSe, """"OID" < 4""") as sCursor:
        for row in sCursor:
            print row [0], row [1],row [2],row [3],row [4],row [5]
            f.write(str(row[0]) + "," + str(row[1])+ "," +str(row[2]) + "," + str(row[3])+ "," +str(row[4])+ "," +str(row[5]) + "\n")

f.close()

The problem with this code is that the field “Combi” of the “resulttxt” has the type “double” therefore it does not fill the combinations in the right way, because the combinations are originally in a “text “-field (see the first table).

This is the result from the code with open():

Because of this I tried the tool "merge" with the following code:

import arcpy
import os, sys
from arcpy import env


env.overwriteOutput = True
env.workspace = r"D:\Users\julia\erste_aufg\cities_UA"
env.qualifiedFieldNames = False


resulttxt = r"D:\Users\julia\erste_aufg\cities_UA\resultfolder"+  "\\" +"4resultSUM.txt"
fc_tables = arcpy.ListTables("*sort_sort*")
for tableR in fc_tables:
    tableresultName = os.path.splitext (tableR) [0]
    print tableresultName
    arcpy.Merge_management(fc_tables, resulttxt)

The new table looks like this:

This is exactly what I need, but how can I do this for the selection of just the first four lines? Now it gives me all (10) back. Does someone has an idea?

Re: Create a new table out of listed tables including a section

RiyasDeen — Wed, 08 Oct 2014 01:18:31 GMT

Hi Jutta,

Create an empty table with your target schema.

In your for loop,

Create a table view with your table ArcGIS Help 10.1
Then do a select by attribute where OID < 4 ArcGIS Help (10.2, 10.2.1, and 10.2.2)
Append the table view to your empty table.

If creating an empty table is not an option where you don't know the schema before hand. Instead of step 2 above, follow below:

Create an empty array outside the for loop and populate the array with your table views after selection.
Use the array as input for your merge, do this outside the loop, (In your above code you are doing merge within the loop, which will essentially result in your target table overwritten many times)

Re: Create a new table out of listed tables including a section

JuttaSchiller — Wed, 08 Oct 2014 09:54:15 GMT

Riyas Deen‌

Hi Riyas Deen,

Thank you for your answer. It tried it but the huge problem is that the combi is still getting lost. To test is I just copied the selection of one table to the new table. I’m really desperate because I don’t know the way to select the first lines and fill them to one big table without losing the combi. And the Combi-field is now a string why is the combi lost?

fc_tables = arcpy.ListTables("*sort_sort*")

for tableR in fc_tables:

tableresultName = os.path.splitext (tableR) [0]

print tableresultName

arcpy.MakeTableView_management(env.workspace+ "\\" +tableR, "tableR_ly")

arcpy.SelectLayerByAttribute_management("tableR_ly", "NEW_SELECTION",""""OID" < 4""")

tablename = tableresultName + "selc.dbf"

arcpy.CreateTable_management (env.workspace, tablename, tableR)

arcpy.CopyRows_management("tableR_ly", tablename)

Re: Create a new table out of listed tables including a section

RiyasDeen — Wed, 08 Oct 2014 10:50:22 GMT

Hi Jutta,

try below script. This selects rows in a table and uses the selection to perform merge.

import arcpy  
import os, sys  
from arcpy import env  




env.overwriteOutput = True  
env.workspace = r"C:\tempdelete\PL_TO_LINE.gdb"  # Set your workspace
env.qualifiedFieldNames = False  


resulttxt = r"C:\tempdelete\4resultSUM.dbf" # Set your output
fc_tables = arcpy.ListTables("") # Set your filter here
tableViews = []
for tableR in fc_tables:  
  #tableresultName = os.path.splitext (tableR) [0]
  arcpy.AddMessage(tableR)
  tableView = tableR + "_View"
  arcpy.MakeTableView_management(tableR, tableView)
  arcpy.SelectLayerByAttribute_management(tableView, "NEW_SELECTION","OBJECTID < 4") # Set your where clause here
  tableViews.append(tableView)
  arcpy.AddMessage(len(tableViews))


arcpy.Merge_management(tableViews, resulttxt)

Re: Create a new table out of listed tables including a section

JuttaSchiller — Wed, 08 Oct 2014 11:57:10 GMT

Riyas Deen‌

I’m so sorry but it is still the same problem but thanks for your help. This is what I was running:

import arcpy

import os, sys

from arcpy import env

env.overwriteOutput = True

env.workspace = r"D:\Users\julia\erste_aufg\cities_UA"

env.qualifiedFieldNames = False

resulttxt = env.workspace + "\\" + "outFINAL.dbf"# Set your output

fc_tables = arcpy.ListTables("*sort_sorted3.dbf") # Set your filter here

tableViews = []

for tableR in fc_tables:

#tableresultName = os.path.splitext (tableR) [0]

arcpy.AddMessage(tableR)

tableView = tableR + "_View"

arcpy.MakeTableView_management(tableR, tableView)

arcpy.SelectLayerByAttribute_management(tableView, "NEW_SELECTION",""""OID" < 4""") # Set your where clause here

tableViews.append(tableView)

arcpy.AddMessage(len(tableViews))

arcpy.Merge_management(tableViews, resulttxt)

Re: Create a new table out of listed tables including a section

RiyasDeen — Wed, 08 Oct 2014 12:15:40 GMT

Hi Jutta,

My understanding was, you wanted to select first 4 rows in all the tables listed from your workspace and merge these rows into a separate table right? I tested the code and it does exactly that.

I can't access my ArcGIS box now to double check, my suspicion is on your where clause, can you change it to just arcpy.SelectLayerByAttribute_management(tableView, "NEW_SELECTION","OID < 4") removing the extra double quotes.

Re: Create a new table out of listed tables including a section

JuttaSchiller — Wed, 08 Oct 2014 12:59:35 GMT

Riyas Deen‌

Ok, I did a very stupid mistake: If I only select 4 lines it is not possible to get a combi with more than one number, because there is no combination. Doing the selection for more lines it works perfectly. I’m so sorry. And thanks a lot for your help!