Importación de archivos XML con información de «esquema de Excel»

Power Query
Un usuario Pregunto ✅

_n_MarianLein

// Editar: Lamento que aparezca el emoji … No tengo idea de cómo solucionarlo …

Estimados,

Gracias por echarle un vistazo a esto.

De una herramienta, obtengo un archivo XML que necesito importar a Excel. Adjunto «user.xml» es el archivo de demostración que haré referencia.

Expediente:

Revelación

<?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">
 <Styles>
  <Style ss:ID="s62">
   <Interior ss:Color="#8DB4E2" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="s63">
   <NumberFormat ss:Format="dd/mm/yyyy hh:mm:ss"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="CAM Export">
  <Table>
   <Column ss:Width="50"/>
   <Column ss:Width="100"/>
   <Column ss:Width="50"/>
   <Column ss:Width="100"/>
   <Column ss:Width="50"/>
   <Column ss:Width="50"/>
   <Column ss:Width="100"/>
   <Column ss:Width="100"/>
   <Row>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Tool</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">ToolDescription</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Level</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">LevelDescription</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Profile</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Person</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">PersonName</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Validity</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Tool1</Data></Cell>
    <Cell><Data ss:Type="String">ToolDesc1</Data></Cell>
    <Cell><Data ss:Type="String">Level1</Data></Cell>
    <Cell><Data ss:Type="String">LevelDesc</Data></Cell>
    <Cell><Data ss:Type="String">Profile1</Data></Cell>
    <Cell><Data ss:Type="String">Approver1</Data></Cell>
    <Cell><Data ss:Type="String">Name1</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="DateTime">2020-01-17T07:58:24Z</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Tool2</Data></Cell>
    <Cell><Data ss:Type="String">ToolDesc2</Data></Cell>
    <Cell><Data ss:Type="String">Level1</Data></Cell>
    <Cell><Data ss:Type="String">LevelDesc</Data></Cell>
    <Cell><Data ss:Type="String">Profile2</Data></Cell>
    <Cell><Data ss:Type="String">Approver2</Data></Cell>
    <Cell><Data ss:Type="String">Name2</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="DateTime">2020-01-07T08:40:41Z</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Tool3</Data></Cell>
    <Cell><Data ss:Type="String">ToolDesc4</Data></Cell>
    <Cell><Data ss:Type="String">Level1</Data></Cell>
    <Cell><Data ss:Type="String">LevelDesc</Data></Cell>
    <Cell><Data ss:Type="String">Profile3</Data></Cell>
    <Cell><Data ss:Type="String">Approver3</Data></Cell>
    <Cell><Data ss:Type="String">Name3</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="DateTime">2020-01-13T13:48:39Z</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Tool3</Data></Cell>
    <Cell><Data ss:Type="String">ToolDesc4</Data></Cell>
    <Cell><Data ss:Type="String">Level1</Data></Cell>
    <Cell><Data ss:Type="String">LevelDesc</Data></Cell>
    <Cell><Data ss:Type="String">Profile4</Data></Cell>
    <Cell><Data ss:Type="String">Approver4</Data></Cell>
    <Cell><Data ss:Type="String">Name4</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="DateTime">2020-01-04T19:48:28Z</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Tool3</Data></Cell>
    <Cell><Data ss:Type="String">ToolDesc4</Data></Cell>
    <Cell><Data ss:Type="String">Level1</Data></Cell>
    <Cell><Data ss:Type="String">LevelDesc</Data></Cell>
    <Cell><Data ss:Type="String">Profile4</Data></Cell>
    <Cell><Data ss:Type="String">Approver4</Data></Cell>
    <Cell><Data ss:Type="String">Name4</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="DateTime">2020-01-04T19:48:28Z</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Tool3</Data></Cell>
    <Cell><Data ss:Type="String">ToolDesc4</Data></Cell>
    <Cell><Data ss:Type="String">Level1</Data></Cell>
    <Cell><Data ss:Type="String">LevelDesc</Data></Cell>
    <Cell><Data ss:Type="String">Profile3</Data></Cell>
    <Cell><Data ss:Type="String">Approver3</Data></Cell>
    <Cell><Data ss:Type="String">Name3</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="DateTime">2020-01-13T13:48:39Z</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Tool3</Data></Cell>
    <Cell><Data ss:Type="String">ToolDesc4</Data></Cell>
    <Cell><Data ss:Type="String">Level1</Data></Cell>
    <Cell><Data ss:Type="String">LevelDesc</Data></Cell>
    <Cell><Data ss:Type="String">Profile4</Data></Cell>
    <Cell><Data ss:Type="String">Approver4</Data></Cell>
    <Cell><Data ss:Type="String">Name4</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="DateTime">2020-01-04T19:48:28Z</Data></Cell>
   </Row>
  </Table>
  <AutoFilter x:Range="R1C1:R1C8" xmlns="urn:schemas-microsoft-com:office:excel">
  </AutoFilter>
 </Worksheet>
