Member since
02-11-2022
21
Posts
1
Kudos Received
3
Solutions
My Accepted Solutions
Title | Views | Posted |
---|---|---|
2444 | 01-03-2023 01:57 PM | |
1256 | 01-03-2023 01:56 PM | |
4640 | 02-14-2022 10:08 AM |
01-21-2023
08:22 AM
Hi, I dont think you catch the SQL error in the sense that PutSQL wont report the error. However you can use the PutDatabaseRecrod instead and use the failure relationship to LogMessage where you can access the error message using the "putdatabaserecord.error" attribute. A better way of capturing errors from any processor (Global Catch) is to use the SiteToSiteBulletinReportingTask as explained here: SiteToSiteBulletinReportingTask If that helps please accept solution. Thanks
... View more
01-13-2023
07:39 AM
Hi I want to extract a CSV file from SQL query one of the columns should be present in the CSV twice, diffirent position. I had the same error is there a workaround ? Thank you
... View more
01-03-2023
01:57 PM
1 Kudo
I found a solution, this is the script: @Grapes(@Grab(group='org.apache.poi', module='poi-ooxml', version='3.9'))
import com.opencsv.CSVReader
@Grapes(@Grab(group='com.opencsv', module='opencsv', version='4.2'))
import org.apache.poi.ss.usermodel.*
import org.apache.poi.xssf.streaming.*
import org.apache.poi.hssf.usermodel.*
import org.apache.poi.xssf.usermodel.*
import org.apache.poi.ss.util.*
import org.apache.poi.ss.usermodel.*
import org.apache.poi.hssf.extractor.*
import java.nio.charset.*
import java.io.*
import org.apache.commons.io.IOUtils
def flowFile = session.get()
def date = new Date()
if(!flowFile) return
flowFile = session.write(flowFile, {inputStream, outputStream ->
SXSSFSheet sheet1 = null;
CSVReader reader = null;
Workbook wb = null;
String generatedXlsFilePath = "/home/";
FileOutputStream fileOutputStream = null;
def filename = flowFile.getAttribute('filename')
def path = flowFile.getAttribute('path')
def nextLine = ''
reader = new CSVReader(new FileReader(path+filename), ',');
//Workbook wb = WorkbookFactory.create(inputStream,);
//Sheet sheet1 = wb.createSheet("Feuille");
wb = new SXSSFWorkbook(inputStream);
sheet1 = (SXSSFSheet) wb.createSheet('Sheet');
def rowNum = 0;
while((nextLine = reader.readNext()) != null) {
Row currentRow = sheet1.createRow(rowNum++);
for(int i=0; i < nextLine.length; i++) {
if(NumberUtils.isDigits(nextLine[i])) {
currentRow.createCell(i).setCellValue(Integer.parseInt(nextLine[i]));
} else if (NumberUtils.isNumber(nextLine[i])) {
currentRow.createCell(i).setCellValue(Double.parseDouble(nextLine[i]));
} else {
currentRow.createCell(i).setCellValue(nextLine[i]);
}
}
}
//fileOutputStream = new FileOutputStream(generatedXlsFilePath.trim());
//wb.write(fileOutputStream);
generatedXlsFilePath = generatedXlsFilePath + 'SAISIE_MAGASING.XLS'
outputStream = new FileOutputStream(generatedXlsFilePath.trim());
wb.write(outputStream);
wb.close();
//fileOutputStream.close();
outputStream.close();
reader.close();
//outputStream.close();
inputStream.close();
} as StreamCallback)
flowFile = session.putAttribute(flowFile, 'filename', filename)
... View more
01-03-2023
01:56 PM
I found a solution, this is my worked scripr: @Grapes(@Grab(group='org.apache.poi', module='poi-ooxml', version='3.9'))
import com.opencsv.CSVReader
@Grapes(@Grab(group='com.opencsv', module='opencsv', version='4.2'))
import org.apache.poi.ss.usermodel.*
import org.apache.poi.xssf.streaming.*
import org.apache.poi.hssf.usermodel.*
import org.apache.poi.xssf.usermodel.*
import org.apache.poi.ss.util.*
import org.apache.poi.ss.usermodel.*
import org.apache.poi.hssf.extractor.*
import java.nio.charset.*
import java.io.*
import org.apache.commons.io.IOUtils
def flowFile = session.get()
def date = new Date()
if(!flowFile) return
flowFile = session.write(flowFile, {inputStream, outputStream ->
SXSSFSheet sheet1 = null;
CSVReader reader = null;
Workbook wb = null;
String generatedXlsFilePath = "/home/";
FileOutputStream fileOutputStream = null;
def filename = flowFile.getAttribute('filename')
def path = flowFile.getAttribute('path')
def nextLine = ''
reader = new CSVReader(new FileReader(path+filename), ',');
//Workbook wb = WorkbookFactory.create(inputStream,);
//Sheet sheet1 = wb.createSheet("Feuille");
wb = new SXSSFWorkbook(inputStream);
sheet1 = (SXSSFSheet) wb.createSheet('Sheet');
def rowNum = 0;
while((nextLine = reader.readNext()) != null) {
Row currentRow = sheet1.createRow(rowNum++);
for(int i=0; i < nextLine.length; i++) {
if(NumberUtils.isDigits(nextLine[i])) {
currentRow.createCell(i).setCellValue(Integer.parseInt(nextLine[i]));
} else if (NumberUtils.isNumber(nextLine[i])) {
currentRow.createCell(i).setCellValue(Double.parseDouble(nextLine[i]));
} else {
currentRow.createCell(i).setCellValue(nextLine[i]);
}
}
}
//fileOutputStream = new FileOutputStream(generatedXlsFilePath.trim());
//wb.write(fileOutputStream);
generatedXlsFilePath = generatedXlsFilePath + 'SAISIE_MAGASING.XLS'
outputStream = new FileOutputStream(generatedXlsFilePath.trim());
wb.write(outputStream);
wb.close();
//fileOutputStream.close();
outputStream.close();
reader.close();
//outputStream.close();
inputStream.close();
} as StreamCallback)
flowFile = session.putAttribute(flowFile, 'filename', filename)
... View more
12-21-2022
02:35 PM
Hi @ollie_ did you find a solution ?
... View more
12-09-2022
08:07 AM
Hi @ollie_ Did you success to process your xls file? I want to write the flowfile content (wih csv format) into an XLS file, can you help please?
... View more
02-15-2022
02:11 AM
Hi Yamaga, Basically all lookup services in NIFI does not return multiple rows. In my case i wanted out put in json, so I had converted csv lookup file using JOLT transfomation to have key:[jsonArray], with this i used properties lookup and I got the required output. Thanks Ansar
... View more
02-14-2022
11:57 PM
The configuration of the ElasticSearchLookupService Controler Service is pretty straight forward as you have noticed. It uses another controller service, a ElasticSearchClientServiceImpl service, to connect to the Elastic service. Most of the connectivity details need to be specified on the ElasticSearchClientServiceImpl service configuration. The best place for reference is the documentation: ElasticSearchLookupService ElasticSearchClientServiceImpl Cheers, André
... View more
02-14-2022
10:08 AM
I used GetHDFSFileInfo to get the numbe of incoming files with hdfs.count.files attribute Then at the end of the dataflow I move the processed files into a separate folder so only files to merge stay in the root folder. Thanks to @OliverGong for the hint 🙂
... View more
02-14-2022
02:45 AM
I think about Groovy script but did not find how to loop each flowfile or how to get the count of the files
... View more