topic Re: How to Empty a files data after the load is completed in Talend Studio

How to Empty a files data after the load is completed

Anonymous — Mon, 02 Jul 2018 07:30:58 GMT

Requirement is:

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

How to achieve this.

Re: How to Empty a files data after the load is completed

fdenis — Mon, 02 Jul 2018 07:36:08 GMT

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

Re: How to Empty a files data after the load is completed

Anonymous — Mon, 02 Jul 2018 09:56:11 GMT

@TRF @fdenis @manodwhb

Can you explain me in steps.

Re: How to Empty a files data after the load is completed

manodwhb — Mon, 02 Jul 2018 12:26:12 GMT

@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.

Re: How to Empty a files data after the load is completed

Anonymous — Mon, 02 Jul 2018 12:53:47 GMT

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

Re: How to Empty a files data after the load is completed

fdenis — Mon, 02 Jul 2018 12:55:32 GMT

tfiletouch create an empty file not an xlsx one

Re: How to Empty a files data after the load is completed

manodwhb — Mon, 02 Jul 2018 13:01:11 GMT

@ravi777,you need to create with below way.

tFilxedInputFlow-->tFileExceloutput

Re: How to Empty a files data after the load is completed

iamabhishek — Thu, 05 Jul 2018 12:30:32 GMT

@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();
}

Re: How to Empty a files data after the load is completed

Anonymous — Wed, 01 Aug 2018 06:08:41 GMT

Hello ravi777,

Is there any update for your issue?

Best regards

Sabrina

Re: How to Empty a files data after the load is completed

AIjel — Thu, 07 Jan 2021 18:23:31 GMT

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 */