</Workbook>

Herramienta Descripción de la herramienta Nivel Nivel Descripción Perfil Persona PersonName Validez Herramienta1 ToolDesc1 Nivel 1 LevelDesc Perfil1 Aprobador1 Nombre1 2020-01-17T07: 58: 24Z Herramienta2 ToolDesc2 Nivel 1 LevelDesc Perfil2 Aprobador2 Nombre2 2020-01-07T08: 40: 41Z Herramienta3 ToolDesc4 Nivel 1 LevelDesc Perfil3 Aprobador3 Nombre3 2020-01-13T13: 48: 39Z Herramienta3 ToolDesc4 Nivel 1 LevelDesc Perfil4 Aprobador4 Nombre4 2020-01-04T19: 48: 28Z Herramienta3 ToolDesc4 Nivel 1 LevelDesc Perfil4 Aprobador4 Nombre4 2020-01-04T19: 48: 28Z Herramienta3 ToolDesc4 Nivel 1 LevelDesc Perfil3 Aprobador3 Nombre3 2020-01-13T13: 48: 39Z Herramienta3 ToolDesc4 Nivel 1 LevelDesc Perfil4 Aprobador4 Nombre4 2020-01-04T19: 48: 28Z

Este archivo tiene incrustada la siguiente información de esquema / estilo:

<?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">
 <Styles>
  <Style ss:ID="s62">
   <Interior ss:Color="#8DB4E2" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="s63">
   <NumberFormat ss:Format="dd/mm/yyyy hh:mm:ss"/>
  </Style>
 </Styles>

Cuando hago doble clic en el archivo, Excel lo muestra correctamente:

El archivo se muestra correctamente en ExcelEl archivo se muestra correctamente en Excel

Al importar a PowerQuery (Datos -> Importar -> Desde archivo -> Desde XML), el esquema XML parece no ser reconocido y, por lo tanto, el archivo no se está importando correctamente:

Importación incorrecta en ExcelImportación incorrecta en Excel

¿Alguien tiene una idea de cómo puedo hacer que Excel trate este archivo como lo hace en el «visor»? La razón por la que quiero importar los datos es que obviamente tengo varios archivos user.XML y cambian con frecuencia. Por lo tanto, copiar y pegar manualmente desde el visor en una tabla no es una opción …

Encontré algunos hilos similares en el foro, pero no funcionaron para mí:

  • Desde aquí: cargando el archivo en PQ-Editor, expandiendo la tabla (varias veces):
    • Termino con esto en lugar de una tabla con varias columnas.
      Columna única, valores en serieColumna única, valores en serie
  • Desde aquí: Investigando con editor avanzado:
    • No tengo idea de qué debería hacer exactamente aquí …

¡Se agradece cualquier ayuda!

ImkeF

En respuesta a _n_MarianLein

Eso es porque su JSON debe ser diferente al que usé en la consulta que pegué.

Si sigues los pasos del código que he pegado, verás que no hay un resultado intermedio como el que has mostrado.

Para recuperar Worksheet-table de la tabla que ha pegado, puede usar el siguiente código, por ejemplo:

# «XML analizado» {[Name = «Worksheet»]}[Table]

Y para convertirlo en una función, reemplaza la cadena XML por el parámetro de función así:

(XMLFilePath as text) =>
Source = File.Contents(XMLFilePath),
....

ImkeF

Esta es una opción para analizar el XML: 

