topic Re: How to read excel using xml format tFileInputXML? in Talend Studio

How to read excel using xml format tFileInputXML?

Anonymous — Sat, 16 Nov 2024 04:36:29 GMT

Hi,

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

here is picture of my job :

Thanks.

Re: How to read excel using xml format tFileInputXML?

fdenis — Mon, 23 Sep 2019 09:48:43 GMT

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

Re: How to read excel using xml format tFileInputXML?

Anonymous — Mon, 23 Sep 2019 11:58:32 GMT

Hi @fdenis ,

Thank you for your information.

Re: How to read excel using xml format tFileInputXML?

GPHemsley-RELX — Mon, 07 Nov 2022 20:39:55 GMT

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.