# Script Keeps Writing Over Old Data

**URL:** <https://forum.inductiveautomation.com/t/script-keeps-writing-over-old-data/78791>\
**Category:** Ignition\
**Tags:** ignition81, vision, scripting\
**Created:** [September 5, 2023, 9:21pm UTC](https://forum.inductiveautomation.com/t/script-keeps-writing-over-old-data/78791 "2023-09-05T21:21:44Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![Brittany\_Villasenor](https://avatars.discourse-cdn.com/v4/letter/b/e68b1a/32.png) [@Brittany\_Villasenor](https://forum.inductiveautomation.com/u/Brittany_Villasenor)\
**Post date:** [September 5, 2023, 9:21pm UTC](https://forum.inductiveautomation.com/t/script-keeps-writing-over-old-data/78791/1 "2023-09-05T21:21:44Z")

</div>

I have a script where the goal is to input the new data into an Excel spreadsheet. The script keeps writing over the old data instead of appending to the sheet. Anyone know what the issue is with this script?

```auto
# Import necessary modules
from org.apache.poi.ss.usermodel import WorkbookFactory
from org.apache.poi.xssf.usermodel import XSSFWorkbook
from java.io import FileInputStream, FileOutputStream, File
from system.file import writeFile

# Define the file path where you want to save the Excel file
filePath = "PATH TO FILE"

# Define a function to append data to Excel
def appendToExcel(event):
    try:
        # Get the selected text values from the dropdowns
        value1 = event.source.parent.getComponent('SawSelector').selectedStringValue
        value2 = event.source.parent.getComponent('TypeSelector').selectedStringValue
        value3 = event.source.parent.getComponent('BrandSelector').selectedStringValue
        value4 = event.source.parent.getComponent('ReasonSelector').selectedStringValue

        # Get the current date and time
        value5 = system.date.now()

        # Open the existing Excel file for reading
        fileInputStream = FileInputStream(File(filePath))
        workbook = WorkbookFactory.create(fileInputStream)
        sheet = workbook.getSheetAt(0) # Assuming you're working with the first sheet

        # Find the next available row index
        nextRowIndex = sheet.getLastRowNum() + 1

        # Create a new row for the new data
        newRow = sheet.createRow(nextRowIndex)
        newRow.createCell(0).setCellValue(value1)
        newRow.createCell(1).setCellValue(value2)
        newRow.createCell(2).setCellValue(value3)
        newRow.createCell(3).setCellValue(value4)
        newRow.createCell(4).setCellValue(value5)

        # Save the changes to the existing Excel file without prompting the user
        fileInputStream.close()
        tempFilePath = filePath + ".tmp"
        fileOutputStream = FileOutputStream(File(tempFilePath))
        workbook.write(fileOutputStream)
        fileOutputStream.close()

        # Move the temporary file to replace the original file
        File(tempFilePath).renameTo(File(filePath))

    except Exception as e:
        # Handle any exceptions here
        print("Error:", str(e))

# Bind the button's action event to the appendToExcel function
event.source.parent.getComponent('Button 1').setActionCommand("append")
event.source.parent.getComponent('Button 1').addActionListener(appendToExcel)

```

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [September 6, 2023, 11:37am UTC](https://forum.inductiveautomation.com/t/script-keeps-writing-over-old-data/78791/2 "2023-09-06T11:37:24Z")

</div>

> [@Brittany\_Villasenor](#):
>
> `File(tempFilePath).renameTo(File(filePath))`

Are you certain it's overwriting old data? renameTo() will not work if the destination file already exists.

---

<div class="post-metadata">

**Author:** ![Brittany\_Villasenor](https://avatars.discourse-cdn.com/v4/letter/b/e68b1a/32.png) [@Brittany\_Villasenor](https://forum.inductiveautomation.com/u/Brittany_Villasenor)\
**Post date:** [September 6, 2023, 1:00pm UTC](https://forum.inductiveautomation.com/t/script-keeps-writing-over-old-data/78791/3 "2023-09-06T13:00:41Z")

</div>

Hi Jordan,

I've come up with a different solution that does not rewrite over any old data. The destination file did already exist so I see that the renameTo() wasn't going to work with how I coded it.

Here is my new code:

```auto
# Import necessary modules
from org.apache.poi.ss.usermodel import WorkbookFactory
from org.apache.poi.ss.usermodel import Row
from java.io import FileInputStream, FileOutputStream
from java.io import File

# Define the file path where you want to save the Excel file
filePath = "PATH TO FILE"

# Define a function to export data to Excel
def exportToExcel(event):
    try:
        # Get the selected text values from the dropdowns
        value1 = event.source.parent.getComponent('SawSelector').selectedStringValue
        value2 = event.source.parent.getComponent('TypeSelector').selectedStringValue
        value3 = event.source.parent.getComponent('BrandSelector').selectedStringValue
        value4 = event.source.parent.getComponent('ReasonSelector').selectedStringValue

        # Get the current date and time
        value5 = system.date.now()

        # Open the Excel file for reading
        fileInputStream = FileInputStream(File(filePath))
        workbook = WorkbookFactory.create(fileInputStream)
        sheet = workbook.getSheetAt(0) # Assuming you're working with the first sheet

        # Create a new row
        newRow = sheet.createRow(sheet.getLastRowNum() + 1)

        # Set cell values
        newRow.createCell(0).setCellValue(value1)
        newRow.createCell(1).setCellValue(value2)
        newRow.createCell(2).setCellValue(value3)
        newRow.createCell(3).setCellValue(value4)
        newRow.createCell(4).setCellValue(value5)

        # Save the changes to the Excel file
        fileOutputStream = FileOutputStream(File(filePath))
        workbook.write(fileOutputStream)
        fileOutputStream.close()

    except Exception as e:
        # Handle any exceptions here
        print("Error:", str(e))

# Bind the button's action event to the exportToExcel function
event.source.parent.getComponent('Button 1').setActionCommand("export")
event.source.parent.getComponent('Button 1').addActionListener(exportToExcel)

```

Now it is working except when I press the button in Ignition once, it saves the data in the drop down to the Excel sheet way too many times. Is the issue in the try block?

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [September 6, 2023, 1:20pm UTC](https://forum.inductiveautomation.com/t/script-keeps-writing-over-old-data/78791/4 "2023-09-06T13:20:51Z")

</div>

I'd say it's in trying to add an extra listener to the button.

I suggest putting the function into a project script and calling it from the button event. That will also make it reusable.

Example: if I make a project script called _project_, then put this slightly modified version of your function in it.

```python
from org.apache.poi.ss.usermodel import WorkbookFactory
from org.apache.poi.ss.usermodel import Row
from java.io import FileInputStream, FileOutputStream
from java.io import File

def exportToExcel(filePath, values):
    logger = system.util.getLogger('exportToExcel')
    try:
        # Open the Excel file for reading
        fileInputStream = FileInputStream(File(filePath))
        workbook = WorkbookFactory.create(fileInputStream)
        sheet = workbook.getSheetAt(0) # Assuming you're working with the first sheet

        # Create a new row
        newRow = sheet.createRow(sheet.getLastRowNum() + 1)

        # Set cell values        
        for i, value in enumerate(values):               
            newRow.createCell(i).setCellValue(value)

        # Save the changes to the Excel file
        fileOutputStream = FileOutputStream(File(filePath))
        workbook.write(fileOutputStream)
        fileOutputStream.close()
        logger.info('export to file {} successful'.format(filePath))

    except Exception as e:
        logger.warn('Error: {}').format(str(e))

```

Then, in the button's action performed script:

```python
fileName = '/path/to/the/file.xlsx'
values = [
          event.source.parent.getComponent('SawSelector').selectedStringValue,
          event.source.parent.getComponent('TypeSelector').selectedStringValue,
          event.source.parent.getComponent('BrandSelector').selectedStringValue,
          event.source.parent.getComponent('ReasonSelector').selectedStringValue,
          system.date.now()
         ]

project.exportToExcel(fileName, values)

```

---

<div class="post-metadata">

**Author:** ![Brittany\_Villasenor](https://avatars.discourse-cdn.com/v4/letter/b/e68b1a/32.png) [@Brittany\_Villasenor](https://forum.inductiveautomation.com/u/Brittany_Villasenor)\
**Post date:** [September 6, 2023, 1:34pm UTC](https://forum.inductiveautomation.com/t/script-keeps-writing-over-old-data/78791/5 "2023-09-06T13:34:11Z")

</div>

I'm going to try this out! Thanks for your help so far. I'll let you know how it turns out.

---

<div class="post-metadata">

**Author:** ![Brittany\_Villasenor](https://avatars.discourse-cdn.com/v4/letter/b/e68b1a/32.png) [@Brittany\_Villasenor](https://forum.inductiveautomation.com/u/Brittany_Villasenor)\
**Post date:** [September 6, 2023, 2:36pm UTC](https://forum.inductiveautomation.com/t/script-keeps-writing-over-old-data/78791/6 "2023-09-06T14:36:32Z")

</div>

> [@JordanCClark](#):
>
> ```python
> from org.apache.poi.ss.usermodel import WorkbookFactory
> from org.apache.poi.ss.usermodel import Row
> from java.io import FileInputStream, FileOutputStream
> from java.io import File
> 
> ```

Hey Jordan,  
I took your suggestion but am still having the issue of the data logging multiple times. And ideas as to what could be causing this?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [September 6, 2023, 3:01pm UTC](https://forum.inductiveautomation.com/t/script-keeps-writing-over-old-data/78791/7 "2023-09-06T15:01:37Z")

</div>

> [@Brittany\_Villasenor](#):
>
> still having the issue of the data logging multiple times

Sounds like a side effect of improperly using a propertyChange event instead of the button's actionPerformed event.

---

<div class="post-metadata">

**Author:** ![Brittany\_Villasenor](https://avatars.discourse-cdn.com/v4/letter/b/e68b1a/32.png) [@Brittany\_Villasenor](https://forum.inductiveautomation.com/u/Brittany_Villasenor)\
**Post date:** [September 6, 2023, 4:12pm UTC](https://forum.inductiveautomation.com/t/script-keeps-writing-over-old-data/78791/8 "2023-09-06T16:12:10Z")

</div>

I have the button script in the actionPerformed event... any other ideas?  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/8/f8863f84ee35092a4a6b80a571a7262899898c3b.png)

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [September 6, 2023, 4:27pm UTC](https://forum.inductiveautomation.com/t/script-keeps-writing-over-old-data/78791/9 "2023-09-06T16:27:43Z")

</div>

Try it on a fresh button, if you're still using the original.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [September 6, 2023, 4:29pm UTC](https://forum.inductiveautomation.com/t/script-keeps-writing-over-old-data/78791/10 "2023-09-06T16:29:57Z")

</div>

Suspicion being that added action listener still getting triggered right?

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [September 6, 2023, 4:30pm UTC](https://forum.inductiveautomation.com/t/script-keeps-writing-over-old-data/78791/11 "2023-09-06T16:30:16Z")

</div>

Yep.

---

<div class="post-metadata">

**Author:** ![Brittany\_Villasenor](https://avatars.discourse-cdn.com/v4/letter/b/e68b1a/32.png) [@Brittany\_Villasenor](https://forum.inductiveautomation.com/u/Brittany_Villasenor)\
**Post date:** [September 6, 2023, 4:35pm UTC](https://forum.inductiveautomation.com/t/script-keeps-writing-over-old-data/78791/12 "2023-09-06T16:35:33Z")

</div>

That did the trick! Thanks again!