XLSdatareader and trim fields

CloverDX Platform
1

Hi,

I’m using the XLSDataReader, but there is no option for triming spaces from fields. Am I missing something? Is there a simple work around rather than passing through reformatter with trim() on every field? (I have like 350 fields)

Thanks
Jay

2

Hello Jay,
unfortunately there no way to trim strings directly in XLSDataReader, but I’ve reported the request to our issue tracking system at Add trim attribute to XLSReader. Please use guest/guest as username/password credentials to see it.
So you need to pass the records through Reformat, but the java transformation for this task is really simple:

import org.jetel.component.DataRecordTransform;
import org.jetel.data.DataField;
import org.jetel.data.DataRecord;
import org.jetel.exception.TransformException;
import org.jetel.metadata.DataFieldMetadata;
import org.jetel.util.string.StringUtils;


public class Trim extends DataRecordTransform {
	
	DataField currentInput;

	@Override
	public int transform(DataRecord[] arg0, DataRecord[] arg1)
			throws TransformException {
		for (int i = 0; i < arg0[0].getNumFields(); i++) {
			currentInput = arg0[0].getField(i);
			if (!currentInput.isNull() && currentInput.getType() == DataFieldMetadata.STRING_FIELD) {
				arg1[0].getField(i).setValue(
						StringUtils.trim((StringBuilder) currentInput.getValue()));
			}else{
				arg1[0].getField(i).setValue(currentInput);
			}
		}
		return 0;
	}

}
3

Hi Agata and thanks for the reply.

I cheated and did it the easy way :slight_smile:

I read the file with XLSReader, wrote it back to a temp file as “|” delimited. The read it with Universal Data Reader with “trim”. Which worked.

Thanks
Jay