Wishlist / Coding Examples for the following...

[al0]

To put it short - size reduction. sometimes very significant.
But anyway, delimiter with possibility to escape it is much better solution then delimiter per field and relatively often, then fixed length.

One thing I would also like to point out is that delimiters are set for an entire file. Meaning, that there is one setter for a delimiter that is used. Attempting to set a delimiter per field would require significantly more configuration than there is currently, for very little value. At a minimum, if this feature is needed, it would need to be a separate tokenizer all together, so that the more common use case would be easier to configure. However, with that being said, I still don't understand what setting a delimiter per field would add that couldn't more easily be accommodated by using fixed-length formatting.

[raymondwee]

Hi, I need to implement an ItemReader for an excel file. How do I do this? I've taken a look at this thread because its the closest thing I can find that is related to creation of an ItemReader for Excel. anyway, I'm not sure but I think I cannot use FlatFileItemReader for excel files specially since the Excel file that I need to parse contains multiple tabs.

Regards,
Raymond

[Dave Syer]

The LineReader that we use internally and the DelimitedLineTokenizer are designed to work with Excel-generated CSV files. Not sure about tabs, but I can't see why it wouldn't work. Did you try it?

[raymondwee]

Hi Dave,

Thanks for the quick reply.

You see I'm a bit confused on how the read() method is called. I'm trying to parse an Excel file. I pass the excel file name and sheet name to the constructor of the CustomItemReader I created (which extends FlatFileItemReader and implements ItemReader). In the constructor of my CustomItemReader, I used JExcelAPI to load the worksheet whose data I needed to process, in the read() method, I'm supposed to return the contents of each row. However, for some reason, the read() method is not called at all. I'm at a loss on why this happens. Please see below my job configuration and my classes:

<beans ...>

<import resource="applicationContext.xml"/>

<bean id="myJob" class="org.springframework.batch.core.job.SimpleJo b">
<property name="name" value="myJob" />
<property name="steps">
<list>
<bean id="process" parent="simpleStep">
<property name="itemReader" ref="customExcelReader"/>
<property name="itemWriter" ref="customExcelWriter"/>
</bean>
</list>
</property>
</bean>

<bean id="customExcelReader" class="testing.reader.CustomExcelSheetReader">
<constructor-arg value="d:/sample.xls"/>
<constructor-arg value="Sample"/>
<property name="lineTokenizer">
<bean
class="org.springframework.batch.item.file.transfo rm.DelimitedLineTokenizer">
</bean>
</property>
<property name="fieldSetMapper">
<bean
class="testing.mapping.MyFieldSetMapper" />
</property>
</bean>

<bean id="customExcelWriter" class="testing.writer.CustomExcelSheetWriter">
</bean>
</beans>

Here's my code for the custom ItemReader I created:

public class CustomExcelSheetReader extends FlatFileItemReader implements ItemReader {

private List<DomObject> itemList;

public CustomExcelSheetReader(String excelFileName, String sheetName) {
Workbook workbook = null;
try {
workbook = Workbook.getWorkbook(new File(excelFileName));
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

Sheet sheet = null;
if (workbook.getSheet(sheetName) != null) {

itemList = new ArrayList<DomObject>();
sheet = workbook.getSheet(sheetName);

for (int i=1; i< sheet.getRows(); i++) {
DomObject data = new DomObject();
//call setter methods of DomObject
itemList.add(data);
}
}
}

/* (non-Javadoc)
* @see org.springframework.batch.item.ItemReader#mark()
*/
public void mark() throws MarkFailedException {
// TODO Auto-generated method stub

}

/* (non-Javadoc)
* @see org.springframework.batch.item.ItemReader#read()
*/
public Object read() throws Exception, UnexpectedInputException,
NoWorkFoundException, ParseException {
// TODO Auto-generated method stub
System.out.println("read called");
System.out.println(testItems.isEmpty());
if (!itemList.isEmpty()) {
return itemList.remove(0);
}
return null;
}

/* (non-Javadoc)
* @see org.springframework.batch.item.ItemReader#reset()
*/
public void reset() throws ResetFailedException {
// TODO Auto-generated method stub

}

}

Thanks!
Raymond

[Dave Syer]

I don't understand why you extend FlatFileItemReader. There isn't much point it you are not reading a flat file. Also your implementation of ItemReader is not honouring the reset() and mark() contract (so rollbacks will not work). And it doesn't implement ItemStream with the index of your row list, so it isn't restartable.

Other than that I can't see any issues explaining why read() is not working. How did you launch the job?

(Please use [code][/code] tags to post code and stack traces.)

[raymondwee]

Hi Dave,

Thanks for the reply,

I finally figured out what's wrong. It was because I included the log4j jar file but did not create a log4j property file so all my errors messages did not appear. Anyway, the actual cause was that I had a class not found exception. I had renamed my custom item writer class but had not updated it in my job configuration xml file. Also, I changed my approach. Here's how I coded it.

public class CustomExcelSheetReader extends FlatFileItemReader implements StepExecutionListener {

public void beforeStep(StepExecution arg0) {
   //read excel file name from execution context
  //convert excel file to csv
  // super.setResource(new FileSystemResource(parentPath + "Sample.csv"));
}

with that code, I am now able to read my input file as a flat file and use it for my processing.

Regards,
Raymond

[kkn032000]

hi raymond,

can you provide the code for beforeStep method ?

Hi Dave,

Thanks for the reply,

I finally figured out what's wrong. It was because I included the log4j jar file but did not create a log4j property file so all my errors messages did not appear. Anyway, the actual cause was that I had a class not found exception. I had renamed my custom item writer class but had not updated it in my job configuration xml file. Also, I changed my approach. Here's how I coded it.

public class CustomExcelSheetReader extends FlatFileItemReader implements StepExecutionListener {

public void beforeStep(StepExecution arg0) {
   //read excel file name from execution context
  //convert excel file to csv
  // super.setResource(new FileSystemResource(parentPath + "Sample.csv"));
}

with that code, I am now able to read my input file as a flat file and use it for my processing.

Regards,
Raymond

[kkn032000]

how did you covert excel to csv ?

[raymondwee]

Hi,

I'm using Apache POI to convert from excel to CSV. Please see a sample code below:

