Run script thru all rows and check cell entry

[binaercode]

Your Setup:

• SeaTable Edition (Developer or Enterprise)
• SeaTable Version (only necessary for self-hosted):
  You can find your SeaTable Server version at https://your-server-url/server-info

Describe the Problem/Error/Question:

Please describe your issue as precise as possible. If helpful, you can also provide:

• API endpoints you used
• Commands you executed (remove credentials)
• Screenshots
• Diagrams with Excalidraw

Error Messages:

If there are any error messages shown in the console or in the logs, please include them for better readability by wrapping them in a code block.

Example of a code block for error messages

Hello Everybody. I’m pretty new to SeaTable and I have a Problem.

Base Structure:

Table: CEL-Rohdaten

Columns: 15 (e.g. “Equipment”, “Event”)

Rows: Up to 30.000

Whole table will be updated often by hand with new Data provided by an .csv file.

Table: Event

Columns: 10 (only one equal to Table “CEL-Rohdaten”)

Rows: Depending on Table “CEL-Rohdaten”

What do I want?

I Want to run a script thru all Rows of Table “CEL-Rohdaten”.

If the value of the Column “Event” is “ZD”, then copy the Data from Column “Equipment” into the Table “Event”.

So far I couldn’t figure how to do this.

I tried to run an Phyton auto script by “add new entry”. In this way I could grab the Informations I need. But it seems, that the script doesn’t run thru all rows. The Output list didn’t contain all specific values from the Table “CEL-Rohdaten”. It seems it stops after a few hundred of rows.

So I would like to run it manually with a “while” loop thru all rows.

I tried to grab every row by row this way:

while i < 30000:
  rows = base.list_rows(Tabelle, view_name='Default View', start=i, limit=i)

But how do I get the Value of the Column “Event” to make an “if” check?

Thx for help.

[bha]

Hi @binaercode, and welcome to the SeaTable forum!

You might find most of you answers in the Developer documentation but let me summarize few info here to answer some of your questions:

• If you run a python script via an automation with the trigger conditions “Records added”, it will be triggered each time a new record is added, so it’s maybe not what you want as the script will run a huge number of time making each time api calls Besides, I don’t know for sure how this is triggered with csv bulk import , I’ll have to check
• Most operations have size limits. The list_rows operation for example has a 1000 records limit, that’s why you don’t manage to get all your rows. So you’ll have to make a loop, but your one is probably not what you want:
  • for i=1 you retrieve only the first row (start at 1, limit to 1 row)
  • for i=2 you retrieve only the second and third rows (start at 2, limit to 2 rows)
    etc…
• However, as you want to filter your rows (only those where “Event” column is “ZD”), you should definitely filtering while retrieving the rows by using the base.query function (see last function of tihs section in the developer manual). Using a SQL query (which should be in your case something as simple as SELECT * FROM `CEL-Rohdaten` WHERE Event = "ZD" (don’t forget the `` around your table name as SQL doesn’t like names with spaces or special characters) you’ll be able to retrieve only the rows you’re actually interested in, PLUS it has a 10 000 rows limit:
  • if you think you won’t never have more than 10 000 rows actually meeting your “Event”=“ZD” condition, you can run it only once
  • otherwise you’ll have to make a loop

PS: to only answer the primary question, if you get all your rows (unfiltered) you can just the name of the column between brackets to access it:
filtered_rows = [row for row in rows if row['Event'] == "ZD"]

Hope this helps.

Bests,
Benjamin

[binaercode]

Thx a lot. I just tried the selection using SQL and it works perfect.

Thats my Code so far:

#Clear targetTable
zielTabelle = "EventZD"
sqlDel = 'DELETE FROM ' + zielTabelle
base.query(sqlDel)

#Select Data from sourceTable
Tabelle = "`CEL-Rohdaten`"
sqlGet = 'SELECT EQUIPMENT, BEZEICHNUNG, SERIAL FROM ' + Tabelle + ' WHERE VORHABEN = "ZA" LIMIT 10000'
json_data = base.query(sqlGet)
#print(json_data)
base.batch_append_rows(zielTabelle, json_data)

But in the case I may get more than 10.000 rows, how can I loop it?

I couldn’t find anything inside the Documentation of setting an “start/stop” trigger using the SELECT SQL code.

[cdb]

sql query supports LIMIT and OFFSET as described here: SQL Reference - SeaTable Developer Manual

With this is should be easy to create a loop.

AI just proposed my such code:

def process_batches(base, source_table, target_table, batch_size=10000):
    offset = 0

    # Clear target table once at the start
    sqlDel = 'DELETE FROM ' + target_table
    base.query(sqlDel)

    while True:
        # Fetch batch from source table with OFFSET and LIMIT
        sqlGet = f'SELECT EQUIPMENT, BEZEICHNUNG, SERIAL FROM `{source_table}` WHERE VORHABEN = "ZA" LIMIT {batch_size} OFFSET {offset}'
        json_data = base.query(sqlGet)

        # Stop if no more records
        if not json_data:
            break

        # Append batch to target table
        base.batch_append_rows(target_table, json_data)

        # Increment offset for next batch
        offset += batch_size
process_batches(base, "CEL-Rohdaten", "EventZD")

Please review and test it!