let
    Source = "<?xml version=""1.0""?>#(cr)#(lf)<?mso-application progid=""Excel.Sheet""?>#(cr)#(lf)<Workbook xmlns=""urn:schemas-microsoft-comffice:spreadsheet""#(cr)#(lf)          xmlnsa=""urn:schemas-microsoft-comfficeffice""#(cr)#(lf)          xmlns:x=""urn:schemas-microsoft-comffice:excel""#(cr)#(lf)          xmlns:ss=""urn:schemas-microsoft-comffice:spreadsheet"">#(cr)#(lf) <Styles>#(cr)#(lf)  <Style ss:ID=""s62"">#(cr)#(lf)   <Interior ss:Color=""#8DB4E2"" ssattern=""Solid""/>#(cr)#(lf)  </Style>#(cr)#(lf)  <Style ss:ID=""s63"">#(cr)#(lf)   <NumberFormat ss:Format=""dd/mm/yyyy hh:mm:ss""/>#(cr)#(lf)  </Style>#(cr)#(lf) </Styles>#(cr)#(lf) <Worksheet ss:Name=""CAM Export"">#(cr)#(lf)  <Table>#(cr)#(lf)   <Column ss:Width=""50""/>#(cr)#(lf)   <Column ss:Width=""100""/>#(cr)#(lf)   <Column ss:Width=""50""/>#(cr)#(lf)   <Column ss:Width=""100""/>#(cr)#(lf)   <Column ss:Width=""50""/>#(cr)#(lf)   <Column ss:Width=""50""/>#(cr)#(lf)   <Column ss:Width=""100""/>#(cr)#(lf)   <Column ss:Width=""100""/>#(cr)#(lf)   <Row>#(cr)#(lf)    <Cell sstyleID=""s62""><Data ss:Type=""String"">Tool</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s62""><Data ss:Type=""String"">ToolDescription</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s62""><Data ss:Type=""String"">Level</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s62""><Data ss:Type=""String"">LevelDescription</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s62""><Data ss:Type=""String"">Profile</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s62""><Data ss:Type=""String"">Person</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s62""><Data ss:Type=""String"">PersonName</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s62""><Data ss:Type=""String"">Validity</Data></Cell>#(cr)#(lf)   </Row>#(cr)#(lf)   <Row>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Tool1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">ToolDesc1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Level1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">LevelDesc</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Profile1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Approver1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Name1</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s63""><Data ss:Type=""DateTime"">2020-01-17T07:58:24Z</Data></Cell>#(cr)#(lf)   </Row>#(cr)#(lf)   <Row>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Tool2</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">ToolDesc2</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Level1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">LevelDesc</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Profile2</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Approver2</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Name2</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s63""><Data ss:Type=""DateTime"">2020-01-07T08:40:41Z</Data></Cell>#(cr)#(lf)   </Row>#(cr)#(lf)   <Row>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Tool3</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">ToolDesc4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Level1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">LevelDesc</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Profile3</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Approver3</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Name3</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s63""><Data ss:Type=""DateTime"">2020-01-13T13:48:39Z</Data></Cell>#(cr)#(lf)   </Row>#(cr)#(lf)   <Row>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Tool3</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">ToolDesc4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Level1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">LevelDesc</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Profile4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Approver4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Name4</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s63""><Data ss:Type=""DateTime"">2020-01-04T19:48:28Z</Data></Cell>#(cr)#(lf)   </Row>#(cr)#(lf)   <Row>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Tool3</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">ToolDesc4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Level1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">LevelDesc</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Profile4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Approver4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Name4</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s63""><Data ss:Type=""DateTime"">2020-01-04T19:48:28Z</Data></Cell>#(cr)#(lf)   </Row>#(cr)#(lf)   <Row>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Tool3</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">ToolDesc4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Level1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">LevelDesc</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Profile3</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Approver3</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Name3</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s63""><Data ss:Type=""DateTime"">2020-01-13T13:48:39Z</Data></Cell>#(cr)#(lf)   </Row>#(cr)#(lf)   <Row>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Tool3</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">ToolDesc4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Level1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">LevelDesc</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Profile4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Approver4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Name4</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s63""><Data ss:Type=""DateTime"">2020-01-04T19:48:28Z</Data></Cell>#(cr)#(lf)   </Row>#(cr)#(lf)  </Table>#(cr)#(lf)  <AutoFilter x:Range=""R1C1:R1C8"" xmlns=""urn:schemas-microsoft-comffice:excel"">#(cr)#(lf)  </AutoFilter>#(cr)#(lf) </Worksheet>#(cr)#(lf)</Workbook>",
    #"Parsed XML" = Xml.Tables(Source),
    Worksheet = #"Parsed XML"{0}[Worksheet],
    Table = Worksheet{0}[Table],
    Row = Table{0}[Row],
    #"Added Custom" = Table.AddColumn(Row, "Custom", each List.Transform([Cell][Data], (x) => x[#"Element:Text"]{0})),
    Custom1 = Table.FromRows(#"Added Custom"[Custom]),
    #"Promoted Headers" = Table.PromoteHeaders(Custom1, [PromoteAllScalars=true])
in
    #"Promoted Headers"

_n_MarianLein

En respuesta a ImkeF

Hola,
¿Puede guiarme sobre cómo crear una función personalizada que implemente esto? No he llegado tan lejos para hacerlo …

ImkeF

En respuesta a _n_MarianLein

¿Qué desea utilizar como parámetro (s) de función?

_n_MarianLein

En respuesta a ImkeF

Hola,

Lo que me gustaría hacer es importar una carpeta completa y analizarla en una tabla grande.
Reemplacé la fuente con el «File.Contents (‘… user.xml’)» real y ejecuté la consulta que proporcionaste anteriormente, de modo que tengo al menos un archivo analizado.
Sin embargo, en el paso «Hoja de trabajo» encuentro el siguiente problema:

1: 1-fuente.png

2: Xml.png analizado en 2

3: 3-hoja de trabajo.png

Lo que se traduce como «el campo ‘hoja de trabajo’ no se encontró en el conjunto de datos».
No entiendo esto como se muestra en el paso 2 …

ImkeF

En respuesta a _n_MarianLein

Eso es porque su JSON debe ser diferente al que usé en la consulta que pegué.

Si sigues los pasos del código que he pegado, verás que no hay un resultado intermedio como el que has mostrado.

Para recuperar Worksheet-table de la tabla que ha pegado, puede usar el siguiente código, por ejemplo:

# «Analizado XML «{[Name = «Worksheet»]}[Table]

Y para convertirlo en una función, reemplaza la cadena XML por el parámetro de función así:

(XMLFilePath as text) =>
Source = File.Contents(XMLFilePath),
....

_n_MarianLein

En respuesta a ImkeF

¡Eres increíble, Imke!

Lo que terminé con es esto:

let
    xmlfile = (XMLFilePath as text) => let
        Source = File.Contents(XMLFilePath),
        #"Parsed XML" = Xml.Tables(Source,null,65001),
        Worksheet = #"Parsed XML"{[Name = "Worksheet"]}[Table],
        Table = Worksheet{0}[Table],
        Row = #"Table"{[Name = "Row"]}[Table],
        #"Added Custom" = Table.AddColumn(Row, "Custom", each List.Transform([Cell][Data], (x) => x[#"Element:Text"]{0})),
        Custom1 = Table.FromRows(#"Added Custom"[Custom]),
        #"Promoted Headers" = Table.PromoteHeaders(Custom1, [PromoteAllScalars=true])
    in
        #"Promoted Headers"
in
    xmlfile

Pasos adicionales para que funcione:

  1. Consulta -> Nuevo -> Carpeta
  2. Crear columna personalizada – Fórmula: 
    = Table.AddColumn(#"Source", "FullName", each [Folder Path] &[Name])
  3. Ejecutar función personalizada – Fórmula: 
    = Table.AddColumn(#"Custom Column", "fxImportXML", each fxImportXML([FullName]))
  4. Expandir columna personalizada
  5. Desde aquí: haz lo que quiera 🙂

¡Felicitaciones, Imke!

Entradas relacionadas

Último viernes fecha

Power Query

Un usuario Pregunto ✅ mb0307 Hola, Como puedo conseguir la fecha del último viernes en una columna personalizada de Power Query por favor? Hoy es…

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *