Talend: Setting the attribute name and value corresponding to columns

I have a csv as:

ID,Name,Age
1,qwerty,12
2,asdf,11
3,zxcvb,10

I need to create an xml as follows:

<?xml version="1.0" encoding="UTF-8"?>

<entities>
    <entity>
  开发者_如何学Python      <field name="ID" value="1"/>
        <field name="Name" value="qwerty"/>
        <field name="Age" value="12"/>
    </entity>
        <entity>
        <field name="ID" value="2"/>
        <field name="Name" value="asdf"/>
        <field name="Age" value="11"/>
    </entity>
         <entity>
        <field name="ID" value="3"/>
        <field name="Name" value="zxcvb"/>
        <field name="Age" value="10"/>
    </entity>
</entities>

The column names vary from one csv file to another. So I want to make a generic job that takes in all the columns and generate an xml as shown. Any help would be appreciated

---

Since you asked for Talend -- it is easy if you know the column names beforehand, because then you only need tFileInputDelimited -> tFileOutputXML. If you don't know the column names (they are in the first row of your data file), then you need to use Talend Integration Suite (not Talend Open Studio), which supports "dynamic columns".

Define the schema of your file as a single column of type Dynamic. That column will then contain all your data. But for writing it out as XML, you would need to do some Java coding by hand (in a tJavaFlex component). It's not difficult, and I could probably show you how to do it if you need further help.

But as I said, this doesn't work in the Talend Open Studio version, only in the subscription version.

---

try this

   /**
   * Converts a CSV file to a simple XML file
 *
 * @param string $file
 * @param string $container
 * @param string $rows
 * @return string
 */
   function csv2xml($file, $container = 'data', $rows = 'row')
{
        $r = "<{$container}>\n";
        $row = 0;
        $cols = 0;
        $titles = array();

        $handle = @fopen($file, 'r');
        if (!$handle) return $handle;

        while (($data = fgetcsv($handle, 1000, ',')) !== FALSE)
        {
             if ($row > 0) $r .= "\t<{$rows}>\n";
             if (!$cols) $cols = count($data);
             for ($i = 0; $i < $cols; $i++)
             {
                  if ($row == 0)
                  {
                       $titles[$i] = $data[$i];
                       continue;
                  }

                  $r .= "\t\t<{$titles[$i]}>";
                  $r .= $data[$i];
                  $r .= "</{$titles[$i]}>\n";
             }
             if ($row > 0) $r .= "\t</{$rows}>\n";
             $row++;
        }
        fclose($handle);
        $r .= "</{$container}>";

        return $r;
}

$xml = csv2xml('/home/user/myfile.csv', 'people', 'person');

?>

found this at: http://www.hotscripts.com/listing/convert-a-csv-file-to-xml-using-php/

---

Here is an XQuery program that should do it:

<entities>
{
  let $rows :=
    let $input := "ID,Name,Age
      1,qwerty,12
      2,asdf,11
      3,zxcvb,10"
    return tokenize($input, "&#10;")
  let $columns := tokenize(head($rows), ",")
  for $row in tail($rows)
  let $data := tokenize($row, ",")
  return 
    <entity>
    {
      for $column at $p in $columns
      return
        <field name="{$column}" value="{$data[$p]}"/>
    }
   </entity>
}
</entities>

Tested on try.zorba-xquery.com, it outputs:

<?xml version="1.0" encoding="UTF-8"?>
<entities>
  <entity>
    <field name="ID" value=" 1"/>
    <field name="Name" value="qwerty"/>
    <field name="Age" value="12"/>
  </entity>
  <entity>
    <field name="ID" value=" 2"/>
    <field name="Name" value="asdf"/>
    <field name="Age" value="11"/>
  </entity>
  <entity>
    <field name="ID" value=" 3"/>
    <field name="Name" value="zxcvb"/>
    <field name="Age" value="10"/>
  </entity>
</entities>