How to read excel using xml format tFileInputXML?

[Anonymous]

Hi,

I want to know how to read excel using xml format tFileInputXML?

here is picture of my job :

Thanks.

[fdenis]

hi,
excel file xlsx are zipped xml file you have to unzip file first then you may obtain data into xml format.
but this data are not easy to use. so the best way is to use excel components. if you need more. you can use apache poi : https://poi.apache.org/
and tJava flex.
it's useful but it' codding.
regards

[Anonymous]

Hi @fdenis ,

Thank you for your information.

[GPHemsley-RELX]

I believe this post is referring to an older XML-based Excel format, which starts something like this:

```

<?xml version="1.0"?>

<?mso-application progid="Excel.Sheet"?>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

 xmlns:o="urn:schemas-microsoft-com:office:office"

 xmlns:x="urn:schemas-microsoft-com:office:excel"

 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

 xmlns:html="http://www.w3.org/TR/REC-html40">

```

It's unclear whether Talend has native support for this format, but you can manually parse each sheet into a separate connector using tFileInputMSXML if you want:

• Root XPath Query: "/Workbook"
• Schema XPath Loop: "Worksheet[1]/Table/Row"
• Schema XPath Querys: "Cell[1]/Data/text()"

Note that the XPath library doesn't seem to support certain functions and/or namespaces, so you have to specify everything directly by index.