[resolved] Excel Matrix to XML : only the last columns is got

One Star

[resolved] Excel Matrix to XML : only the last columns is got

Hello!
I've made my own component that can take a Excel sheet (with a matrix) and transform it to an XML file with the value inside the case and its x and y coordinates.
As a test, I made it so it create a XML file with the data and so it can be used as a processing component with the tFileOutputXML.
While the file created by my component has all the values from all the columns of the matrix, the XML file I get thanks to tFileOutputXML only contain the value of the last column.
I have been trying all kind of things but I can't get it to work like I want and I don't know where it come from.
Here is my XML file (of my component) :
<?xml version="1.0" encoding="UTF-8"?>
<COMPONENT>
<HEADER AUTHOR="Marine"
COMPATIBILITY="ALL"
PLATEFORM="ALL"
RELEASE_DATE="20170324A"
SERIAL=""
STARTABLE="false"
STATUS="BETA"
VERSION="0.1"
SCHEMA_AUTO_PROPAGATE="false"
DATA_AUTO_PROPAGATE="false" >
<SIGNATURE />
</HEADER>

<FAMILIES>
<FAMILY>CustomCode</FAMILY>
</FAMILIES>

<DOCUMENTATION>
<URL />
</DOCUMENTATION>

<CONNECTORS>
<CONNECTOR CTYPE="FLOW" MIN_INPUT="1" MAX_INPUT="1" MIN_OUTPUT="1" MAX_OUTPUT="1" />
</CONNECTORS>

<PARAMETERS>
<PARAMETER NAME="TEMPFILE" FIELD="FILE" NUM_ROW="1" REQUIRED="true" />
<PARAMETER NAME="SCHEMA" FIELD="SCHEMA_TYPE" NUM_ROW="2" REQUIRED="true" />
</PARAMETERS>

<CODEGENERATION />

<RETURNS>
<RETURN AVAILABILITY="AFTER" NAME="NB_LINE" TYPE="id_Integer" />
</RETURNS>
</COMPONENT>