    InputStream inp;
    HSSFWorkbook wb = null;
    
    //instantiate a new workbook object
    try {
         //inputFile is the name of the xls file, i.e. sample.xls
	 inp = new FileInputStream(inputFile);
	 wb = new HSSFWorkbook(inp);
    } catch (FileNotFoundException e1) {
	// TODO Auto-generated catch block
	e1.printStackTrace();
    } catch (IOException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
    }

    HSSFSheet sheet = wb.getSheet(sheetName);
    try {
        Writer output = new BufferedWriter(
                     new FileWriter(sheetName + ".csv"));
	StringBuilder contents = new StringBuilder();
		
        //i=1 because I wanted to skip the first line. 
	for (int i=1; i <= sheet.getLastRowNum(); i++) {
	    HSSFRow row = sheet.getRow(i);			

	   for (int j=0; j < sheet.getRow(0).getPhysicalNumberOfCells(); j++) {
		HSSFCell cell = row.getCell(j);
				
		if (cell != null) {
			contents.append(cell.getRichStringCellValue());
		} 
		if (j< (sheet.getRow(0).getPhysicalNumberOfCells() -1)) {
			contents.append(",");
		}					
	   }
	   contents.append("\n");				
	}
	try {
		output.write( contents.toString());
	} finally {
		output.close();
	}		
    }

Regards,
Raymond

[kkn032000]

Thanks for quick reply.

Hi,

I'm using Apache POI to convert from excel to CSV. Please see a sample code below:

    InputStream inp;
    HSSFWorkbook wb = null;
    
    //instantiate a new workbook object
    try {
         //inputFile is the name of the xls file, i.e. sample.xls
	 inp = new FileInputStream(inputFile);
	 wb = new HSSFWorkbook(inp);
    } catch (FileNotFoundException e1) {
	// TODO Auto-generated catch block
	e1.printStackTrace();
    } catch (IOException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
    }

    HSSFSheet sheet = wb.getSheet(sheetName);
    try {
        Writer output = new BufferedWriter(
                     new FileWriter(sheetName + ".csv"));
	StringBuilder contents = new StringBuilder();
		
        //i=1 because I wanted to skip the first line. 
	for (int i=1; i <= sheet.getLastRowNum(); i++) {
	    HSSFRow row = sheet.getRow(i);			

	   for (int j=0; j < sheet.getRow(0).getPhysicalNumberOfCells(); j++) {
		HSSFCell cell = row.getCell(j);
				
		if (cell != null) {
			contents.append(cell.getRichStringCellValue());
		} 
		if (j< (sheet.getRow(0).getPhysicalNumberOfCells() -1)) {
			contents.append(",");
		}					
	   }
	   contents.append("\n");				
	}
	try {
		output.write( contents.toString());
	} finally {
		output.close();
	}		
    }

Regards,
Raymond