Excel to XML converter

[mike_01]

Tweet

Hiya,

I am looking for the script or a program that i can run on local to convert the excel spreadsheet to xml sheet. Tried googling too but couldn't find the right one available. There are few offering the demo version too but that generates the xml sheet with the "Please buy licence" code.

Does anyone here got a script or program for the same?

P.S - I am looking for the "FREE" script as of now

Thanks,
Mike...

[moredial]

The free script

1/ convert excel to csv format

2/ use php (or other) script to parse the csv file to xml format

If it is a big set of data, the quicker option is to parse to an sql database.

[mike_01]

Thanks for the pointers moredial, just wanted to ask if you or someone else here have the php script to parse the same ? I have found another way using the XML parsing through the MS excel 2003 but thats a real lengthly and manual process. Anyone care to share..

Thanks again.

[Donk]

If you have a csv file with headings in the first line try this:

<?php
header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); // Date in the past
header('Content-type: text/xml');
$global_tag="product_list";
$outer_tag="product";
$tags=array();
$xml="<?xml version=\"1.0\" ?>\r\n";
$xml.="<".$global_tag.">";
$fp=fopen("filename.csv","r");
$data = fgetcsv ($fp,8192);
$tags=$data;
while ($data=fgetcsv ($fp,8192)){
$xml.="<".$outer_tag.">";
foreach ($tags as $key=>$value){
$xml.="<".$value.">";
$xml.="<![CDATA[".$data[$key]."]]>";
$xml.="</".$value.">\r\n";
}
$xml.="</".$outer_tag.">\r\n";
}
$xml.="</".$global_tag.">";
echo $xml;
?>

If you don't have the headings in the first row change $tags=array() to $tags=array("col1","col2"); and leave out the first fgetcsv.

If your data is well formed for xml you don't need to have the cdata round the data. Change the line to $xml.=$data[$key]; and don't forget to change filename.csv to your own file name.

Regards Bob

[mike_01]

Thanks for the script mate.. Will surely give it a try.

Thanks again,

If you have a csv file with headings in the first line try this:

<?php
header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); // Date in the past
header('Content-type: text/xml');
$global_tag="product_list";
$outer_tag="product";
$tags=array();
$xml="<?xml version=\"1.0\" ?>\r\n";
$xml.="<".$global_tag.">";
$fp=fopen("filename.csv","r");
$data = fgetcsv ($fp,8192);
$tags=$data;
while ($data=fgetcsv ($fp,8192)){
$xml.="<".$outer_tag.">";
foreach ($tags as $key=>$value){
$xml.="<".$value.">";
$xml.="<![CDATA[".$data[$key]."]]>";
$xml.="</".$value.">\r\n";
}
$xml.="</".$outer_tag.">\r\n";
}
$xml.="</".$global_tag.">";
echo $xml;
?>

If you don't have the headings in the first row change $tags=array() to $tags=array("col1","col2"); and leave out the first fgetcsv.

If your data is well formed for xml you don't need to have the cdata round the data. Change the line to $xml.=$data[$key]; and don't forget to change filename.csv to your own file name.

Regards Bob

[Donk]

As an afterthought.

If you want stick to excel:
Insert a column in front of the data and also insert a new column after each of your current columns.

Delete the heading row
insert 2 rows at the top
cell A1 enter <?xml version="1.0" ?>
in cell A2 enter <product_list>
in A3 enter<product> and the start tag for the first column of you data. eg <product_id>
in C3 close the first tag and open the next eg </product_Id><product_name>
continue this in E3 G3 etc

After your last column you will need to close the preceding column tag and also the product tag eg </last_data></product>

Copy and paste these cell into every row
and finally after the final row close the </product_list> tag.

Save this as a text file and rename it with an xml extension.