Here is the begin javajet :
<%@ jet
imports="
org.talend.core.model.process.INode
org.talend.core.model.process.ElementParameterParser
org.talend.core.model.metadata.IMetadataTable
org.talend.core.model.metadata.IMetadataColumn
org.talend.core.model.process.IConnection
org.talend.core.model.process.IConnectionCategory
org.talend.designer.codegen.config.CodeGeneratorArgument
org.talend.core.model.metadata.types.JavaTypesManager
org.talend.core.model.metadata.types.JavaType
java.util.List
java.util.Map
"
%>
<%
CodeGeneratorArgument codeGenArgument = (CodeGeneratorArgument) argument;
INode node = (INode)codeGenArgument.getArgument();
String cid = node.getUniqueName();
String filename = ElementParameterParser.getValue(node, "__TEMPFILE__");
%>
System.out.println("BEGINNING SECTION.");
// Initializing the number of line
int nb_line_<%=cid %> = 0;
// Creating the temporary file
java.io.FileWriter fw_<%=cid %> = new java.io.FileWriter(<%=filename %>);
java.io.PrintWriter pw_<%=cid %> = new java.io.PrintWriter(fw_<%=cid %>);
// Creating the XMl skeleton
pw_<%=cid %>.println("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
pw_<%=cid %>.println("<TABLE>");
pw_<%=cid %>.println(" <DATA>");

Here the main javajet :
<%@ jet
imports="
org.talend.core.model.process.INode
org.talend.core.model.process.ElementParameterParser
org.talend.core.model.metadata.IMetadataTable
org.talend.core.model.metadata.IMetadataColumn
org.talend.core.model.process.IConnection
org.talend.core.model.process.IConnectionCategory
org.talend.designer.codegen.config.CodeGeneratorArgument
org.talend.core.model.metadata.types.JavaTypesManager
org.talend.core.model.metadata.types.JavaType
java.util.List
java.util.Map
"
%>
<%
CodeGeneratorArgument codeGenArgument = (CodeGeneratorArgument) argument;
INode node = (INode)codeGenArgument.getArgument();
String cid = node.getUniqueName();
%>
System.out.println("MAIN SECTION.");
<%
// Getting the name of the incoming connection
String inRowName = node.getIncomingConnections().get(0).getName();

// Getting the metadata of the incoming connection
IMetadataTable preMetadata = null;
if ((node.getIncomingConnections() != null) && (node.getIncomingConnections().size() > 0)) {
preMetadata = node.getIncomingConnections().get(0).getMetadataTable();
}

// Getting the metadata of the current component
IMetadataTable curMetadata = node.getMetadataList().get(0);

// Getting the name of the outcoming connection
String outRowName = node.getOutgoingConnections().get(0).getName();

// Getting the metadata table of the outgoing connection
// IMetadata postMetadata = null;
// if ((node.getOutgoingConnections() != null) && (node.getOutgoingConnections().size() > 0)) {
// postMetadata = node.getOutgoingConnections().get(0).getMetadataTable();
// }

// Getting the columns of the preMetadata, curMetadata and postMetadata
List<IMetadataColumn> preColumns = preMetadata.getListColumns();
List<IMetadataColumn> curColumns = curMetadata.getListColumns();
// List<IMetadataColumn> postColumns = postMetadata.getListColumns();
%>
// Initializing the columns and lines variables
int nbLines = 0;
int nbCol = 0;
<%
// Parsing through these columns
for (int i = 0; i < preColumns.size(); i++) {
IMetadataColumn preCol = preColumns.get(i);
%>
pw_<%=cid %>.println(" <COORDINATES>");
<%
for (int j = 0; j < curColumns.size(); j++) {
IMetadataColumn curCol = curColumns.get(j);

// Testing the label of the current column :
// If the label is VALUE
if (curCol.getLabel().equals("VALUE")) {
%>
pw_<%=cid %>.println(" <VALUE>" + <%=inRowName %>.<%=preCol.getLabel() %> + "</VALUE>");
<%=outRowName %>.<%=curCol.getLabel() %> = <%=inRowName %>.<%=preCol.getLabel() %>;
<%
}

// If the label is X_FIELD
else if (curCol.getLabel().equals("X_FIELD")) {
%>
nbLines = 24 - nb_line_<%=cid %>;
pw_<%=cid %>.println(" <X_FIELD>" + nbLines + "</X_FIELD>");
<%=outRowName %>.<%=curCol.getLabel() %> = nbLines;
<%
}

// If the label is Y_FIELD
else if (curCol.getLabel().equals("Y_FIELD")) {
%>
nbCol = <%=i %> + 1;
pw_<%=cid %>.println(" <Y_FIELD>" + nbCol + "</Y_FIELD>");
<%=outRowName %>.<%=curCol.getLabel() %> = nbCol;
<%
}
}
%>
pw_<%=cid %>.println(" </COORDINATES>");
<%
}
%>

nb_line_<%=cid %>++;

Here is the end javajet :
<%@ jet
imports="
org.talend.core.model.process.INode
org.talend.core.model.process.ElementParameterParser
org.talend.core.model.metadata.IMetadataTable
org.talend.core.model.metadata.IMetadataColumn
org.talend.core.model.process.IConnection
org.talend.core.model.process.IConnectionCategory
org.talend.designer.codegen.config.CodeGeneratorArgument
org.talend.core.model.metadata.types.JavaTypesManager
org.talend.core.model.metadata.types.JavaType
java.util.List
java.util.Map
"
%>
<%
CodeGeneratorArgument codeGenArgument = (CodeGeneratorArgument) argument;
INode node = (INode)codeGenArgument.getArgument();
String cid = node.getUniqueName();
%>
System.out.println("ENDING SECTION.");
// Closing the XML skeleton
pw_<%=cid %>.println(" </DATA>");
pw_<%=cid %>.println("</TABLE>");
// Closing the temporary file
pw_<%=cid %>.close();
// Saving the number of lines
globalMap.put("<%=cid %>_NB_LINE", nb_line_<%=cid %>);

I will appreciate all the help you could give me Smiley Very Happy
Moderator

Re: [resolved] Excel Matrix to XML : only the last columns is got

Hi,
Are you looking for multi loop element? Here is an "Append the source xml file" checkbox in the tadvancedFileOuputXML component.
Could you please try to use this component instead to see if the output contains all the value of columns?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] Excel Matrix to XML : only the last columns is got

Hi!
Thanks for your answer! I have found the solution yesterday : I have to open my for-loop to parse all my columns and not to close it. I have to make Talend closes it for me.
Smiley Very Happy
Moderator

Re: [resolved] Excel Matrix to XML : only the last columns is got

Hi,
Great this issue has been fixed by yourself. Could you please mark this topic as resolved?
You can click the "Set this topic as resolved" link which is right underneath your initial post? This way, other users will be informed that this thread has been resolved.
Best regards
Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: [resolved] Excel Matrix to XML : only the last columns is got

Hi All, I have to load data from file to Table/File with below conditions. Source record count not fixed can change day by day. Still need to create three files, first file with first five records, Second file with last five records and third file with remaining records.