How to Empty a files data after the load is completed

[Anonymous]

Requirement is:

Tfileinputexcel ----> Tfileoutputexcel  ------> after loading into the output. i want to empty the inputexcel file without deleting the file.

How to achieve this.

[fdenis]

count number of rows and fill them with null or blank.

[Anonymous]

@TRF @fdenis @manodwhb

Can you explain me in steps.

[manodwhb]

@ravi777,Once the file has loaded and then take on subjob ok and delete the existing file or archive the file and create a new file as similar with your source file columns.

[Anonymous]

But when i am creating new .xlsx file using tfiletouch. it is creating but not able to open it.

[fdenis]

tfiletouch create an empty file not an xlsx one

[manodwhb]

@ravi777,you need to create with below way.

tFilxedInputFlow-->tFileExceloutput

[iamabhishek]

@ravi777 - if you want a java solution to handle excel files then you can write a routine and invoke that in your talend job something like this - 

And, you can use below code to check and delete all rows from your excel file - 

FileInputStream ExcelFileToRead = new FileInputStream(filePath);
XSSFWorkbook workbook = new XSSFWorkbook(ExcelFileToRead);
Sheet sheet = workbook.getSheetAt(0);
String sheetName = sheet.getSheetName();
int numberOfRows = sheet.getPhysicalNumberOfRows();
if(numberOfRows > 0) {
		for (int index = sheet.getLastRowNum(); index >= sheet.getFirstRowNum(); index--) {
		//System.out.println(index);
		if(sheet.getRow(index) != null) {
		sheet.removeRow( sheet.getRow(index));
		}
		else{
		    System.out.println("Info: clean sheet");
		}
             }
	}
ExcelFileToRead.close();  
// Write the output to the file
FileOutputStream ExcelFileToWrite = new FileOutputStream(filePath);
workbook.write(ExcelFileToWrite);
ExcelFileToWrite.close();  

// Closing the workbook
workbook.close();
}

[Anonymous]

Hello ravi777,

Is there any update for your issue?

Best regards

Sabrina

[AIjel]

Thank you very much, very useful.

Just 2 comments to help here:

1- Just in case, you need to add the following in your tJava component under Advanced setting -> import

import java.io.FileOutputStream;

import java.io.FileInputStream;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFSheet;

2- Sheet must be replaced by XSSFSheet in the code

3- there is an extra } at the end of the code that need to be removed

here is the corrected code:

/* start */

FileInputStream ExcelFileToRead = new FileInputStream(context.filePath);

XSSFWorkbook workbook = new XSSFWorkbook(ExcelFileToRead);

XSSFSheet sheet = workbook.getSheetAt(0);

String sheetName = sheet.getSheetName();

int numberOfRows = sheet.getPhysicalNumberOfRows();

if(numberOfRows > 0) {

for (int index = sheet.getLastRowNum(); index >= sheet.getFirstRowNum(); index--) {

//System.out.println(index);

if(sheet.getRow(index) != null) {

sheet.removeRow( sheet.getRow(index));

}

else{

System.out.println("Info: clean sheet");

}

}

}

ExcelFileToRead.close();

// Write the output to the file

FileOutputStream ExcelFileToWrite = new FileOutputStream(context.filePath);

workbook.write(ExcelFileToWrite);

ExcelFileToWrite.close();

// Closing the workbook

workbook.close();

/* end */