CODEWITHSUNDEEP
xmlcsvtalend
aditya_gaur
aditya_gaur

Reputation: 3295

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>
        <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

Upvotes: 0

Views: 2012

Answers (3)

drmirror
drmirror

Reputation: 3760

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.

Upvotes: 1

Ghislain Fourny
Ghislain Fourny

Reputation: 7279

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>

Upvotes: 0

redshark1802
redshark1802

Reputation: 924

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/

Upvotes: 0

Related Questions